• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 850
  • 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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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