Page 1 of 1
Problems with CSV files
Posted: Sat Oct 20, 2012 2:29 pm
by Newbie4
I am reading data in from CSV files and having minor problems with quotes, spaces and names.
Some fields had extra quotes around them (e.g. "10/18/2012") or extra spaces before and after the field (e.g. " Yes") that I want to remove. I wrote a function (see below)
Code: Select all
function removeSpaces x
replace quote & quote with " " in x
repeat while char 1 of x = " "
put length(x) into y
put char 2 to y of x into x
end repeat
repeat while char -1 of x = " "
put length(x) into y
put char 1 to y of x into x
end repeat
return x
end removeSpaces
but it seems awkward. Is there a better way to remove leading/trailing spaces and quotes?
I tried using replaceText but I had trouble with the quote (") - even backslashing it did not work.
Finally the name. Often the name is in the form last,first (e.g. "Smith, Sam"). I have code that takes off char 1 (the first ") and char -1 (the last ") and separates the last and first names into separate fields. But my headers are off (I am reading the file into an array - then showing it in a data grid). The first line read has the headers and does not line up with the number of fields anymore. Is there an easy was to adjust this in the program instead of editing the original CSV file?
LiveCode is so powerful, it just seems like I am doing everything by brute force. Are there more elegant ways to accomplish what I am trying to do?
Thank you in advance...
Re: Problems with CSV files
Posted: Sat Oct 20, 2012 3:27 pm
by Simon
Did you try filter without space
and
filter without empty
and
filter without quote?
Simon
Re: Problems with CSV files
Posted: Sat Oct 20, 2012 4:00 pm
by timlit
how about this opening step (or variations with/without leading or trailing qoute):
Code: Select all
replace (quote & <delimiter> & quote ) with <delimiter> in x
as for line deletion, try smth like:
look up "delete chunk" in the dictionary
Re: Problems with CSV files
Posted: Sat Oct 20, 2012 5:25 pm
by Newbie4
Simon wrote:Did you try filter without space
and
filter without empty
and
filter without quote?
Simon
Thank you for the suggestions but I am sorry, I do not understand.
Is filter a command? Do you mean I should not be using "'s?
Re: Problems with CSV files
Posted: Sat Oct 20, 2012 5:29 pm
by Newbie4
timlit wrote:how about this opening step (or variations with/without leading or trailing qoute):
Code: Select all
replace (quote & <delimiter> & quote ) with <delimiter> in x
as for line deletion, try smth like:
look up "delete chunk" in the dictionary
Thank you for the suggestion, I will try it.
As to the delete, I do not want to delete the line 1 because I use it to populate my data grid. I just wanted to know if there is a way to fix it easier than having to edit the CSV file every time I re-load it with new info.
The ist line is for the headers and it comes in with "name" but after I split the names into first and last, the headings are off.
Does that make sense?
Re: Problems with CSV files
Posted: Sat Oct 20, 2012 5:43 pm
by Simon
Thank you for the suggestions but I am sorry, I do not understand.
Is filter a command? Do you mean I should not be using "'s?
Yes, filter is a command, look it up in the dictionary. I like it because it doesn't use the slow repeat loop.
Just put the whole csv into a temp and "filter without...".
You can use "" it is shorthand for "empty".
Simon
Re: Problems with CSV files
Posted: Sat Oct 20, 2012 6:04 pm
by FourthWorld
CSV is an inherently damaged format, the creation and perpetuation of which is arguably the single most stupid move ever made in the computing industry.
There is no single standard; export implementations vary from product to product, and sometimes between versions of the same product.
The script on this page is the result of a lot of discussion in the LiveCode community about how to deal with the broken mess that is CSV, so far the most efficient solution which handles all of the exceptions exhibited in the sample data set provided there:
http://www.fourthworld.com/embassy/arti ... t-die.html
Re: Problems with CSV files
Posted: Sat Oct 20, 2012 6:42 pm
by Simon
The horror, the horror...
Re: Problems with CSV files
Posted: Wed Oct 24, 2012 5:00 pm
by Simon
Hi Newbie4,
Aside from CSV format being "inherently damaged", were you able to figure out the problem?
Simon
Re: Problems with CSV files
Posted: Wed Oct 24, 2012 6:20 pm
by FourthWorld
There are so many variants of CSV that it may not be possible to know what went wrong with the original code without seeing the data he's parsing with it.
If he uses Alex Tweedly's script in the page I linked to above he should be able to parse nearly any CSV file without error.