Import csv file into either access or sql server database using vb 2008.net

kshumway
kshumway used Ask the Experts™
on
I am creating a vb.net program which will import data from a .csv file into a table in a sql server database.  I am not sure how to do the import.  If I were writing this in Access I would use docmd.transfertext.  Your help is greatly appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
call OPENRAWSET query of SQL Server into VB.Net

query should like:

SELECT * FROM  
OPENROWSET  ('MSDASQL', 'Driver={Microsoft Text  Driver (*.txt; *.csv)};DBQ=D:\mmc;', 'SELECT * from  test.csv');

for more help:

http://sqlserver2000.databases.aspfaq.com/how-do-i-load-text-or-csv-file-data-into-sql-server.html

http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/5869d247-f0a0-4224-80b3-ff2e414be402


Senior .Net Consultant
Top Expert 2016
Commented:
I would highly recommend investigating SSIS as a solution.  This is the sort of thing that it is designed for and it will handle the import much more quickly with much less effort.
since you have specifically asked for calling procedure from vb.net, I suggested above given way but I too agree with above response that SSIS could be better candidate for the task you are looking for. I have few tutorials of SSIS on my blog, in case you are interested. http://www.sqlhub.com/

Author

Commented:
The database is currently SQL Server 2000.  Was SSIS available for that version?  I believe we are upgrading later this year to SQL Server 2008, but I can't wait until then.  I know nothing about SSIS, so forgive me my questions...
Since it is SS2000, you would need to use DTS . . . which is sort of like SSIS (only different ;-).  
It shouldn't be a hard package (which is what both DTS and SSIS "programs" are called) to set up.  I'll have to play around a bit to set up a CSV file and then review DTS to set up a sample package before I can provide the steps to do it.
The thing is that either DTS or SSIS are designed for this sort of thing and they operate much faster than writing an application that, essentially does a Row By Agonizing Row processing of the data to do the inserts. ;-)

Author

Commented:
Thank you.  I would like to do this as efficiently as possible.  I do have emoreau's code working (Thank you emoreau!) The csv file is not very large (less than 1000 records and there is only 10 fields in each record) so this is definitely doable.  

But I would be interested in learning more about SSIS and DTS because this just the first of several programs I will be writing.  Trying to migrate most of our Access program to VB.NET with SQL Server as the backend.  
Ron MalmsteadInformation Services Manager

Commented:
See vb.net example below.

Compile it to an ImportCSV.exe, then call it from command line...supplying the filename as an argument.
(ImportCSV.exe -f:C:\yourfile.csv)


Imports Microsoft.VisualBasic.FileIO
Imports System.Data.SqlClient
Module Module1

    Private SqlServerName As String = "yourservername"
    Private SqlDB As String = "yoursqldbname"
    Private SqlTable As String = "dbo.yoursqltablename"
    Private SqlUserName As String = "yourusername"
    Private SqlPass As String = "yourpass"
    Private FileNameArgSwitch As String = "-f:"
    Sub Main()

        Dim FileNameArg As String = My.Application.CommandLineArgs.ToString.Replace("-f:", "")

        Dim filename As String = FileNameArg
        Dim fields As String()
        Dim delimiter As String = ","
'Read the file
        Using parser As New TextFieldParser(filename)
            parser.SetDelimiters(delimiter)
            While Not parser.EndOfData
                ' Read in the fields for the current line
                fields = parser.ReadFields()


                SendToSQL(fields)

            End While
        End Using


    End Sub

    Public Sub SendToSQL(ByVal Fields As String())
        '* This all assumes that all the destination fields are Text, and that the input file has the same number of fields as the destination table

        'Convert all input fields into a single string
        Dim AllValues As String = "'"
        Dim i As Integer = 0
        For i = 0 To Fields.Length - 1

            AllValues = Fields.GetValue(i).ToString & "','"

        Next

        AllValues = AllValues & "'"


        'Create a connection to SQL
        Dim conn As New SqlConnection()
        conn.ConnectionString = "Data Source=" & SqlServerName & ";Initial Catalog=" & SqlDB & ";Persist Security Info=True;User ID=" & SqlUserName & ";Password=" & SqlPass

        'Prepare the INSERT statement
        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "INSERT INTO " & SqlTable & _
                            "VALUES (" & AllValues & ")"
        cmd.Connection = conn


        Try
            conn.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub
End Module

Open in new window

To make the DTS Package (in its simplest form):
  1. Open Enterprise Manager and extablish a connection to the _Destination_ SQL Server instance;
  2. Expand the instance's information by clicking on the + beside its name;
  3. Expand the Data Transformation Services information by clicking on the + beside Data Transformation Services under the instance;
  4. Right click on Local Packages and then select New Package;
  5. Click on the Text File (Source) icon (which looks like a document page with an arro in a circle coming out of it) in the Conenection area;
  6. Edit the Connection Properties as follows:
    1. Name the component by changing the "Text File (Source)" in the New Connection edit field to something more meaningful, e.g. "TF(S) your source data file name";
    2. Select the source data file in the entry field named "File Name";
       Click on the Properties button and go through the exercise of setting the appropriate properties;
    3. When you have set the properties, click the OK button on the Connection Properties dialog;
  7. Now, click on the Microsoft OLE DB Provider for SQL Server component in the Connection area (it looks like a little server box) and follow a similar procedure to set up the connection to the database;
  8. Now, click once on the Text File (Source) component that should be on your design area and then click on the Data Transformation Task (it looks like a little coge wheel with an arrow coming into it) in the Task area and then click on your Destination SQL Server connection;
  9. Double click on the arrow that should now exist going from your Text File (Source) Component to your Destination SQL Server DB component;
    1. In the Description field, give the arrow a description (you might also want to click on the Preview button to amke sure the data is set up correctly in the Text File (Source) component);
    2. Click on the Destination tab and make sure you have the correct table selected;
    3. Click on the Transformations tab and make sure that the Source data columns are mapped to the Destination data columns correctly;
  10. If you could import the CSV data directly into the table, you are now done.

  11. However, if there is a need to modify any of the source before it is moved to the database table, you need to create a staging table that matches the source data in the CSV file and then import the csv file into that staging table (and I would set up every column in the Staging table as a VarChar;
  12. Now, (and I know this seems a bit redundant), click on the Microsoft OLE DB Provider for SQL Server component in the Connection area again;
  13. Once again, put a Name in the New connection field, select the proper database instance and database (this will be the final Destination Database for the next Transformation);
  14. Now click once on that old Destination Database, hold the <Shift> key down and click once on the new Destination Database;
  15. Let up on the <Shift> key and then Right Click on the Destination Database and select the Transform Data Task option;
  16. You should now have another arrow from the old Destination Database to the New Destination Database;
  17. Double click on that arrow:
    1. On the Source tab, give the transformation a name and then click on the SQL Query radio button and create a SELECT query that accomplishes the column transformations that you need to accomplish;
    2. Now, on the Destination tabe, select the final target table;
    3. Next, click on the Transformations tab and make sure the columns are properly connected;
    4. Finally, click on OK;
  18. At this point, your package should look something the attached image and you should be ready to test the execution of the package by clicking on Package (on the menu line) and selecting Execute.
Some additional notes:
  • I would create this in a test database and execute it there before moving on to creating it in the production environment. ;-)
  • I would use a staging table even if the data looks right in the csv file.
     
Simple-DTS-Image.bmp
Ron MalmsteadInformation Services Manager

Commented:
Sorry.....correction.

For i = 0 To Fields.Length - 1

            AllValues = AllValues & Fields.GetValue(i).ToString & "','"

        Next

Author

Commented:
Thank you -  I actually used emoreau's code for this and tested 8080 Driver for future needs.  Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial