hi!
I am trying to import a text file into a table created on the fly in
SQL Server 2005 since the number of columns can vary depending on what
the user has checked or not.
Here is the code I am using:
Dim myConnectionStr ing As String = "Data Source=dr-ny-sql003;Initial
Catalog= TEST_TAMMY;Inte grated Security=YES"
Dim myConnection As SqlConnection = New
SqlConnection(m yConnectionStri ng)
'Dim csvInputFile As String = "R:\Spam BB Search Engine
Application\Exp orts\GridExport .txt"
Dim csvInputFile As String = "C:\GridExport. txt"
Dim ds As New DataSet
'Dim m_strConnection As String = "Data Source=" &
lstServers.Sele ctedValue & ";Initial Catalog= SpamBank;Integr ated
Security=SSPI"
myConnection = New SqlConnection
myConnection.Co nnectionString = myConnectionStr ing
myConnection.Op en()
' Make sure the .CSV file exists:
If IO.File.Exists( csvInputFile) Then
Try
' ------ Load the data from the .CSV file: --------
Dim strSQL As String
'strSQL = "SELECT * INTO Spambank.dbo.Te st_Tammy FROM
[Text;HDR=YES;DA TABASE=" & csvInputFile & "]"
'strSQL = "SELECT * INTO [dr-ny-
sql003].TEST_TAMMY.dbo .Test_Tammy FROM
OPENROWSET('msd asql,Driver={Mi crosoft Text Driver
(*.txt;*.csv)}; DEFAULTDIR=R:\S pam BB Search Engine Application\Exp orts
\;Extensions=tx t;','select * from GridExport.txt' "
strSQL = "SELECT * INTO [dr-ny-
sql003].TEST_TAMMY.dbo .temp_test_spam FROM
OPENROWSET('msd asql,Driver={Mi crosoft Text Driver
(*.txt;*.csv)}; DEFAULTDIR=C:\; Extensions=txt; ','select * from
GridExport.txt' "
Dim objCommand As SqlClient.SqlCo mmand
objCommand = New SqlClient.SqlCo mmand(strSQL,
myConnection)
objCommand.Comm andText = strSQL
objCommand.Exec uteNonQuery()
myConnection.Cl ose()
Catch ex As Exception
MessageBox.Show ("Error importing the file" &
csvInputFile & ".")
End Try
End If
I am not getting the error message, but neither is the table being
created in the database TEST_TAMMY.
What am I doing wrong?
Thanks in advanced!
Tammy
I am trying to import a text file into a table created on the fly in
SQL Server 2005 since the number of columns can vary depending on what
the user has checked or not.
Here is the code I am using:
Dim myConnectionStr ing As String = "Data Source=dr-ny-sql003;Initial
Catalog= TEST_TAMMY;Inte grated Security=YES"
Dim myConnection As SqlConnection = New
SqlConnection(m yConnectionStri ng)
'Dim csvInputFile As String = "R:\Spam BB Search Engine
Application\Exp orts\GridExport .txt"
Dim csvInputFile As String = "C:\GridExport. txt"
Dim ds As New DataSet
'Dim m_strConnection As String = "Data Source=" &
lstServers.Sele ctedValue & ";Initial Catalog= SpamBank;Integr ated
Security=SSPI"
myConnection = New SqlConnection
myConnection.Co nnectionString = myConnectionStr ing
myConnection.Op en()
' Make sure the .CSV file exists:
If IO.File.Exists( csvInputFile) Then
Try
' ------ Load the data from the .CSV file: --------
Dim strSQL As String
'strSQL = "SELECT * INTO Spambank.dbo.Te st_Tammy FROM
[Text;HDR=YES;DA TABASE=" & csvInputFile & "]"
'strSQL = "SELECT * INTO [dr-ny-
sql003].TEST_TAMMY.dbo .Test_Tammy FROM
OPENROWSET('msd asql,Driver={Mi crosoft Text Driver
(*.txt;*.csv)}; DEFAULTDIR=R:\S pam BB Search Engine Application\Exp orts
\;Extensions=tx t;','select * from GridExport.txt' "
strSQL = "SELECT * INTO [dr-ny-
sql003].TEST_TAMMY.dbo .temp_test_spam FROM
OPENROWSET('msd asql,Driver={Mi crosoft Text Driver
(*.txt;*.csv)}; DEFAULTDIR=C:\; Extensions=txt; ','select * from
GridExport.txt' "
Dim objCommand As SqlClient.SqlCo mmand
objCommand = New SqlClient.SqlCo mmand(strSQL,
myConnection)
objCommand.Comm andText = strSQL
objCommand.Exec uteNonQuery()
myConnection.Cl ose()
Catch ex As Exception
MessageBox.Show ("Error importing the file" &
csvInputFile & ".")
End Try
End If
I am not getting the error message, but neither is the table being
created in the database TEST_TAMMY.
What am I doing wrong?
Thanks in advanced!
Tammy