Solved

Parse CSV file into table using an array

Posted on 2008-06-09
6
839 Views
Last Modified: 2013-11-25
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
Comment
Question by:OB1Canobie
  • 3
  • 3
6 Comments
 
LVL 65

Expert Comment

by:RobSampson
ID: 21746849
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
 

Author Comment

by:OB1Canobie
ID: 21754331
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
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 21755329
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:OB1Canobie
ID: 21763274
Rob,

this works great.  Thanks for your help. ; )
0
 
LVL 65

Expert Comment

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

Regards,

Rob.
0
 

Author Comment

by:OB1Canobie
ID: 21766203
Yes.  Thanks for your great help.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question