Alternative delimiters for CSV files?
Discussion
Need to dump some data out from a website in CSV format, which is easy enough; problem I have is when the data itself contains commas - naturally, excel interprets these as a delimiter and breaks to a new column... 
I've tried using vbtab as a delimiter but that doesn't work.
Any ideas?
I've tried using vbtab as a delimiter but that doesn't work.
Any ideas?
Being pedantic, CSV stands for "comma separted values", so the exact answer is "no you can't use different separators".
Practically speaking, you hit this sort of problem all the time. 3 options:
1. Remove commas from the data before creating the csv file (or replace them with something else that the application(s) that read the file will know to substitute commas for)
2. Surround each piece of data with delimeters (e.g. " quotes) and, again, get the app that reads the data to sort it out.
3. Pick another delimeter that doesn't appear in the data, but call the file something else other than csv...
Practically speaking, you hit this sort of problem all the time. 3 options:
1. Remove commas from the data before creating the csv file (or replace them with something else that the application(s) that read the file will know to substitute commas for)
2. Surround each piece of data with delimeters (e.g. " quotes) and, again, get the app that reads the data to sort it out.
3. Pick another delimeter that doesn't appear in the data, but call the file something else other than csv...
pdV6 said:
2. Surround each piece of data with delimeters (e.g. " quotes) and, again, get the app that reads the data to sort it out.
Actually, just tried a few experiments with Excel and the above seems to work best. Excel sorts it out for you.
NB Probably only need the quotes around text fields, which has the side-effect of forcing string formatting onto the affected cells.
)Bah! Looks like I've actually got some bad data which is screwing up the CSV generation anyhow.
Back to the drawing board...
I think the programmer who came up with the database schema was either smoking something pretty potent, or is a sadistic bastard who likes making my life a misery. Either way there's gonna be a kickin' tomorrow...
>> Edited by judas on Thursday 19th May 17:43
Back to the drawing board...
I think the programmer who came up with the database schema was either smoking something pretty potent, or is a sadistic bastard who likes making my life a misery. Either way there's gonna be a kickin' tomorrow...
>> Edited by judas on Thursday 19th May 17:43
Trying to pick a magic delimiter character which never, ever, ever appears in the data is pants, because it always crops up eventually and then there is misery, gnashing of hair, wailing of teeth and, uhm, ripping ... think i need more coffee.
Each data item in a CSV should be separated by a comma. Any data item that is a text string should be enclosed in quotation marks. Fine so far, but what happens if you want your text to contain quotation marks too? You need another rule. Two possibilities are:
Rule 1:
If "" appears inside a text field it doesn't end the string but should be interpretted as a single " in the string. Thus ,"", is an empty string and ," "" ", is a space followed by a quotation mark followed by another space, and ,"""", is a single quotation mark.
Rule 2:
Use an "escape" character to indicate that the following character is never to be considered a delimiter. Common choice is backslash (\). This method also means you don't have to delimit strings in quotation marks though many people choose to do it anyway as it avoids confusion btwn numbers and text. If you wanted to a text field containing a single comma, you would enter it like this ,\,, or like this ,"\,", if you keep the quotation marks in. If you want to have a backslash in the text, you enter \\ as the first backslash indicates that the second one is just a peice of text.
editted because all my backslashes dissapeared as they are escape chars in HTML ... duh
>> Edited by ATG on Friday 20th May 10:28
Each data item in a CSV should be separated by a comma. Any data item that is a text string should be enclosed in quotation marks. Fine so far, but what happens if you want your text to contain quotation marks too? You need another rule. Two possibilities are:
Rule 1:
If "" appears inside a text field it doesn't end the string but should be interpretted as a single " in the string. Thus ,"", is an empty string and ," "" ", is a space followed by a quotation mark followed by another space, and ,"""", is a single quotation mark.
Rule 2:
Use an "escape" character to indicate that the following character is never to be considered a delimiter. Common choice is backslash (\). This method also means you don't have to delimit strings in quotation marks though many people choose to do it anyway as it avoids confusion btwn numbers and text. If you wanted to a text field containing a single comma, you would enter it like this ,\,, or like this ,"\,", if you keep the quotation marks in. If you want to have a backslash in the text, you enter \\ as the first backslash indicates that the second one is just a peice of text.
editted because all my backslashes dissapeared as they are escape chars in HTML ... duh
>> Edited by ATG on Friday 20th May 10:28
Developer duly kicked - I've made him rewrite the query to pull the data out 
As for delimiters, the dataset possibiities are finite - just items from fixed lists of text and numeric scores - so I can work around that as long as Excel is ok with the delimiters. I've enclosed everything in quotes, which works ok if I save the file and then open it. But if I try to open it directly by streaming it from the website Excel doesn't interpret it properly
Ah, I'll get there eventually...
As for delimiters, the dataset possibiities are finite - just items from fixed lists of text and numeric scores - so I can work around that as long as Excel is ok with the delimiters. I've enclosed everything in quotes, which works ok if I save the file and then open it. But if I try to open it directly by streaming it from the website Excel doesn't interpret it properly
Ah, I'll get there eventually...
Getting the file into excel is only an intermediate step anyhow - it then gets imported in SPSS for statistical analysis. We've made it clear there's only so much hand-holding we can do for them.Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff