Export data to csv SQL Anywhere 8

[former_SQLA_member1695034]

I am using ISQL with SQL Anywhere 8 to export some data from tables to csv. My query looks something like this:

SELECT ColumnA, ColumnB, ColumnC FROM sometable WHERE ....; OUTPUT TO 'c:\\file.csv' FORMAT ASCII QUOTE '';

When there is a comma in the value of any of the columns, it causes the data to get truncated and shift. For example, if ColumnA has the text 'some text, more text' then ColumnA in the resulting spreadsheet will be 'some text' while ColumnB will be 'more text'. This is messing up the formatting. I've tried using different delimiters with no luck. The goal here is to get the csv data into a nicely formatted Excel spreadsheet.

Thanks, Tom

[former_SQLA_member1694885 · Accepted solution]

Hi Tom, that's what the quotes are for. If the separator character appears within the quotes, it will be considered part of the string value. When importing the .csv into Excel, you can also specify the quote, causing Excel to remove them from the actual value.

It is the fundamental issue with csv that without quotes, you need a separator character (or sequence) that can't occur in values.

HTH

Volker

DB-TecKnowledgy

[VolkerBarth · Accepted solution]

AFAIK, Excel assumes the double quote ('"') as default quote, so OUTPUT ... QUOTE '"' may be the easiest/most appropriate choice.

[former_SQLA_member1695034 · Accepted solution]

So what do I change in the OUTPUT TO section to prevent this from happening?

[Former Member · Accepted solution]

You can specify the delimiter too, adding: delimited by 'somecharacters'; Both output and input 🙂

[former_SQLA_member1695034 · Accepted solution]

Using QUOTE '"' resolved my problem. Thanks