Upload CSV to SQL Server, changing data type for columns on upload


  • Dear All,

    I am trying to get CSV file into my SQL server. I found this which is quite handy, but get lost in the very begining. Can someone please kindly advice on how do I declare file (csv file) as Object, or am I getting it wrong.

    I have tried:

    Dim CsvDataReader As FileStream

    CsvDataReader = ("C : \Users\XXXXXXXX\Desktop\CSV_IMPORT_02.csv")

    but does not work right - at sits as error.

    Code example I would like to use is:

      ' declare CsvDataReader object which will act as a source for data for SqlBulkCopy
            Using csvData = New CsvDataReader(New StreamReader(fileUpload.PostedFile.InputStream, True))
                ' will read in first record as a header row and
                ' name columns based on the values in the header row
                csvData.Settings.HasHeaders = True

                ' must define data types to use while parsing data
                csvData.Columns.Add("varchar") ' First
                csvData.Columns.Add("varchar") ' Last
                csvData.Columns.Add("datetime") ' Date
                csvData.Columns.Add("money") ' Amount

                ' declare SqlBulkCopy object which will do the work of bringing in data from
                ' CsvDataReader object, connecting to SQL Server, and handling all mapping
                ' of source data to destination table.
                Using bulkCopy = New SqlBulkCopy("Data Source=.;Initial Catalog=Test;User ID=sa;Password=")
                    ' set the name of the destination table that data will be inserted into.
                    ' table must already exist.
                    bulkCopy.DestinationTableName = "Customer"

                    ' mappings required because we're skipping the customer_id column
                    ' and letting SQL Server handle auto incrementing of primary key.
                    ' mappings not required if order of columns is exactly the same
                    ' as destination table definition. here we use source column names that
                    ' are defined in header row in file.
                    bulkCopy.ColumnMappings.Add("First", "first_name") ' map First to first_name
                    bulkCopy.ColumnMappings.Add("Last", "last_name") ' map Last to last_name
                    bulkCopy.ColumnMappings.Add("Date", "first_sale") ' map Date to first_sale
                    bulkCopy.ColumnMappings.Add("Amount", "sale_amount") ' map Amount to sale_amount

                    ' call WriteToServer which starts import

                End Using ' dispose of SqlBulkCopy object

            End Using ' dispose of CsvDataReader object

    Thank you so much,


    Friday, March 10, 2017 5:38 PM

All replies