Reading csv files

[Guest_imported]

Very much a beginner here.
My question is how do I read a csv file?
ReadAll and ReadLine won't do it for me ...
any help much appreciated
Thanks

 

[sfriday]

Why won't ReadAll and ReadLine do it?? what are you trying to achieve??


Regards
Steve Friday

 

[Guest_imported]

I want to be able to read a record extract the variables from each record in the file and then load the resultant variables into another application

 

[sfriday]

The code below will read a CSV file and place each section in an array, it identifies each section by using the split command and the comma as a delimiter.

Dim Root
Dim Dir
Const ForReading = 1
Const ADS_PROPERTY_UPDATE = 2
Set objTextFile = objFSO.OpenTextFile "c:\test.csv", ForReading)

Do Until objTextFile.AtEndOfStream
strNextLine = objTextFile.ReadLine
arruserList = Split(strNextLine , ",&quot
first = arruserList(0)
second = arruserList(1)
msgbox first
msgbox second
Loop

Set objTextFile = Nothing
Regards
Steve Friday

 

[ibbguy]

This is okay if your values are all numeric, e.g., each line does not contain quoted strings (with or without embedded commas.) However, in conjunction with a read routine, you could pass each line of input (result of ReadLIne) to this function, along with the column you want extracted (1, 2, 3, etc.). I use this function a lot in Outlook. Of course, reading and interpreting one line at a time (ReadLine) does not address quoted strings containing CR or LF such as you might find in BodyText and other text fields or text boxes, as they would naturally span several "lines" in the .CSV file. The parsing function below would remain the same in any event; only the construction of the unparsed "line" would change.

No doubt some error checking could be added here (such as avoiding an error due to column number being passed which is greater than the actual number of columns in the input line). Perhaps even the code could be made more efficient. In either case, please post your suggestions and/or enhancements.

Function ParseInput(ByVal strInput, ByVal intCol)
   Dim intCounter, intOffset
   intCounter = 1
   intOffset = 0
   While intCounter < intCol
      If Left(strInput, 1) = Chr(34) Then
         intOffset = InStr(strInput, Chr(34) & &quot;,&quot;) + 1
      Else
         intOffset = InStr(1, strInput, &quot;,&quot;)
      End If
      strInput = Right(strInput, Len(strInput) - intOffset)
      intCounter = intCounter + 1
   Wend
   If Left(strInput, 1) = Chr(34) Then
      strInput = Right(strInput, Len(strInput) - 1)
      If Instr(strInput, chr(34) & &quot;,&quot;) = 0 then
         intOffset = len(strInput) - 1
      Else 
         intOffset = InStr(strInput, Chr(34) & &quot;,&quot;) - 1
      End If
   Else
      If Instr(strInput, &quot;,&quot;) = 0 Then
         intOffset = len(strInput)
      Else
         intOffset = InStr(strInput, &quot;,&quot;) - 1
      End If
   End If
   strInput = Left(strInput, intOffset)
   ParseInput = strInput
End Function