Importing from Excel; .csv questions
Right now I’m trying to grab data from an Excel spreadsheet and import them into a Max patcher. Basically, I want to be able to grab a number (the data will always be number values, never text) from any given cell in the spreadsheet, and put that number into Max.
After searching through the forums here, I know that I can save an Excel file as a .csv file for Max to read as text (http://cycling74.com/forums/topic.php?id=30799). But after saving my .csv file, I don’t know what to do with it! Do I write the file into a coll object or something?
Sorry if this is a basic question…I’m rather new at Max.
hey i'm guessing you probably want this in a [coll]? its possible to load data from a excel csv into max coll but not directly. coll wants: each line to begin with a int followed by a comma, fields seperated by a space (not comma), and lines ending with a semicolon. [text] is pretty indifferent to the contents though so you could just load the csv into that with (read) after you add a '.txt' extensiom to the file name. but if you want it in a coll, see attached. i made this to work with text and add sequential numbers as each line enters the coll but it would be fine with numeric data. this patch doesn't verify the integrity of the csv in any way. empty fields cant be blank (just fill with a '0' or something in excel). other things would probably break it tool. this is more of a job for java. i hesitate to post this but, eh:
-- Pasted Max Patch, click to expand. --Copy all of the following text. Then, in Max, select New From Clipboard.----------begin_max5_patcher---------- 1514.3oc2Zs0biZCE9YmeEZX5aMkn6Rrc5zo+NZ1YGLVIlN1fKf2MY2Y+uWc ARrcLBrASb5KVVxJhy4SmKemC4G2LKXd9Spx.vm.+MX1rebyrY1kLKLqd9rf 0wOkrJtztsf0pxx3GUA259sJ0SU10+86Kz6CrJdaVxx4E4eqTU.VVUs4S2cW xyIqRydTPCSxWe2C4EaWWdWU9lzjvMK27moK9CBgH3Mmodupj7sY1ClWu3l3 pjk5C4KEpjJm.yCg2BPPpc.gMCRQHD745+jzEVIKe9+7aDTyg+PdVUV7Zk8m 9qhz3UM+R110oYqTUV8D+5h4aqZVEsygTl9c6gXevtkc6r54MJm.FD.9r4W9 4M2X931dBwZPZsRq8GBwwKV.BqdpBTkCpVp.prEf7Gre8gzUJfQs.oYfmy2V .xKCAaJzWV2mcePgJdw8AfX8ef6pwdDOUEdTHmzAjyfVDmILCT4wQbL8DQbz QQbXKH9HBqkweUikOknV4fw3RPXR4WC5.EvQNTPZm.aAEvWanPl5aZA6MfPE XNHEf7nzXgvooH60N2qZCGE0lbhtayiydL3VPPp4V1MbVNfsfQE5GnBTpp7g RHK7PD1AlvGJghFETBepAkt0FXpQGzwHzdEwUo4Y6nG53w1qYmF.q+XLQye0 u0FG6bxXV3DGx7fiv2kf6NyK+vHEa8SHQ3KEL1gSKl1eXjc0BiDoXrfw13uX x.B9kdfkDrEKYNZGsj8CwubXIJjEwPRgGRG8BMo3KlQ41umB3bdOLL41AA1W bRxnfkmaxjcSpzIxxsjBzrYuX1oIqTwEd.VDW5BbGxkDHhZxE4yREelfqQfR xWkWT+XsOic+3kG17GObevW9X28MXp19uaPQVfAEQuTV8wa1XnjmTzM+.LwN 3r9aytmNN7CllhVZiWo4KcSWhF0MoRp7cgT4aiAbNny10yU9rKHtxIvhcpvp U10mKPTo2Q1iqT6VRwv3RVyz1hOioYi1G15K0AYaGKaByl9P30YhP+36L0fJ nd3QEYi5yYdwDxGeLoGTMbHRsQhWlFH4UDUiQjzv+tUU7rOHh3xFKstPThWp sz2UpsSVAn0c4Bx6QkS32wJmlrRIctQ8CPP+u.PNgF9fgxta3y6Z+dl1ZkqM Vbzb8WqLQ9QKfRa3hoM68nzLGmEePh3LQjiUpEORBIQWh5sl5pDPb6fzKmF1 G2hDZyrJWS5qayp5tS4sXe5zVru9JBKnLjw3iJjTh8aMqckZKpk+U9PaFeml mwh7lFbTrEomS79aOrO+l2x0hunkJsl7k3Jcofy0I2JqwmZ.ZVfRWmpUArua IGj1.r1yxF7+f2XrElLquOZWlusHoQppCqAdEnVnJqRyrsoYm8v1aOKSWrPk sa8pqSWrIW6hUKBHgvdCfCifBImahRvzA8QXgr0kQLVXDmnCqX1Bzb.utTK1 R8UGw8PGQmkNx4gDBUPv0JyNqLPYldwjYcoDDjNCD+EY9kUl.b1zwnyPnI08 gxYYXmMPgEQGeKeLoN1CwkNrl44tyZd4zMl3xAqHFYD0kkB8zTjgZH.6i0aW VB6k0R1jzRWDXjz7EARvoh8yYc38gj6hD4dUU5aGgDAiDGcIxdVXC2.yBB3t .Azd2dc.BMY2eCuwCzaBhX0aRsRReUue6RtluiI1AyjIQuYWR8V5ZbK04+Yl MF5DpOFzmhNAOJcsC0InqwytdeX5IMEwfhishqxNrvBBDzzn0jKpR6rMqciY zoQkvmTjoSUmnt26OQZ0IyrQHAPW9aGDQdjToglBNpGoILMDeBScc.+jV7zi lTYBw5CNQOUtJ0gIbwJkvWlMTokeAnANTYR1yHY8zEwyqL+PX1kDp9eVFLIT WZ.jwO1JXl6pvcwXmMP0lzGaY10ms7AlPWGxDbZkIXeu6PSGs9KVuBPLzdUE MbtK8I0BcZCiSit9BLRHieeFFpHQu.rBZxuUWrC90YC0OsWMWBMo9oHTejI7 jdq1mPrSq6HQN9gylhKNx6.0odYfetxjdxOu4+7bE.bD -----------end_max5_patcher-----------
First off: thank you so much!!! This will DEFINITELY help, and there is no chance that I ever would have thought this up.
Secondly: this might complicate matters, but is there a way to do this without manually saving the Excel file…? The reason for this is that the Excel file will constantly be updated by means of a DDE server, so the values to be imported are always changing in real-time, and having to manually save the Excel as a .csv takes away that real-time aspect of it.
The data I’ll be using is being received by one program (MT4), and from there it’s sent to Excel in real-time, which is to be sent to Max also in real-time, if possible. So the next best thing I can think of off the top of my head would be to set Excel’s auto-saving to the shortest time interval possible and use the patcher you just provided. But if there’s a better way to preserve real-time values, that would be a big help.
And again, sorry if this is basic! :(
PS: Does Max support DDE servers? That would be kinda nice…
see attached excel file. whatever is on sheet1 will be saved as "data4max.csv" every 13 seconds. the old data is overwritten. press ctrl+t to start the macro. to see the vba code press alt+f11 to open the editor. to change the time interval, double-click "Module1" and find the number 13. so maybe put your dde stuff on sheet2 then format for the record to go to max on sheet1.
to get [text] to read .csv without renaming the file, goto your max "init" folder, open "max-fileformats.txt" and add a line like "max fileformat .csv TEXT 0 "CSV Text file" textfile;"
add [filewatch] to the patch. it sends a bang every time the contents of the file has changed (see help). with the max example above, instead of indexing the data with that auto incrementing number (the second pair of [i] below the second uzi) maybe concatenate some things in excel to make a big timestamp int to send the coll for index. to make the index stop resetting to 1, delete the green patch cord going to the lower [i 1].
Oh wow! Thanks!! I’m going to try this out tomorrow!
I think that was the ticket!!! But I can’t get too excited yet…I can’t get floats inputted into [text] to not be cut off after the 3rd decimal point! And I do need to work with values with 4 decimal points…arghhh
For instance, if I have an Excel cell with a value of 0.12345, the value that will end up in the second [text] and ultimately the [coll] is 0.123. Long strings don’t get cut off, though.
Is this an inherent issue with [text]? Because I tried connecting a flonum to a [text], and inputting numbers with 4 or 5 decimals, and only the first 3 are ever shown inside the [text].
If this is a limitation in [text] I won’t be able to work around, I’ll see what I can do to compensate. Perhaps if I multiply floating points by a large multiple of ten before putting it in the second [text], and dividing it back to normal before using the value, I can get past this.
But is there a floating point precision tricks of [text] that I’m not aware of? That’s all I will ask of you now…you’ve done a lot already OTL
Actually, never mind! The multiplication thing worked!
*Now* I can say YIPPEE!!!!! :D
Thank you SO very much. Never would I have been able to do this without your help. :)
Hi Mr Johnson – i found this thread this morning and am also having issues with the text object and decimal points. Within your experience how did you solve this? My data is coming from long lat co-oodinates which come as 51.4930794 / -0.0540378. Don’t know if you solved this but I am using the same (Justin G ‘hats off’) example for packing coll with a converted .cvs file. I eventually want to map this data to between 0.-1. I’ve attached data as .cvs incase your interested. :) many thanks in advance.
Hi Sampointone, I believe mr Johnson multiplied his number in excel (in your case multiply by 10,000) then once the number arrives in Maxmsp, divide it by 10,000 to return it to normal.
Howdy Justin G,
Just made an account to thank you for this patch from 4 years ago. It really helped in a pinch!
Forums > MaxMSP