VB.NET - Importing csv file into Sql Server using vb net

Asked By ninel gorbunov on 08-Aug-06 11:33 AM
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
[/code]

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

OpenRowSet - Asked By F Cali on 08-Aug-06 11:51 AM

You will need to use OpenRowSet for this.  Your query will look like this:

[CODE]
SELECT F1
INTO List_Staging
FROM
     OPENROWSET('MSDASQL',
        'Driver={Microsoft Text Driver (*.txt; *.csv)};
          DEFAULTDIR=D:\;Extensions=CSV;',
        'SELECT * FROM CSVFile.csv')
[/CODE]

For something similar to what you're doing, here's some links that may be able to help:

http://www.sql-server-helper.com/tips/read-import-excel-file-p01.aspx
http://www.sql-server-helper.com/tips/read-import-excel-file-p02.aspx
http://www.sql-server-helper.com/tips/read-import-excel-file-p03.aspx

What is DEFAULTDIR - Asked By ninel gorbunov on 08-Aug-06 11:54 AM

Do I change the D:\; to where my csv file is located?

It worked somewhat - Asked By ninel gorbunov on 08-Aug-06 12:13 PM

Thank you...The data got imported into the table, but not properly.

The csv file contains one row it does not have any headings. So what happened was the first   record was created as a field name in the database. Is there any way to create a field name in the table I am importing into and keep the first record of the csv file as the first record in the table and not as a field name?

Thanks,
Ninel
Use Excel OleDbProvider - Asked By F Cali on 08-Aug-06 12:35 PM
What I would suggest is that instead of treating the file as a text file, treat it as an Excel.  The following links should be able to help you:

http://www.sql-server-helper.com/tips/read-import-excel-file-p01.aspx
http://www.sql-server-helper.com/tips/read-import-excel-file-p02.aspx
http://www.sql-server-helper.com/tips/read-import-excel-file-p03.aspx

It can take a CSV file instead of just an Excel file.  Also, there's an option there to tell OPENROWSET that there is no header in the file.
DefaultDir - Asked By F Cali on 08-Aug-06 12:35 PM
Yes, the DefaultDir property is where your file is located because you cannot specify that in the SELECT statement.
doesn't work - Asked By ninel gorbunov on 08-Aug-06 01:58 PM
I tried using the following:

Select * 
INTO Voicenet_JH2468.dbo.[List_staging] 
FROM  OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)}; DEFAULTDIR=C:\VoicenetSQL\project\tampa\Politic\JH2468; Extensions=CSV; HDR=No;','SELECT * FROM at1008.csv') "

This imports into sql, but treats the first row as heading. The HDR=No is not working.

WHen I tried:
SELECT * 
INTO Voicenet_JH2468.dbo.[List_staging] 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\VoicenetSQL\project\tampa\Politic\JH2468\at1008.csv',
                'SELECT * FROM [at1008$]')

I got the following error : "OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error."

I just need to get the HDR=No to work.
BCP in - Asked By mv ark on 09-Aug-06 04:49 AM
Consider using the BCP "in" option to import CSV file to Sql Server. BCP is a command line utility. You can use it in a stored procedure and  with the help of xp_cmdshell. 
Check these related links for sample code -
http://www32.brinkster.com/srisamp/sqlArticles/article_4.htm
http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html

Call the stored procedure in your VB.NET code. 
BCP is one of the fastest ways of transferring data.
Another Option - Asked By F Cali on 09-Aug-06 10:51 AM
If that didn't work, the other option that I can think of is to use StreamReader to read your text file one line at a time and for each line, do an INSERT in your destination table.  This may not be as fast as the first option but you will have more control with this option.
am i doing something stupid? - Asked By ninel gorbunov on 09-Aug-06 12:20 PM
I created a table called "list_staging"

I am now trying to do the bulk insert:
[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=" & DATABASE & ";user id=voicenetuser;password=voicenetuser;"

        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 = "BULK INSERT " & DATABASE & ".dbo.[list_staging] FROM 'C:\VoicenetSQL\project\tampa\Politic\" & projectfile & "\at1008.csv' " & _
                        "WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )"


                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
[/code]

The table list_staging is being dropped and I'm getting an error message "Invalid object name 'VoiceNet_JH2468.dbo.list_staging'."

Any ideas?
Verify Table in Database - Asked By F Cali on 10-Aug-06 09:46 AM
Looking at your code, there's nothing wrong with it and there's no reason why your table will be dropped.  The BULK INSERT doesn't have an option to drop a table.  Check your database again to make sure that you created the list_staging table in the correct database, which is VoiceNet_JH2468 and make sure that the owner is dbo.
Here's another question - Asked By ninel gorbunov on 10-Aug-06 10:51 AM
Ok...I finally figure out what the issue is...

Let me give some background of what I need to do.

My company acquires projects all the time. For each project they want to create it's own database. So what I did was create a template of a project database and through a user interface allow the user to select a particular file. Based on this file I know what to name the database. I backup and restore the template database with the new project name.
Then I need to import this .csv file into a particular table.

My problem was that I created the list_staging table in the new project database and not in the template so when I backed up and restored it got wiped out.

Now on to my next problem...I use a user login to connect to the database when creating these new databases. That user doesn't see the new database. 

How can I add that database to the user account within VB.Net? Is this even possible?

Thanks for all your help,
Ninel
sp_grantdbaccess - Asked By F Cali on 10-Aug-06 10:58 AM
After restoring the database, you can give a user account an access to the new database using the sp_grantdbaccess system stored procedure but this sp can only be executed by a member of the sysadmin fixed server role or by members of db_accessadmin or db_owner fixed database roles.
Thanks - Asked By ninel gorbunov on 10-Aug-06 11:39 AM
Thank you so much for all of your help. I'll look into it.
s replied to ninel gorbunov on 25-Aug-10 10:30 PM

You can try something like this which I got to work for the csv files I need to upload to db table.

localDir = "c:\Temp\"

'create sql connection

dwConn.ConnectionString = ("server=.;uid=xxx;pwd=xxx;database=xxx")

'create csv connection string

csvConn.ConnectionString = _

("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & localDir & ";" & _

"Extended Properties=""text;HDR=YES;FMT=CSVDelimited""")

'open csv connection

csvConn.Open()

'create csv command

Dim csvCmd As New System.Data.OleDb.OleDbCommand

'assign  sql to  command text

csvCmd.CommandText = ("Select [yourColumn] From yourCsvFile#csv"  'csv file being in localDir

'assign the cmd to a connection

csvCmd.Connection = csvConn

'create csv reader

Dim csvRdr As System.Data.OleDb.OleDbDataReader

'insert the csv contents into a reader

csvRdr = csvCmd.ExecuteReader()

'open sql connection

dwConn.Open()

'create sqlbulk copy to insert the csv reader into db table

Dim sqlBulk As New SqlBulkCopy(dwConn)

sqlBulk.DestinationTableName = "[dbo].[yourDBtable]"

sqlBulk.WriteToServer(csvRdr)

'close csv connection

csvConn.Close()

'close sql connection

dwConn.Close()