Problems with CSV files

[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.

Code: Select all

replaceText(x,"\"","")

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...

[Simon]

Did you try filter without space
and
filter without empty
and
filter without quote?

Simon

[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:

Code: Select all

delete line 1 to 1 of x

look up "delete chunk" in the dictionary

[Newbie4]

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?

[Newbie4]

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:

Code: Select all

delete line 1 to 1 of x

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?

[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

[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

[Simon]

The horror, the horror...

[Simon]

Hi Newbie4,
Aside from CSV format being "inherently damaged", were you able to figure out the problem?

Simon

[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.