Parse CSV file into table using an array

For VBScript, you can use something like this.  This should also generate the insert query for you and display the query per row.

Regards,

Rob.

Set objFSO = CreateObject("Scripting.FileSystemObject")
Const intForReading = 1
strCSVFile = "C:\Temp\MyFile.csv"
strDelim = ","
Set objFile = objFSO.OpenTextFile(strCSVFile, intForReading, False)
While Not objFile.AtEndOfStream
   arrValues = Split(objFile.ReadLine, strDelim)
   strSQL = "INSERT INTO <TABLE> SET(<field1>,<field2>) VALUES("
   For Each strValue In arrValues
      If Right(strSQL, 1) = "(" Then
         strSQL = strSQL & strValue
      Else
         strSQL = strSQL & "," & strValue
      End If
   Next
   strSQL = strSQL & ")"
   MsgBox strSQL
Wend
objFile.Close
MsgBox "Done"

Select allOpen in new window

Rob,
For some reason the code did not work, it never inserted the data into the table.  I am giving more specific information.  The file is named 060108 TRANSACTIONS.CSV.  Of course the date will change each day.  A header is on the first line and starts with TRANSACTIONS REPORT which will need to be removed.  The field order is (TRANS DATE, ACCOUNT NO, LAST NAME, FIRST NAME, CURRENT FC, TRANS FC, FLAG, PTYPE, PAYMENT, ITEM NO, DESCRIPTION, CURRENT BALANCE) which will be inserted into a table  called [TRANSACTIONS] and will have the same field names as the file.  Thanks.

Rob,

this works great.  Thanks for your help. ; )

Oh great! So you've already got code to connect to the database.....that's good....  :- )

Regards,

Rob.

Yes.  Thanks for your great help.