Handle comma to keep field in one column when saving to csv from sql query

1

Hi all,

How do I handle my sql query where one of the fields with comma in between text so when I save the result set to csv, it does create 2 columns but I want to keep it to 1 column. The current data type is VARCHAR but when I save the result set to csv I see two columns. Anyone?

Thanks all

2

Correct syntax for embedded commas, and then embedded double-quotes, is:

COL1,COL2,COL3
COL1,"Col2a,Col2b",COL3
COL1,"Col2a,""Col2x,Col2y"",Col2b",COL3

but not all CSV import routines honour that complete syntax, in which case you are hosed. Change the embedded commas to something else (which is a right-royal-pain to implement on every single column, of every single table, that you export.

Use XML instead. Problem solved.

Better still, use direct database-to-database connection instead, and then things like DATES don't get "formatted" on Export and then "parsed" on Import, with all the nightmare of dd/mm/yy and mm/dd/yy etc. that that entails.

Either way, CSV should have died a death bacjk in the 70's, but sadly it still hasn't done.

Chinese Characters anyone? Line Break in the data?

2 Likes
3

I have moved over to tab delimited. not sure if it helps in this situation

1 Like
4

Yes, definitely. Still the same problem if you want the delimiter in the data, but IME its rare to have TAB in the data that users enter into a regular data entry form - if they press TAB it takes them to the next field. But TAB much more likely to be "safe", in the data stream, than , or "

However, if they cut & paste from, say, Word then any TABs may well survive, and be pasted into the field, and of course in that case a) they are invisible to the user (they will probably not expand to ROW % 8, so will just look like a single space), and then most definitely will screw up the exported data.

At the least I'd check if there are already any TABs in any of the VARCHAR columns that you might export (now, or in future), and also if it is possible to paste a TAB into a data entry form. Maybe you can easily disallow TAB as a data entry "character" as part of existing validation systems / rules? I don't suppose users actually want to have TAB in normal varchar (rather than varchar(MAX) ) columns, so if they get there it will be unintended and unrequired.

1 Like
5

Thank you kristen and Yosiasz