Solved

Parse CSV file into table using an array

Posted on 2008-06-09
6
829 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
listing all functions in JavaScript 19 114
Running Visio Macro from VBS File 3 36
control image tags in a string ? 12 68
Not needed 13 58
This article will show, step by step, how to integrate R code into a R Sweave document
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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 …

759 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

19 Experts available now in Live!

Get 1:1 Help Now