heydude
asked on
database import from csv file
I have a program that grabs information from a comma delimited file and imports it into an sqlite database. Problem I have now is that the same code that I had been using doesn't work correctly when the fields are blank. The last 2 columns of my csv file only has a few records with info so out of 350 records only 5 of the records may have data in them. the data file would look something like the following:
10014,Doe,John,1/4/1990,8, M,H,100,5/ 2/2005,1,F ,,1
43521,Doe,Jane,7/23/1991,8 ,M,H,150,3 /8/2005,1, F,H
How can I make sure that this file gets imported regardless of blank columns or even write n/a into the columns if they are blank? I just need to be ably to import this file in regardless if some fields are blank or not. Also, this file has no header row.
Below is the code I am currently using to do this.
Private Sub ImportFile(ByVal filename As String, ByVal SQLTable As String)
Dim fso As New System.IO.StreamReader(fil ename)
Dim line As String
Try
If Not fso Is Nothing Then
Do
line = fso.ReadLine()
DoSql(line, SQLTable)
Loop Until line Is Nothing
End If
Catch ex As Exception
Trace.WriteLine(ex.Message )
End Try
End Sub
Private Sub DoSql(ByVal fields As String, ByVal SQLTable As String)
Dim sql_command As String
Dim value_string As String
value_string = ""
For Each item As String In fields.Split(CChar(","))
value_string &= "'" & item & "'" & ","
Next
'remove the last comma
value_string = value_string.Substring(0, value_string.Length - 1)
sql_command = String.Format("INSERT INTO " & SQLTable & " VALUES(" & value_string & ")")
Trace.WriteLine(sql_comman d)
Try
'Execute the SQL command here
'
Catch ex As Exception
'trap syntax errors
Trace.WriteLine(ex.Message )
End Try
End Sub
Thank you
10014,Doe,John,1/4/1990,8,
43521,Doe,Jane,7/23/1991,8
How can I make sure that this file gets imported regardless of blank columns or even write n/a into the columns if they are blank? I just need to be ably to import this file in regardless if some fields are blank or not. Also, this file has no header row.
Below is the code I am currently using to do this.
Private Sub ImportFile(ByVal filename As String, ByVal SQLTable As String)
Dim fso As New System.IO.StreamReader(fil
Dim line As String
Try
If Not fso Is Nothing Then
Do
line = fso.ReadLine()
DoSql(line, SQLTable)
Loop Until line Is Nothing
End If
Catch ex As Exception
Trace.WriteLine(ex.Message
End Try
End Sub
Private Sub DoSql(ByVal fields As String, ByVal SQLTable As String)
Dim sql_command As String
Dim value_string As String
value_string = ""
For Each item As String In fields.Split(CChar(","))
value_string &= "'" & item & "'" & ","
Next
'remove the last comma
value_string = value_string.Substring(0, value_string.Length - 1)
sql_command = String.Format("INSERT INTO " & SQLTable & " VALUES(" & value_string & ")")
Trace.WriteLine(sql_comman
Try
'Execute the SQL command here
'
Catch ex As Exception
'trap syntax errors
Trace.WriteLine(ex.Message
End Try
End Sub
Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I actually fixed it, I'm not sure why, but I added a dummy column to my table and it worked. All records imported in no problem. Strange. I'm giving Sancler the points for his explanation and code example that I can use for other programs.
Bob