Importing csv file to SQL Server Using VB.Net

[ninel]

I am doing this on my local machine.
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?

 

[outcast1881]

HI,
This code works,you can also try....

Can


PrivateSub Button3_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim conn1 = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\CSVFiles;Extended Properties=Text;")
Dim sqlconnection As SqlConnection = New SqlConnection("data source=.\SQLEXPRESS;database=RFID;User ID=catalay;Password=")
Dim cmd1 = New OleDbCommand("select * from can7.txt;", conn1)
Dim sqlcmd1 = New SqlCommand("insert into TEST(Bir) values (@col1)", sqlconnection)
Dim OLEadapter AsNew OleDbDataAdapter
Dim OLEds AsNew DataSet
Dim OLEdt AsNew DataTable
Dim dr As DataRow
Dim cmd = New OleDbCommand("CREATE TABLE can7.txt(COL1 integer ,COL2 date);", conn1)
sqlconnection.Open()
conn1.open()
cmd.executenonquery()


OLEadapter.SelectCommand = cmd1
OLEadapter.Fill(OLEds, "mytable1")
OLEdt = OLEds.Tables("mytable1")
Dim prm1 As SqlParameter = sqlcmd1.Parameters.Add("@col1", SqlDbType.Int)
ForEach dr In OLEds.Tables(0).Rows
sqlcmd1.Parameters("@Col1").value = dr(0)
sqlcmd1.executenonquery()
Next
MessageBox.Show("ok")
sqlconnection.Close()
conn1.close()







EndSub

 

[cjard]

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?

Use NOTEPAD.EXE to edit the file so that the first line says "PhoneNumber" ?