Excel CSVs and commas in text

Starr Duskk
Starr Duskk used Ask the Experts™
on
I am exporting a file in excel csv format. However, there are text fields that contain commas, so the comma delimeter sends them off to the next column prematurely.

How do I either change the delimiter to something else so that I can keep my text and commas in the correct column. Or how do I escape the commas.

And  do I have to escape quotes and wrap quotes? I've been googling and finding all kinds of info on quotes, but nothing on fixing my comma issue.

thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Glanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015
Commented:
"Text-qualify" the fields. This means that fields containing inline commas are quoted.

e.g.

each,field,separated,by,comma,"except for, in the case of inline commas, those which are quoted"

In the above, there are only 6 fields.

The rub comes in when you try to do the export, from what I am reading. Apparently Excel doesn't give you an easy way to export text-qualified files. Perhaps this has changed in the newer versions--I don't know. You may try some of the following:

http://support.microsoft.com/default.aspx?scid=kb;en-us;291296&Product=xlw
http://www.mcgimpsey.com/excel/textfiles.html
Export a file in excel csv format
1,"Selena, Mines","2321, East Lamar Blvd, United State"
2,"Tameka, Nicolosi","88 East 99th Street, United State"
3,"Karina, Burkley"," 452 5th Avenue, United State"

Use TextFieldParser instead of split

Output

No: 1 Name: Selena, Mines, Address: 2321, East Lamar Blvd, United State
No: 2 Name: Tameka, Nicolosi, Address: 88 East 99th Street, United State
No: 3 Name: Karina, Burkley, Address: 452 5th Avenue, United State

Sub Main()

        Dim filename As String = "C:\Test.csv"
        Dim fields As String()
        Dim delimiter As String = ","
        Using parser As New TextFieldParser(filename)
            parser.SetDelimiters(delimiter)
            While Not parser.EndOfData

                ' Read in the fields for the current line
                fields = parser.ReadFields()

                Console.WriteLine(String.Format("No: {0} Name: {1}, Address: {2}", fields(0), fields(1), fields(2)))


            End While
        End Using

        Console.ReadKey()

    End Sub

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial