Home > Code Samples > Upload CSV file to SQL Server in VB

Upload CSV file to SQL Server using
CsvDataReader, and SqlBulkCopy in ASP.Net



File Format
First,Last,Date,Amount
John,Smith,02/05/2005,$52.15
Jane,Doe,01/23/2006,$71.95
...
Create Table Script
CREATE TABLE [dbo].[Customer] (
	[customer_id] [int] IDENTITY (1, 1) NOT NULL ,
	[first_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[last_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[first_sale] [datetime] NOT NULL ,
	[sale_amount] [money] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD 
	CONSTRAINT [PK_Customer] PRIMARY KEY  CLUSTERED 
	(
		[customer_id]
	)  ON [PRIMARY] 
GO
VB.Net
Protected Sub uploadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles uploadButton.Click

    ' declare CsvDataReader object which will act as a source for data for SqlBulkCopy
    Using csvData = New CsvDataReader(fileUpload.PostedFile.InputStream, Encoding.Default)
        ' 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
            bulkCopy.WriteToServer(csvData)

        End Using ' dispose of SqlBulkCopy object

    End Using ' dispose of CsvDataReader object

End Sub ' end uploadButton_Click