Link to home
Start Free TrialLog in
Avatar of heydude
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(filename)
        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_command)

        Try
            'Execute the SQL command here
            '
        Catch ex As Exception
            'trap syntax errors
            Trace.WriteLine(ex.Message)
        End Try
    End Sub

Thank you
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

What happens when the field is blank?  What is missing?

Bob
ASKER CERTIFIED SOLUTION
Avatar of Sancler
Sancler

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of heydude
heydude

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.