Read a spreadsheet (csv or tsv) into a [coll]
I'd been having trouble getting spreadsheet data formatted as a csv or tsv into a [coll], and I didn't find any convenient solutions when I looked around for them, so I made a new object [sheet2coll], which you can download here if it's useful: https://github.com/natepmay/sheet-coll-converter. You just have the change the file extension to .txt but no other formatting is required. Let me know how it works for you!
Hello,
the link doesn't work.
the sheet2coll seems the thing I need for a new project. Can you put it back ?
Thanks !
Found it here :
https://github.com/natepmay/sheet-coll-converter
Yes sorry the dot at the end got included in the link. Should be fixed now. Glad you found it and hope it's helpful!
Very usefull, thanks
Good to hear!
Thanks so much for this incredible tool! I'd have many needs for this.
At the moment, I'm getting some js errors. I don't much javascripting. Any advice?
js: tsvtocoll.js: Javascript InternalError: buffer too small, line 20
js: error calling function read [tsvtocoll.js]
Sounds like it could be a problem with the input file. Did you try it with tabfile.txt (the demo file that comes with the project)? If so, can you tell me more about when you get the error and share your patch if possible?
thanks. Yes tabfile.txt works fine.
I am using a txt file containing information about an Apple Music playlist. I'd like to use coll to access the file location of the playlist's audio files. The text file was produced by Apple Music (in Apple Music: File > Library > Export Playlist...
Perhaps the txt file I want to read is not formatted properly?
I've attached my patch (which is not special) and the text file.
why do you bother with java ?
you can read csv file without renaming directly into filein and dump lines to coll.
Even text could do so if file gets renamed to txt.
Musicmovement: thanks for the files. I was able to reproduce the error and I'll see if I can figure out why it's happening when I get a chance.
Source Audio: Could you make a quick patch as an example? I messed around with those ideas a bit and wasn't able to get them to work right away.
sure, here is simplest one :
read text file, dump to coll using space as delimiter, no matter if source file is
comma, semicolon, space or tab delimited
If you need filein version with delimiter detection,
choice between space and tab delimited coll,
post request.
By the way, is there any reason to use tab delimiter in coll ?
Refering jit.cellblock with such coll format would fail
Thanks Source Audio—this sounds helpful. I should be able to take a look in a couple of days (swamped at the moment).
Okay I had a few more minutes to try some things out.
Musicmovement: I found a workaround that should work for the time being: convert your spreadsheet to CSV rather than TSV (and then you'll still need to change the extension at the end to .txt). I've attached the CSV version of your file that works for me.
Source Audio: Your patch seems to be working for me so far, although it's a bit hard to tell since it treats everything as space-delimited. If it can accept files without changing the extension, and if it doesn't have the bug for TSV files that mine apparently has, it could potentially be a better alternative to mine. Want to connect outside of this thread? I could either update mine on github and credit you, or point users to a repo that you make.
I am not interested in any crediting and such, you can use
that patch and do whatever you like with it.
I see the main problem with spreadsheet files which contain empty cells.
feeding that into coll can not work unless one inserts something for empty cells.
Your solution with zero ... can work but what if zero could be expected value ?
Also cells containing multiple words not enclosed with quotas.
That all makes it difficult to use standard max objects that
mess with text formatting.
Nate May: Thank you so much for looking at my file! Sorry for the delayed response.
What method do you use to convert from tsv to csv? I tried to convert my file in excel, but the resulting file crashed your javascript (even with the txt extension). It would be good if this conversion took place in Max. Could another javascript do this?
Ideally, I'd like to make this a simple process. The playlist file generated from apple music should be entered to coll, where the file location could be accessed and used to play the file from its location.
Thanks for your help!
Musicmovement: You're right it should ideally be a simple process. It's possible that javascript could do this but I would want to figure out your bug first. Excel has a few options for CSV formats (UTF-8 is one of them, and then for me it offers other possibilities). Google Sheets can also import TSV and export CSV. I'm not sure which format is best so I'd try a few of those.
Hopefully I'll find some time to dig into a more permanent solution before long, quite possibly using Source Audio's method. If anyone else wants to help out please let me know!
One should check that export file first, without loosing time fixing working js.
I guess it has wrong text encoding
The original file compared with plain text format,
if one copies and pastes all into new text file.
Thanks both for your help!
I'll keep looking for a solution on my end and report if I stumble across something!
Okay I'll stick with trying to fix the javascript option for now.
I'm having good luck with Musicmovement's file when I save it as a csv and then change the extension. I tried converting it in Excel with both of their options (UTF-8 and regular) and they both worked. There was a bit of an issue with escaped characters but that's probably to fix down the line. It seems like the best option might just be to deprecate support for tsv files, which would also make the object simpler to use.
Musicmovement: could you help me try to reproduce the error you had when you tried to convert your file from tsv to csv in Excel?
Hello from the future,
I was looking into this topic, and I'm wondering if it's possible to parse CSV files using node.script or the v8 object that supports javascript ES6+.
This library seems to do what needed: https://www.npmjs.com/package/csv-parse
I'm pretty sure it is possible but I'm limited with javascript and my attempts failed. I would need to export the CSV data into JSON (dict) or export a specific column or row into a coll.
I wonder if there's some javascript ninja around that could help with this?
thanks in advance
I just found this as well, so in order to gather stuff on this topic in one place I'm posting this here for someone out there with similar needs:
years later but i still found this helpful. thanks for sharing!