CSV file import

Question

Post reply

CSV file import

jwaz1999

Newbie

Points: 3
More actions
September 10, 2010 at 10:47 am

#240117

Is there a way that a CSV file can be imported into a SQL Server table using BCP if the file has the below 5 column format, or do you have to iterate through it one record at a time?
sample data:
5, "test, column", "test column 2", 4, "test column, 5"
6, test column", "test column 2,", 5, "test column 5"
7, test column", "test column 2", 6, "test, column 5"
If it can be done with BCP, how do you define the format for columns with quotes(char data) and columns without quotes(int data)?
Thanks,
Jeff

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

Jeff,

Is all of your character data going to start and end with a double-quote? (Your sample isn't doing this...)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95461 More actions · Answer 2

Assuming that in the second two lines, that column 2 is supposed to start with a double-quote, then this works for me:

if object_id('tempdb..#Test2') IS NOT NULL DROP TABLE #Test2;

CREATE TABLE #Test2 (

Col1 int,

Col2 varchar(15),

Col3 varchar(15),

Col4 int,

Col5 varchar(15));

BULK INSERT #Test2 FROM 'C:\SQL\BCP.txt' WITH (FORMATFILE = 'C:\SQL\BCP.fmt');

select * from #Test2;

The format file is this:

9.0
5
1 SQLCHAR 0 12 ", \"" 1 Col1 ""
2 SQLCHAR 0 15 "\", \"" 2 Col2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 15 "\", " 3 Col3 SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 12 ", \"" 4 Col4 ""
5 SQLCHAR 0 15 "\"" 5 Col5 SQL_Latin1_General_CP1_CI_AS

The data file is saved as C:\SQL\BCP.txt

The format file is saved as C:\SQL\BCP.fmt

The results are:

Col1 Col2 Col3 Col4 Col5

----------- --------------- --------------- ----------- ---------------

5 test, column test column 2 4 test column, 5

6 test column test column 2, 5 test column 5

7 test column test column 2 6 test, column 5

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2