MSSQL Bulk Insert CSV - Multiple columns include commas

Question

I'm running MSSQL Server Management Studio R2 and I have a large .csv file with ~30 columns and ~10,000,000 rows. I downloaded the data from an internet source in about 900 separate files and concatenated them to a single file with a .cmd script, so now I have a full file ready for insertion into the database.

Unfortunately, large numbers in multiple columns are recorded as strings that contain internal commas (i.e., "50,000,000" or "3,972,721"). Note that the double quotes are included in the file formatting. BULK INSERT considers these commas to be text field delimiters rather than part of the string.

This is my insert statement.

BULK INSERT Database.dbo.table
    FROM '\\server\filepath\file.csv'
    WITH
        (
            FIELDTERMINATOR = ',',
            ROWTERMINATOR = '\n'
        );

In the table I created to hold the data, the rows in question have the varchar(max) data type, as anything else I've tried throws an error.

The incorrect output for "3,972,721" currently bleeds over into extra rows, completely distorting the rest of the table.

ROW1        ROW2        ROW3
"3          972         721"

I would like the correct output for "3,972,721" to be

ROW1
3972721

I'm looking either for a way to force the BULK INSERT command to cast these strings as ints (with the added constraint that some of the datapoint strings include a + symbol, for example "2,500,000,000+"), or another method of insertion entirely, such as bcp.

Thank you!

I know with SSIS you can specify that the double-quotations are field delimiters. — Tab Alleman
– Tab Alleman, Commented Jun 9, 2015 at 14:24
I could not find any way to overcome this using bulk insert, but SSIS allows you to specify a text qualifier. read this — Zohar Peled
– Zohar Peled, Commented Jun 9, 2015 at 16:39

Community · Accepted Answer · 2017-05-23 12:22:21Z

0

You can specify a text qualifier with bulk inserts by using a format file. This related StackOverflow question has pretty good documentation on how to do this:

Bulk insert with text qualifier in SQL Server

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered Jun 9, 2015 at 18:52

Patrick Tucci

1,9521 gold badge18 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MSSQL Bulk Insert CSV - Multiple columns include commas

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related