Text file to SQL server hellp puleaase!

[RyanSmith]

Dear VB Elite,

I have this CSV file saved as juvrec.txt from a database from this old DOS program DataEase...[hell]... I need to figure out a way to get a VB program to read the CSV and export it to a database in SQL Server. Any help/starter suggestions would be excellent because I am clueless as where to begin.

EX:
The first line of the text file is column names for the table. The second and so on are the actual fields.

The second line is "00001","ABBOTT","GEORGE","RYAN","",,W,M,9/9/1979,"","MI","1290 PARK RD"

How would I get the program to take the strings and put it in a desired column in a table in SQL? I hope this post makes sense because I'm clueless and lost. Thank you for even reading this.

[Kunchesm]

open ur text file for Input ...using instr function seperate the fields(since each field is sepetaed with a 'Comma'..).. insert the data u got from the above method into ur required table using DAO/ ADOs...

[vb_dba]

You could use DTS to handle this. It allows you to import a text file into a table. This would be the quickest way.

But...

If you need to do it through VB, read in each line of the file, use the Replace function to remove the double quotes, use the split function to populate an array, then execute an Insert statement to upload the data to your table.

Dim strLine As String
Dim strNew As String
Dim arrFile()
Dim strSQL As String
 
Open "C:\FileName.csv" For Input As #1
'Read the first line to get rid of the column headers
Line Input #1, strLine
 
Do Until EOF(1)
     'Read the next line in the file
     Line Input #1, strLine
     'Replace the double quotes with nothing
     strNew = Replace(strLine, """, "")
     'Put the string in an array
     arrFile = Split(strNew, ",")
     'Build the Insert statement
     strSQL = "Insert Into myTable(Field1, Field2, Field3) Values ("
     strSQL = strSQL & "'" & arrFile(0) & "', " & arrFile(1) & ")"
     'Execute the insert statement with the Connection Object
     cn.Execute strSQL
Loop
 
Close #1

The syntax for the Replace and Split functions may be a little off, but they should be close. If the column is a character field, be sure to surround it in single quotes, like arrFile(0) above.

[RyanSmith]

Thank you both for those fast replies! I owe you!!!

Thank you and have a nice evening
!!!