Start a new topic

csv export not valid for excel

[This topic is migrated from our old forums. The original author name has been removed]

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.

[This reply is migrated from our old forums.]

Re: csv export not valid for excel
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
[This reply is migrated from our old forums. The original author name has been removed]

Re: csv export not valid for excel
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.
[This reply is migrated from our old forums.]

Re: csv export not valid for excel
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
[This reply is migrated from our old forums. The original author name has been removed]

Re: csv export not valid for excel
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.
[This reply is migrated from our old forums.]
[Attachment has been removed.]

Re: csv export not valid for excel
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
[This reply is migrated from our old forums. The original author name has been removed]

Re: csv export not valid for excel
Our data contains tab, space, comma, newline, single quote, and double quote characters; so double quoting the text is necessary.
[This reply is migrated from our old forums. The original author name has been removed]

Re: csv export not valid for excel
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
[This reply is migrated from our old forums. The original author name has been removed]

Re: csv export not valid for excel
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
[This reply is migrated from our old forums.]

Re: csv export not valid for excel
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
[This reply is migrated from our old forums.]
[Attachment has been removed.]

Re: csv export not valid for excel
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
[This reply is migrated from our old forums.]

Re: csv export not valid for excel
Hi, This is now fixed in the 6.5.4 version. http://www.minq.se/products/dbvis/download/ Best Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: csv export not valid for excel
Excellent! I wish I would have checked back sooner. After some basic testing, it seems to be functioning as expected in the new version.