Home » DbVisualizer Forums » DbVisualizer » DbVisualizer - Support

Thread: csv export not valid for excel

This question is answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 12 - Pages: 1 - Last Post: Jul 30, 2009 9:28 PM Last Post By: Mark Wenzel
Mark Wenzel

Posts: 5
Registered: 11/24/08
csv export not valid for excel
Posted: Nov 24, 2008 4:58 PM
 
   thread.click_reply Reply
I have searched the forums for csv export and have not been able to find a way to export the query results in "standard" csv format that Excel can handle.

Any idea how to save the following result in a csv file quoting with double quotes:
select 'this is a test of a comma, a ", and a ''' test from dual;

Excel escapes the double quotes with another double quote.
Roger Bjarevall


Posts: 5,082
Registered: 12/17/04
Re: csv export not valid for excel
Posted: Nov 25, 2008 2:32 PM    global.in_response_to.tooltip in response to: Mark Wenzel
 
   thread.click_reply Reply
Mark,

Have you tried the Export feature in the result set grid right click menu? There are various options for "Quote Text Data" for CSV output format.

Best Regards

Roger
Mark Wenzel

Posts: 5
Registered: 11/24/08
Re: csv export not valid for excel
Posted: Nov 25, 2008 3:51 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
 
   thread.click_reply Reply
Yes, I have tried that. When using double quotes, they don't escape the double quote inside the string:
"this is a test of a comma, a ", and a '"

With ansi, they correctly escape the single quote with an extra single quote:
'this is a test of a comma, a ", and a '''

However, excel doesn't support the ansi format.

If they made double quote work like ansi (escape any double quotes with an extra double quote):
"this is a test of a comma, a "", and a '"

Then it would be perfect.
Roger Bjarevall


Posts: 5,082
Registered: 12/17/04
Re: csv export not valid for excel
Posted: Nov 26, 2008 11:55 AM    global.in_response_to.tooltip in response to: Mark Wenzel
 
   thread.click_reply Reply
Mark,

I should have asked this initially but what are you expecting when importing the result from the SQL:

select 'this is a test of a comma, a ", and a ''' test from dual;

Which becomes:

this is a test of a comma, a ", and a '

Should it go into a single cell preserving all quotes or should it be separated based on the commas?

(What version of Excel are you using?)

Best Regards

Roger
Mark Wenzel

Posts: 5
Registered: 11/24/08
Re: csv export not valid for excel
Posted: Dec 2, 2008 3:59 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
 
   thread.click_reply Reply
It should go into a single cell preserving all quotes. I use Excel 2004 for Mac and Excel 2002 for Windows; both treat the csv files the same way.

If the cell is encoded as: "this is a test of a comma, a "", and a '", it will be treated as a single cell. Excel double quotes any cell that contains a comma, double quote, or whitespace that causes a cell be multiple lines (like a newline character). When the cell contains a double quote, it escapes any internal double quote with another double quote.

If the text data is quoted with double quotes and any internal double quotes are replaced with two double quotes, then excel will be able read it just fine.
Roger Bjarevall


Posts: 5,082
Registered: 12/17/04
Re: csv export not valid for excel
Posted: Dec 4, 2008 11:11 AM    global.in_response_to.tooltip in response to: Mark Wenzel
 
   thread.click_reply Reply
 global.attachment 123.png (203.5 KB )
Mark,

Thanks! Still I may not fully understand the problem or if there is one.

Check the attached compound image.

1)
This image show the data in the table in DbVisualizer. Row 1 and 4 are identical except that there is an extra double quote before the original double quote.

2)
This image is the same table exported in CSV format. TAB is between each of the columns. (Text data is not quoted as specified in the Export feature).

3)
Loading the file in Excel (make sure the file extension is .txt) launches the text file loading utility in which I specify what delimiters are in the file. You see the final result in image 3. As you can see, it is identical with how the data is presented in DbVis (image 1).

Best Regards

Roger
Mark Wenzel

Posts: 5
Registered: 11/24/08
Re: csv export not valid for excel
Posted: Dec 5, 2008 5:30 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
 
   thread.click_reply Reply
Our data contains tab, space, comma, newline, single quote, and double quote characters; so double quoting the text is necessary.
Peter Nagel

Posts: 36
Registered: 02/17/06
Re: csv export not valid for excel
Posted: Dec 16, 2008 3:51 PM    global.in_response_to.tooltip in response to: Mark Wenzel
 
   thread.click_reply Reply
This is no problem of DBVis but of Excel (or the way you import the csv).

If i set quoting style to ANSI for the export and set the text recognition char (or what ever it is called in english, in german it is "Texterkennungszeichen" and is a dropdownlist with ' and " and {none} as values) to the single quote ', then everything is fine.

If Excel did not offer you the import assistent on opening the file, save it as *.txt.

BTW: newline in data is a real headache for csv... and i don't know wether there is any way import this correct, because newline is the seperator for rows.

Hope, this helps :-)

Bye, Peter
jcasazza507

Posts: 1
Registered: 01/16/09
Re: csv export not valid for excel
Posted: Jan 16, 2009 4:11 PM    global.in_response_to.tooltip in response to: Mark Wenzel
 
   thread.click_reply Reply
I am having the same problem. It is not an Excel problem, I cannot import the file to other applications. The csv file that is created does not comply with CSV standards.

CSV standards:
Embedded double-quotes - Embedded double-quote characters must be doubled, and the field must be delimited with double-quotes.

Embedded line-breaks - Fields must be surounded by double-quotes.
Always Delimiting - Fields may always be delimited with double quotes, the delimiters will be parsed and discarded by the reading applications.

Here are my export settings:

format="csv"
CsvColumnDelimiter = ","
CsvRowDelimiter = "\r\n"
QuoteTextData = "Double"

My text data also contains new lines, so the field is surrounded by double quotes. If the data also contains a quote it must be translated to "".

The workaround for now is to replace " with "" in your select statement on the fields that cause problems.

I am hoping DBVisualizer will realize this as a problem and fix soon.

Jim
Roger Bjarevall


Posts: 5,082
Registered: 12/17/04
Re: csv export not valid for excel
Posted: Jan 16, 2009 4:38 PM    global.in_response_to.tooltip in response to: jcasazza507
 
   thread.click_reply Reply
Jim,

Thanks for the details.

From the web:

"No general standard specification for CSV exists. Variations between CSV implementations in different programs are quite common and can lead to interoperation difficulties."

We will however consider being as compliant as possible with the most common CSV recommendations in a future version.

Regards

Roger
Roger Bjarevall


Posts: 5,082
Registered: 12/17/04
Re: csv export not valid for excel
Posted: Jan 20, 2009 5:20 PM    global.in_response_to.tooltip in response to: jcasazza507
 
   thread.click_reply Reply
Jim,

Just to inform you that we will take care of the embedded double quote problem in the next 6.5.4 version. You will then be able to specify:

format="csv"
CsvColumnDelimiter = ","
CsvRowDelimiter = "\r\n"
QuoteTextData = "Double"
QuoteDuplicateEmbedded = "true"

Check the attached image for changes in the Export grid GUI.

Best Regards

Roger
Roger Bjarevall


Posts: 5,082
Registered: 12/17/04
Re: csv export not valid for excel
Posted: Feb 6, 2009 11:06 AM    global.in_response_to.tooltip in response to: Roger Bjarevall
 
   thread.click_reply Reply
Hi,

This is now fixed in the 6.5.4 version.

http://www.minq.se/products/dbvis/download/

Best Regards

Roger
Mark Wenzel

Posts: 5
Registered: 11/24/08
Re: csv export not valid for excel
Posted: Jul 30, 2009 9:28 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
 
   thread.click_reply Reply
Excellent! I wish I would have checked back sooner. After some basic testing, it seems to be functioning as expected in the new version.

Point your RSS reader here for a feed of the latest messages in all forums