parsing a comma delimited file

sam · September 18th, 2003, 03:35 PM

Does anyone have code for parsing a comma delimited file?
Thanks,
Sam

pgtips · September 19th, 2003, 02:41 AM

The Split function (VBS or JS version) will take care of the parsing each line for you. Alternatively you could use the Text ODBC driver.

hth
Phil

sam · September 22nd, 2003, 09:50 AM

Dear Phil,
Unfortunately split wont work in the case of a comma in between 2 quotes. I'm not quite sure what you mean by the Text ODBC driver or if I could use this in a shred hosting environment.

I wrote this over the weekend it needs a little more work but I could use some input before continuing.

thanks,
Sam Lewitan

    '::::::::::::::::::::::::::::::::::::::::::::::::: :::::::::::::::::::::::::::::::::
    ':::
    '::: Function: Parse_CVS(text)
    '::: AUTHOR: S. Lewitan
    '::: WebSite: www.ebcpro.com
    '::: DATE: Sep-2004
    '::: PURPOSE: Excepts a comma delimited file and returns a two
    '::: dimensional array.
    ':::
    '::: Note: Please fell free to use and improve. EBC Programming, Inc.
    '::: excepts no liability for the code herein.
    ':::
    '::: (C)2003 EBC Programming, Inc.
    ':::
    '::::::::::::::::::::::::::::::::::::::::::::::::: :::::::::::::::::::::::::::::::::

    'Excepts a comma delimited file
    Function Parse_CVS(text)
        bfirstLine = true
        arryLines = split(text,chr(13))
        xCt = ubound(arryLines)
        for arryCt = 0 to xCt - 1
            if len(arryLines(arryCt)) > 0 then
                arrySections = Parse_ComaDelimLine(arryLines(arryCt))
                if bfirstLine then
                    'I only call redimm once inorder to protect the info and save on time
                    redim arryCSV(xCt,ubound(arrySections))
                    bfirstLine = false
                end if
                for yCt = 0 to ubound(arrySections)
                    arryCSV(arryCt,yCt) = arrySections(yCt)
                next
            end if
        next
        Parse_CVS = arryCSV
    End Function

    'Excepts a comma delimited line form Function Parse_CVS
    Function Parse_ComaDelimLine(strLine)
        ResultsList = ""
        bInTheMiddle = false
        'I reconstruct the comma delimited line protecting the commas and quotes which
        'need to keepted with my own delimiter and then repslit it.
        MyDelim = "|~|~|"
        aQuote = chr(34)
        LineLen = len(strLine)

        for LenCt = 1 to LineLen
            aChr = mid(strLine,LenCt,1)
            if (aChr <> ",") and (aChr <> aQuote) then
                ResultsList = ResultsList & aChr
             else

                 if (aChr = ",") and (bInTheMiddle = false) then
                    ResultsList = ResultsList & MyDelim
                 elseif (aChr = ",") and (bInTheMiddle = true) then
                        ResultsList = ResultsList & aChr
                end if

                if (aChr = aQuote) and (LenCt <> LineLen) then
                    if (bInTheMiddle = true) then
                        if (mid(strLine,LenCt+1,1) = aQuote) then '("")
                            ResultsList = ResultsList & aChr
                            LenCt = LenCt + 1 'Ignore next "
                         else
                             bInTheMiddle = false
                        end if '(mid(strLine,ct+1,1) = aQuote)
                     else 'meanning (bInTheMiddle = false)
                         if (mid(strLine,LenCt+1,1) = aQuote) then
                             ResultsList = ResultsList & aChr
                             LenCt = LenCt + 1 'Ignore next "
                            else
                             bInTheMiddle = true
                         end if
                    end if '(bInTheMiddle = true)
                end if '(aChr = aQuote) and (LenCt < LineLen - 1)
            end if '(aChr <> ",") or (aChr = aQuote)
        next

        Parse_ComaDelimLine = split(ResultsList,MyDelim)
    End Function

pgtips · September 23rd, 2003, 03:18 AM

OK, so you've got a nasty csv to deal with. In this case its definitely worth investigating whether you can use a Text driver, which just allows you to treat a text file as a database table. It will handle commas and quotes within strings. As an example, consider this csv file:

Code:

10247,"nice string",4.274
9036,"string, with a comma",195.33
1285,"one with ""quote"", and comma in",7285.667
225,"nice string to finish",502.11

the asp code you would need for the text driver would be something like this:

Code:

    Set oConn = Server.CreateObject("ADODB.Connection")

    ' the directory goes in the connection string
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=c:\inetpub\wwwroot\csvfiles\;" & _
       "Extended Properties=""text;HDR=No;FMT=Delimited"""

    Set oRs = Server.CreateObject("ADODB.Recordset")

    ' the filename goes in the SQL
    ' (dont forget to define these ADO constants)
    oRs.Open "SELECT * FROM pg.csv", oConn, _
             adOpenForwardOnly, adLockReadOnly, adCmdText

    ' here we just write the contents out to an html table
    Response.Write("<table>")
    Do While Not oRs.EOF

        Response.Write("<tr>")
        For n = 0 To oRs.Fields.Count - 1
            Response.Write("<td>")
            Response.Write(oRs.Fields(n).Value)
            Response.Write("</td>")
        Next
        Response.Write("</tr>")
        oRs.MoveNext

    Loop
    Response.Write("</table>")

    oRs.Close
    oConn.Close

    Set oRs = Nothing
    Set oConn = Nothing

hth
Phil

sam · October 24th, 2003, 09:00 AM

Dear Phil,
Sorry I didn't respond till now I just never had time to try out your code. I put it to a test and found that not all the values came threw. It happened in cases where the columns were 99% numeric except for one or two rows. The one or two rows of alphanumeric were null. Any suggestions since your way I'm sure is much faster then my code.
As far as my code is concerned I found a bug that the first value of the row has either single character which I think is a new line and or carriage return. I assume it has to do with "arryLines = split(text,chr(13))" but I tried "arryLines = split(text,vbCRLF)" but that doesn't work at all.
Any suggestions any of the above is appreciated.
Thanks,
Sam

pgtips · October 24th, 2003, 09:20 AM

OK, that makes sense. The problem is that the driver will try to determine the data type of each column and for those columns it's obviously determined that it should be numeric, so when it comes up against any non-numeric entries it effectively throws them out.

The solution is to tell the driver the datatype of each column (I assume you want alphanumeric for those problem columns?). You can do this by including a schema.ini file along with your text file, see here http://msdn.microsoft.com/library/de...cjetsdk_98.asp for the format of that file (its pretty straightforward). Here's an example of a schema.ini file:
[Text ISAM]
ColNameHeader=False
Format=CSVDelimited
MaxScanRows=25
CharacterSet=OEM

[theFileName.csv]
ColNameHeader=False
Format=CSVDelimited
MaxScanRows=25
CharacterSet=OEM
Col1=Field1Name Long
Col2=Field2Name Char
Col3=Field3Name Long
Col4=Field4Name Integer
Col5=Field5Name Float
Col6=Field6Name Float
Col7=Field7Name Float
Col8=Field8Name Float
Col9=Field9Name Float

As to the probs with your code, I wouldn't expect the split(text, chr(13)) to work properly, because each line ends with the cr/lf pair (chrs 13/10) so what you are seeing is the chr(10) showing up on the start of the next line.

At the mo I'm at a loss to explain why split(text, vbCrLf) doesn't work, I would've expected that to do the biz.

hth
Phil