Importing csv file to SQL Server Using VB.Net-VBForums
Results 1 to 5 of 5

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

  1. #1

    Thread Starter
    Member
    Join Date
    Mar 2005
    Posts
    40

    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

  2. #2
    Banned
    Join Date
    Nov 2005
    Posts
    2,367

    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.

  3. #3

    Thread Starter
    Member
    Join Date
    Mar 2005
    Posts
    40

    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?

  4. #4

    Thread Starter
    Member
    Join Date
    Mar 2005
    Posts
    40

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

    Can anyone help me with this?

  5. #5

    Thread Starter
    Member
    Join Date
    Mar 2005
    Posts
    40

    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
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.