...

View Full Version : How to insert a comma into a CSV file?



Grant Palin
06-22-2004, 10:52 PM
I'm trying to write some data to a CSV file. But when the data has commas in it, it gets split into multiple cells, since CSV uses the comma as a cell delimiter! How could I avoid this? I tried putting a slash(\) before the comma, thinking to escape it, but the slash showed up in the results, and the data was seperated byt eh commas anyway. I tried using the ASCII equivalent of the comma character (,) to replace commas, but it didn't work either; that ASCII character string showed up anyway, and no commas! I'm doing this in ASP...is there a way to get Excel to ignore specific commas?

glenngv
06-23-2004, 03:36 AM
I think you can't do anything about it.

Does the content has the possibility of having tab in it?
Try creating a TAB-delimited file instead. This has an extension of .txt

Bullschmidt
06-23-2004, 07:19 AM
You can enclose each field with double quotes for uniformity (or nothing at all if the field doesn't contain any double quotes or commas). But then all fields would have double quotes around them and thus would be considered text formated columns in Excel. (But of course a user could easily change the format for a column in Excel.)

For example this would be fine:
"API","Atlantic Pacific, Inc."
"CPI","Cata Pacific, Inc."
"HG","Howard Gains"

glenngv
06-23-2004, 08:22 AM
Bullschmidt, the double quotes don't show up in Excel. That's good.

If a field contains double quotes and commas, you need to escape the quotes by doubling it.

"API","Atlantic ""Pacific"", Inc."
"CPI","Cata ""Pacific"", Inc."
"HG","Howard Gains"

That produces this in Excel:


API Atlantic "Pacific", Inc.
CPI Cata "Pacific", Inc.
HG Howard Gains


If you don't expect a field to have commas in it, you can omit the double quotes and if it can contain double quotes, you don't have to double it.


But creating a tab-delimited file should still be considered an option as it is unusual for the data to have tabs and it is easier (straightforward) to implement.

Response.write "1,1" & vbTab & "1,2" & vbTab & "1,3" & vbCrLf
Response.write "2,1" & vbTab & "2,2" & vbTab & "2,3" & vbCrLf

Bullschmidt
06-23-2004, 12:51 PM
Thanks Glenn!

Grant Palin
06-23-2004, 05:12 PM
The data is not likely to have tabs in it; I was creating a routine to save results from an online query to a file (mostly numbers). The CSV creation/saving was working fine until I tried to include some footnotes in the file as well, which of course can contain commas (not too sure about quotes - MAYBE).

However, I tried your suggestion, glenngv. I tied replacing the commas seperating data cells with tabs, and changed the routine to save the data as a text file instead of a CSV file. I was then able to import that file into Excel, and the footnotes containing commas were in one cell, as expected. So that's good.

I had the following code at the top of the routine before:


Response.ContentType = "application/save"
Response.AddHeader "Content-Disposition", "filename=output.csv;"


And I now have:


Response.ContentType = "application/save"
Response.AddHeader "Content-Disposition", "filename=output.txt;"


When I had it set as CSV before, that forced the browser to prompt the user to save the file. But when set as txt, the browser just opens the file in Notepad. Do you know of a way to prompt the user to save the text file?

Grant Palin
06-23-2004, 05:36 PM
Just to try it, I also tried the other suggested method of enclosing the text in quotes; when I opened the CSV file (I switched back to CSV to try this) in Excel, I didn't see the double quotes surrounding the whole thing, and the commas appeared as they should, inside the text strings. One of the lines did have double quotes in it, and I had to go into that particular sting in ASP and double those double quotes. :rolleyes:

And it worked. That was all right, and it would allow me to stay with the CSV format, which my manager wanted anyway (no having to import the results text file into Excel; just open the CSV file).

The way I had this set up was, I had an array of text strings that I wanted to include into the file after the query data. I just looped through that array and wrote each item on a new line in the file. The downside of this is, the same footnotes also need to appear on the HTML page with the results as well, when the query results are viewed through the browser.

I'm wondering if I can have those footnotes in a seperate text file maybe, and read that line by line and generate an ordered list for the HTML page, and write the files to the CSV file. This way, I wouldn't have to have two seperate versions of the same footnotes.

And when doing the CSV file, I could just do a replace on each line, replacing one double quote with two.

Grant Palin
06-23-2004, 06:48 PM
Building on my previous post, I went ahead and put all the footnotes in a seperate text file. Then I set up a seperate function to read that file line by line, and depending on whether I'm doing an HTML list, I put each line inside an LI element. When done reading the file, I just drop the ordered list into the page. If I'm not doing the HTML version, then I just seperate the lines by newlines (vbCrLf) and write each line to the CSV file.

So I've gotten what I wanted. I've still got the useful CSV format, and I've got my footnotes bit working too. Thanks again for the suggestion about using double quotes! :thumbsup:

glenngv
06-24-2004, 03:17 AM
Response.ContentType = "application/save"
Response.AddHeader "Content-Disposition", "filename=output.csv;"


And I now have:


Response.ContentType = "application/save"
Response.AddHeader "Content-Disposition", "filename=output.txt;"


When I had it set as CSV before, that forced the browser to prompt the user to save the file. But when set as txt, the browser just opens the file in Notepad. Do you know of a way to prompt the user to save the text file?
FWIW...
Try:

Response.AddHeader "Content-Disposition","attachment; filename=""output.txt"""

Grant Palin
06-24-2004, 04:53 PM
Thanks glenngv, but I think I'm going to stick with the way I figured out (see my previous posts). Thanks anyway!

M@rco
06-24-2004, 08:08 PM
Streaming files to the browser? Want to force a download (or not)?

*ahem*

http://marcustucker.com/blogold/200404archive001.asp#1081202785001

:D

Grant Palin
06-24-2004, 08:43 PM
A little self-promotion, eh? :D

That looks interesting, but seems to me that it's more for binary data...is that right? For me, I'm just saving text and numbers. And I'm not doing it from a saved file. I'm doing it from a database query.

M@rco
06-24-2004, 09:21 PM
It's not only suitable for binary data/files, it's merely that that's what most people want to use it for, and since lots of people tend to use outdated and inefficient methods of manipulating binary data in VBScript (i.e. the FSO in conjunction with ChrB/AscB/MidB) I provided a wrapper to use the far more efficient ADO.Stream object instead and wrote my main sub to expect this accordingly.

But it's also irrelevant where the data comes from - while (as it stands) the function expects a stream (binary array) of data, you could easily modify it to accept a plain string instead. Or alternatively, since you are dynamically constructing the text output, you should do so using an ADO.Stream object in text mode, thereby eliminating string concatenation issues AND allowing you to use the resulting data stream directly, as the input for my function.

;) :D

Grant Palin
06-25-2004, 07:20 PM
Thank you for your suggestion M@rco, but I'm not sure I needed it...I fixed the problem I had before, and the file download is working fine now, and the CSV file is showing up the way it should be. And it works quickly too. I've just got a simple routine for saving the results to a CSV file, and it works well enough. Your version just looks more complicated than it would need to be for my situation (no offense - I do appreciate your suggestions! :) ).

M@rco
06-26-2004, 12:27 AM
Thank you for your suggestion M@rco, but I'm not sure I needed it...I fixed the problem I had before, and the file download is working fine now, and the CSV file is showing up the way it should be. And it works quickly too. I've just got a simple routine for saving the results to a CSV file, and it works well enough. Your version just looks more complicated than it would need to be for my situation (no offense - I do appreciate your suggestions! :) ).Cool, no probs. ;)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum