Importing csv file to SQL Server Using VB.Net

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Importing csv file to SQL Server Using VB.Net

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2006-08-08 : 11:32:23

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:


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

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-08-08 : 12:52:51

I've never tried to do it that way before. You may just want to do it "the easy way" and use a StreamReader to read the file line by line and call an INSERT statement for each line. The CSV file is just a text file right, or is it an Excel CSV?

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights.

ninel
Posting Yak Master

141 Posts

Posted - 2006-08-08 : 14:00:10

The csv file opens up in excel. I have one column with no heading.

How can I do what you suggested with the StreamReader?

ninel
Posting Yak Master

141 Posts

Posted - 2006-08-08 : 14:52:41

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?

ninel
Posting Yak Master

141 Posts

Posted - 2006-08-23 : 12:42:22

How about doing a bulk insert on a text file that has the following data:
"7187893456"
"8139987654"

Will this work?


BULK INSERT dbo.list_staging FROM '" & sLeadFile & "' " & _
"WITH ( FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n' )"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources