Importing csv file to SQL Server Using VB.Net

[ninelg]

I have a csv file that needs to be imported into sql server using vb.net.

I got it working with the following code with one exception:

Private Function ImportLeadFile(ByVal projectfile As String, ByVal sLeadFile As String, ByVal DATABASE As String) As Boolean
        Dim objConn As nsSqlClient.SqlConnection
        Dim ds As New DataSet
        Dim m_strConnection As String = "server=NINEL-D246655F1;Initial Catalog=TimeControl;user id=timeuser;password=timeuser;"

        objConn = New nsSqlClient.SqlConnection
        objConn.ConnectionString = m_strConnection
        objConn.Open()

        ' Make sure the .CSV file exists:
        If File.Exists(sLeadFile) Then
            Try
                ' ------ Load the data from the .CSV file: ----------
                Dim strSQL As String
                strSQL = "Select * " & _
                       " INTO " & DATABASE & ".dbo.[List_staging] " & _
                      "FROM  OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)}; DEFAULTDIR=C:\VoicenetSQL\project\tampa\Politic\" & projectfile & "; Extensions=CSV; HDR=No;','SELECT * FROM at1008.csv') "

                 Dim objCommand As nsSqlClient.SqlCommand
                objCommand = New nsSqlClient.SqlCommand(strSQL, objConn)

                objCommand.CommandText = strSQL
                objCommand.ExecuteNonQuery()
                objConn.Close()
            Catch ex As Exception
                sResultText = sResultText & "<BR>" & ex.Message
            End Try
        End If
End Function

The csv file contains one column of phone numbers with no heading. When the file gets imported into a table the first phone number record is created as a column name. How can I get around this?

[Lord Soth]

Hi,

I think you would need to write your own CSV parser using String.Split and not use OLEDB Text Driver (eventhough there might be a parameter for the connection string to fix this, check text driver's reference).

Loren Soth

[ninelg]

How do I do that?

I'd really appreciate any help.

[jjthebig1]

Ninleg,

Here is a sample:

I have a Categories.txt file in C:\ that has the following contents:

"Sushi", "All kinds of sushi rolls, including crab tempura roll"
"Wine", "All kinds of wine, including some world famous Australian Shiraz"

To import it into Northwind, create the DataSet and TableAdapters.

Use the following code to import. See comments in code for explanation.

'Opening the file
Using file As New IO.FileStream("C:\Categories.txt", IO.FileMode.Open, IO.FileAccess.Read)
'Creating a strem to read the file into text
Using stream As New IO.StreamReader(file)
'Read the entire file, split it into line arrays by CRLF, and loop through it.
For Each sLine As String In stream.ReadToEnd().Split(Environment.NewLine)
'You to use ", as the delimiter to ensure that commas inside a record are ignored.
Dim sDelimiters() As String = {""","}
'Now split the line into individual records.
Dim sRecords() As String = sLine.Split(sDelimiters, StringSplitOptions.None)
'Insert them into the database by referencing the individual string in the array,
'but be sure to replace the quotes with an emtpy string; otherwise, you will have quote in the record.
'The third argument, to which I'm passing a 'Nothing', is expecting a picture.
CategoriesTableAdapter.Insert(sRecords(0).Replace("""", ""), sRecords(1).Replace("""", ""), Nothing)
Next
End Using
End Using

- Jacob Silberstein

[t_karthik]

When using Text Driver method, "Schema.ini" will be created in the application path.
In the file, specify the "ColNameHeader=False". Then it won't consider the first row as header.