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(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
bulkCopy.WriteToServer(csvData)
End Using ' dispose of SqlBulkCopy object
End Using ' dispose of CsvDataReader object
End Sub ' end uploadButton_Click
|