macOS Arg, data issue is driving me mad.

[detz]

I've been sent a whole bunch of excel files from a client and I need to get that data out and make use of it in the app. The issue is the data is in excel, it's not consistent and has weird MS characters in it. The biggest issue is probably that the second column has everything from quotes to commas to newlines so if I try to export it as csv it breaks. I've tried exporting in ever type to see if there was a way I could parse it but I'm out of ideas. There must be a way, I mean excel and numbers both read the xsl and csv files correctly but I can't get anything else to.

Is there a way I can export this from one of these programs into a meaningful portable way?

 

[toddburch]

I've successfully used Ruby on Windows to set and get data from an excel spreadsheet. Haven't tried it on a Mac yet.

 

[lazydog]

Are you sure the csv is broken? csv can handle quotes, commas and new line characters - fields are quoted if they contain the separator character and any quote characters are escaped (off hand I think a quote character is escaped as "").
The other thing you could do is change the separator character to something you know the data isn't using... perhaps ^, @ or $ and then export again.

b e n

 

[GroovyLinuxGuy]

I don't know if this will work or not, but try opening the files in OpenOffice

http://www.openoffice.org/

It may allow you to save it as another format. I have done this in the past for weird acting excel and word docs and sometimes it works but conversly sometimes it doesn't.

Cheers