Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 15 of 15
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Location
    Victoria, BC, Canada
    Posts
    962
    Thanks
    0
    Thanked 1 Time in 1 Post

    How to insert a comma into a CSV file?

    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?

  • #2
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    10,968
    Thanks
    0
    Thanked 236 Times in 233 Posts
    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

  • #3
    Regular Coder
    Join Date
    Aug 2002
    Location
    USA
    Posts
    478
    Thanks
    0
    Thanked 2 Times in 2 Posts
    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"
    J. Paul Schmidt
    www.Bullschmidt.com - Freelance Web and Database Developer
    www.Bullschmidt.com/DevTip.asp - Classic ASP Design Tips

  • #4
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    10,968
    Thanks
    0
    Thanked 236 Times in 233 Posts
    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:
    Code:
    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

  • #5
    Regular Coder
    Join Date
    Aug 2002
    Location
    USA
    Posts
    478
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Thanks Glenn!
    J. Paul Schmidt
    www.Bullschmidt.com - Freelance Web and Database Developer
    www.Bullschmidt.com/DevTip.asp - Classic ASP Design Tips

  • #6
    Regular Coder
    Join Date
    Jun 2002
    Location
    Victoria, BC, Canada
    Posts
    962
    Thanks
    0
    Thanked 1 Time in 1 Post
    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:
    PHP Code:
        Response.ContentType "application/save"
        
    Response.AddHeader "Content-Disposition""filename=output.csv;" 
    And I now have:
    PHP Code:
        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?

  • #7
    Regular Coder
    Join Date
    Jun 2002
    Location
    Victoria, BC, Canada
    Posts
    962
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.

    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.

  • #8
    Regular Coder
    Join Date
    Jun 2002
    Location
    Victoria, BC, Canada
    Posts
    962
    Thanks
    0
    Thanked 1 Time in 1 Post
    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!

  • #9
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    10,968
    Thanks
    0
    Thanked 236 Times in 233 Posts
    Quote Originally Posted by Grant Palin
    PHP Code:
        Response.ContentType "application/save"
        
    Response.AddHeader "Content-Disposition""filename=output.csv;" 
    And I now have:
    PHP Code:
        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"""

  • #10
    Regular Coder
    Join Date
    Jun 2002
    Location
    Victoria, BC, Canada
    Posts
    962
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks glenngv, but I think I'm going to stick with the way I figured out (see my previous posts). Thanks anyway!

  • #11
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    Streaming files to the browser? Want to force a download (or not)?

    *ahem*

    http://marcustucker.com/blogold/2004...#1081202785001

    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #12
    Regular Coder
    Join Date
    Jun 2002
    Location
    Victoria, BC, Canada
    Posts
    962
    Thanks
    0
    Thanked 1 Time in 1 Post
    A little self-promotion, eh?

    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.

  • #13
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.

    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #14
    Regular Coder
    Join Date
    Jun 2002
    Location
    Victoria, BC, Canada
    Posts
    962
    Thanks
    0
    Thanked 1 Time in 1 Post
    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! ).

  • #15
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Grant Palin
    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.
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •