Import CSV data into SQL Server

bulkinsert sqlbulkcopy sql-server sql-server-2008 sql-server-2008-r2

Question

I have data in the csv file similar to this:

Name,Age,Location,Score
"Bob, B",34,Boston,0
"Mike, M",76,Miami,678
"Rachel, R",17,Richmond,"1,234"

While trying to BULK INSERT this data into a SQL Server table, I encountered two problems.

  1. If I use FIELDTERMINATOR=',' then it splits the first (and sometimes the last) column
  2. The last column is an integer column but it has quotes and comma thousand separator whenever the number is greater than 1000

Is there a way to import this data (using XML Format File or whatever) without manually parsing the csv file first?

I appreciate any help. Thanks.

1
0
3/21/2014 5:30:09 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Popular Answer

Use MySQL load data: 

LOAD DATA LOCAL INFILE 'path-to-/filename.csv' INTO TABLE `sql_tablename` 
CHARACTER SET 'utf8' 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
IGNORE 1 LINES;

The part optionally enclosed by '\"', or escape character and quote, will keep the data in the first column together for the first field.

IGNORE 1 LINES will leave the field name row out.

UTF8 line is optional but good to use if names have diacritics, like in José.

0
6/19/2015 4:59:08 AM

Related Questions

With the SqlBulkCopy class, you may import data from SQLite to SQL Server.

I'm trying to transfer the data from SQLite to SQL Server. The schema of target and destination table are just the same:...SQL Server:...CREATE TABLE [dbo].[Shop] ( [ShopID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](128) NOT NULL, [Url] [nvarchar](128) NOT NULL, PRIMARY KEY CLUSTERED ( [ShopID] ASC )) ...and SQLite:...CREATE ...
sqlbulkcopy sqlite
asked by Jeffrey Zhao

import text file(500 MB) data into SQL Server

Here is my code that I am using for importing text files larger than 500 MB into database....I want to do it in batches so that if any format related error occur in text file during execution at least half of it contents uploaded....If any other suggestion for uploading such a large text file please specify....private DataTable CreateDataTableFromF...
asp.net c# sqlbulkcopy sql-server
asked by user1487506

SQL Server import faster than bulk import

I have a large (~50Gb, ~300 mln rows) tab separated file, which I want to import into a SQL Server table with columns: ...char(10), varchar(512), nvarchar(512), nvarchar(512).......It takes about a day to bulk import it using T-SQL, SSIS or C# ...SqlBulkCopy... class....Is there any faster way to load this data? ...Or could there be some condition ...
bulkinsert sqlbulkcopy sql-server
asked by Egor Antonov

Upload bulk data into SQL Server 2008 from .csv

I want to let my users to upload 3000-4000 rows of data into SQL Server 2008. I would like to use ...SqlBulkCopy.... What is the best policy to do it?...Option A:...First the ....csv... file will be uploaded to the server and then ...SqlBulkCopy... operation will be started....Option B:...Read ....csv... file from user's pc and then send that data ...
asp.net c# sqlbulkcopy
asked by s.k.paul

SqlBulkCopy ColumnMapping error CSV to DataTable to SQL Server Table

I am trying to bulk insert a CSV file into a SQL Server database....The process is ....CSV... file to ...DataTable... to ...SqlBulkCopy... to SQL Server....When I run this I get this error: ...The given ColumnMapping does not match up with any column in the source or destination...When I directly import the CSV into SQL Server via Management Studio...
c# csv-import datatable sqlbulkcopy sql-server
asked by MicroMan

Best method for importing data from text file into MS SQL Server

In need of your opinions. Currently developing an application in VB.NET. ...I have a text file which contains more than one thousand rows. Each rows contains the data needed to be inserted into the database. A sample of a row is as follows:...0001--------SCOMNET--------0.100---0.105 ...At first glance, one might figured that each column was separat...
performance sqlbulkcopy sql-server vb.net
asked by Khairul

Fast and simple way to import csv to SQL Server

We are importing a csv file with ...CSVReader... then using ...SqlBulkCopy... to insert that data into SQL Server. This code works for us and is very simple, but wondering if there is a faster method (some of our files have 100000 rows) that would also not get too complex?... SqlConnection conn = new SqlConnection(connectionString); ...
.net c# sqlbulkcopy sql-server
asked by Austin Harris

C# - Importing a CSV where empty strings do not get sent through bulk import to database

So I have a database of site location information which contains 27 columns. I have coded a bulk import feature that takes a CSV file and lets you modify information. Everything works well when the CSV file contains information in every column, but when there is an empty string/null it will push an empty string to the database and wipe out what was...
.net c# csv mysql sqlbulkcopy
asked by ouro

Importing Data in Parallel in SQL Server

I have more than 100 files to import into the sql server and most of them are of 500 MB. I want to leverage the the parallel import utility of SQL server and have read a number of webpages, like the following ones:...How to load 1 TB data in 30 minutes...https://technet.microsoft.com/en-us/library/dd537533(v=sql.100).aspx...Importing Data in Parall...
bulkinsert bulk-load parallel-processing sqlbulkcopy sql-server
asked by Jason

Load CSV from Blob to Azure SQL Server - Referenced external data source not found

I'm trying to bulk insert a CSV hosted in blob storage into Azure SQL Server, as is described in ...this MSDN post.......My code is taken almost entirely from ...this Microsoft Github sample.... ...When running it I receive the following error:...Referenced external data source "MyAzureBlobStorage" not found....-- Create Database Master Key IF (sel...
azure azure-sql-database csv sqlbulkcopy sql-server
asked by Seafish

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow