Solved

Parse CSV file into table using an array

Posted on 2008-06-09
6
837 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

930 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now