Solved

Parse CSV file into table using an array

Posted on 2008-06-09
6
841 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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 is about my first experience with programming Arduino.
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…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

707 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