Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CSV Files (XL98, SR2)

    I have a CSV file that I am trying to read with code but am having a problem where there is a comma in a name. For example a CSV file may contain:

    Age,Name,Location
    21,Barbara,London
    ???,"Claus, Santa",North Pole

    Normally one would find the position of the commas within the line of data being read. The issue in this example is with Santa Claus; there is a comma after Claus.

    Does anyone have any suggestion on how to resolve the issue of "commas" within names?

    Thanks,
    John

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Files (XL98, SR2)

    That's why the quotes are around the string. If a comma is between quotes, it is part of the string not a separator. If you are parsing the stirng in your code, you have to handle a string that starts with a quote differently.
    Legare Coleman

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: CSV Files (XL98, SR2)

    What technique are you using to read the file? It might be easier to use ADO, which admittedly has a bit of a learning curve, rather than re-invent a CSV parsing procedure. It works more or less like this:
    <pre>Sub ReadCSV()
    ' Remember to set a Reference to Microsoft ActiveX Data Objects library
    ' Dim and instantiate ADO objects (no need to Set if you Dim As New)
    Dim conCSV As New ADODB.Connection, rsCSV As New ADODB.Recordset
    ' Open connection to CSV file
    conCSV.Open "Provider=MSDASQL;Driver={Microsoft Text Driver " & _
    "(*.txt; *.csv)};DBQ=<font color=blue>C:Testing</font color=blue>"
    ' Open recordset onto CSV file (as a table)
    With rsCSV
    .ActiveConnection = conCSV
    .CacheSize = 10
    .CursorType = adOpenStatic
    .Open "<font color=blue>sample.csv</font color=blue>", , , , adCmdTable 'errors occur if commas are omitted
    End With

    Stop 'YOUR CODE HERE...

    ' Clean up your database and objects
    rsCSV.Close
    Set rsCSV = Nothing
    conCSV.Close
    Set conCSV = Nothing
    End Sub</pre>

    To extract the field values, you use the field name, which ADO infers from the first line of the CSV file. For example:
    <pre>strVar = rsCSV.Fields("MyFieldName").Value</pre>

    or, to loop through all the records,
    <pre>With rsCSV
    While Not .EOF
    Debug.Print .Fields("UserID").Value, _
    .Fields("HoursWork").Value, _
    .Fields("Narrative").Value
    .MoveNext
    Wend
    End With</pre>

    If you need to download ADO because, for example, it was not installed with your version of Windows, you can find it on the MDAC site: http://www.microsoft.com/data/download.htm I use MDAC 2.5 SP2, and have had no problems with it. Hope this helps.

  4. #4
    New Lounger
    Join Date
    Apr 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Files (XL98, SR2)

    What has to change to read in a Tab Separated File?

    Thanks in advance

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Files (XL98, SR2)

    Jefferson,

    Excellent example of using ADO and the Text File driver. I've been thinking for a while about replacing my FSO class mod with an ADO model. What are your thoughts?
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  6. #6
    New Lounger
    Join Date
    Apr 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Files (XL98, SR2)

    Actually I have received a file with the following format which is sort of fixed format with tab separators. I am using * for tab in this case.
    The fields are actually fixed length text with tabs separating them.

    Data *07810 *Next info *...

    These files are too big for excel, so I must read them in with VBA and separate out the data.

    Thanks in advance.

  7. #7
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV Files (XL98, SR2)

    You should find all you need right here

    edited to include this statement:

    Don't forget the parameter: "Format=TabDelimited"
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: CSV Files (XL98, SR2)

    ADO adds overhead, but for structured data, it's nice to be able to use familiar objects like recordsets rather than re-inventing the wheel. I'm not sure if that counts as thoughts or only one thought.

  9. #9
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: CSV Files (XL98, SR2)

    Well, that's dumb. I can't believe it doesn't recognize the tab as the delimiter.

    As for an answer, see Kevin's reference. Alternatively, you also could create a DSN, which is a bit less flexible because the file name is hardcoded, but if this is a one-time need, you get a lot more prompting and hand-holding. This page goes through the steps with screen shots (just disregard the .NET stuff).

    Because most of the necessary information is stored in the DSN, your Open statement is simplified to:
    <pre>conCSV.Open "Provider=MSDASQL;DSN=<font color=blue>MySystemDSN</font color=blue>;UID=;PWD=;"</pre>

    (I'm pretty certain you don't need a UserID for a text file, but...I didn't test this.)

    Hope this helps.

Posting Permissions

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