Question Re: Export data to csv SQL Anywhere 8 in Technology Q&A

Export data to csv SQL Anywhere 8

Former Member — Thu, 03 Nov 2016 12:09:25 GMT

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

Re: Export data to csv SQL Anywhere 8

Former Member — Thu, 03 Nov 2016 16:09:32 GMT

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

Re: Export data to csv SQL Anywhere 8

VolkerBarth — Thu, 03 Nov 2016 17:32:14 GMT

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

Re: Export data to csv SQL Anywhere 8

Former Member — Thu, 03 Nov 2016 17:39:09 GMT

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

Re: Export data to csv SQL Anywhere 8

Former Member — Fri, 04 Nov 2016 04:30:11 GMT

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

Re: Export data to csv SQL Anywhere 8

Former Member — Fri, 04 Nov 2016 09:45:02 GMT

Using QUOTE '"' resolved my problem. Thanks