-
Aug 8th, 2006, 10:30 AM
#1
Thread Starter
Member
Importing csv file to SQL Server Using VB.Net
I have a csv file that is selected by a user. The csv file contains one column (phone number). I need to import this file into a table on sql server using vb.net.
I've tried the following code:
Code:
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 F1 " & _
"INTO " & projectfile & ".dbo.[List_staging] " & _
"FROM [Text;HDR=NO;DATABASE=" & sLeadFile & "]"
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
I'm getting an error: "Invalid object name 'C:\VoicenetSQL\project\tampa\Politic\JH2468\at1008.CSV'."
This is the file the user selected.
What am I doing wrong?
Any help would be greatly appreciated,
Ninel
-
Aug 8th, 2006, 10:39 AM
#2
Re: Importing csv file to SQL Server Using VB.Net
Is that the path on the client or the server?
Keep in mind, when you tell the server to import something fom a location; it checks itself for that path. It doesn't assume you mean C:\ on the client.
-
Aug 8th, 2006, 01:50 PM
#3
Thread Starter
Member
Re: Importing csv file to SQL Server Using VB.Net
I am doing this on my local machine.
I got it working with the following code with one exception:
Code:
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?
-
Aug 9th, 2006, 10:29 AM
#4
Thread Starter
Member
Re: Importing csv file to SQL Server Using VB.Net
Can anyone help me with this?
-
Aug 23rd, 2006, 11:40 AM
#5
Thread Starter
Member
Re: Importing csv file to SQL Server Using VB.Net
How about doing a bulk insert on a text file that has the following datad:
"7187893456"
"8139987654"
Will this work?
Code:
BULK INSERT dbo.list_staging FROM '" & sLeadFile & "' " & _
"WITH ( FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n' )"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|