• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 845
  • Last Modified:

Parse CSV file into table using an array

I need some generic code in visual basic that will read a .CSV line by line using an Array, and inserting into a table using an SQL statement.
0
OB1Canobie
Asked:
OB1Canobie
  • 3
  • 3
1 Solution
 
RobSampsonCommented:
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"

Open in new window

0
 
OB1CanobieAuthor Commented:
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.
0
 
RobSampsonCommented:
OK, see if this works for you.......do you get the MsgBox?  This code will *not* insert data into anything at the moment, because it does not contain any database connection code....we'll get to that once the query looks correct....

Regards,

Rob.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Const intForReading = 1
strDate = Right("0" & Month(Date), 2) & Right("0" & Day(Date), 2) & Year(Date)
strCSVFile = strDate & " TRANSACTIONS.CSV"
strDelim = ","
Set objFile = objFSO.OpenTextFile(strCSVFile, intForReading, False)
' Skip the first line because it's just a header
If Not objFile.AtEndOfStream Then objFile.SkipLine
While Not objFile.AtEndOfStream
   arrValues = Split(objFile.ReadLine, strDelim)
   strSQL = "INSERT INTO [TRANSACTIONS] "
   strSQL = strSQL & "SET([TRANS DATE],[ACCOUNT NO],[LAST NAME],[FIRST NAME],[CURRENT FC]," & _
   		"[TRANS FC],[FLAG],[PTYPE],[PAYMENT],[ITEM NO],[DESCRIPTION],[CURRENT BALANCE]) 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"

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
OB1CanobieAuthor Commented:
Rob,

this works great.  Thanks for your help. ; )
0
 
RobSampsonCommented:
Oh great! So you've already got code to connect to the database.....that's good....  :- )

Regards,

Rob.
0
 
OB1CanobieAuthor Commented:
Yes.  Thanks for your great help.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now