VB.NET

Importing csv file to SQL Server Using VB.Net

Please support our VB.NET advertiser: Intel Parallel Studio Home

•

Join Date: Aug 2005

Posts: 6

Reputation: ninelg is an unknown quantity at this point

Solved Threads: 0

ninelg

Offline

Newbie Poster

Importing csv file to SQL Server Using VB.Net

Aug 8th, 2006

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:

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
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
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?

•

Join Date: Mar 2006

Posts: 233

Reputation: Lord Soth is an unknown quantity at this point

Solved Threads: 4

Lord Soth

Offline

Posting Whiz in Training

Re: Importing csv file to SQL Server Using VB.Net

Aug 9th, 2006

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

Best regards,
Loren Soth

Crimson K. Software _________________________________________________________________ Crimson K. Blog

•

Join Date: Aug 2005

Posts: 6

Reputation: ninelg is an unknown quantity at this point

Solved Threads: 0

ninelg

Offline

Newbie Poster

Re: Importing csv file to SQL Server Using VB.Net

Aug 9th, 2006

How do I do that?

I'd really appreciate any help.

•

Join Date: Feb 2009

Posts: 2

Reputation: jjthebig1 is an unknown quantity at this point

Solved Threads: 1

jjthebig1

Offline

Newbie Poster

Re: Importing csv file to SQL Server Using VB.Net

Feb 25th, 2009

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

Last edited by jjthebig1; Feb 25th, 2009 at 11:24 pm.