The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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

asked 03 Nov '16, 12:09

Thomas%20Rolseth's gravatar image

Thomas Rolseth
111
accept rate: 0%


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

permanent link

answered 03 Nov '16, 16:09

Volker%20DB-TecKy's gravatar image

Volker DB-TecKy
256137
accept rate: 36%

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

(03 Nov '16, 17:32) Volker Barth

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

(03 Nov '16, 17:39) Thomas Rolseth

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

(04 Nov '16, 04:30) DRauber

Using QUOTE '"' resolved my problem. Thanks

(04 Nov '16, 09:45) Thomas Rolseth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×24
×15
×5

question asked: 03 Nov '16, 12:09

question was seen: 151 times

last updated: 04 Nov '16, 09:45