MS SQL Server 2019 OpenRowSet Bulk Insert with CSV file

I am attempting to load a CSV file onto MS SQL Server with OPENROWSET BULK INSERT and 0 rows are being inserted.

I am using the following code within SQL Server Management Studio;


-- DROP TABLE finances.dbo.MasterCard_Import_CSV;

CREATE TABLE finances.dbo.MasterCard_Import_CSV (
    Date datetime
    , Type varchar(10)
    , Description varchar(100)
    , Value float
    , Balance float
    , Name varchar(20)
    , Account varchar(50));

INSERT INTO finances.dbo.MasterCard_Import_CSV
    SELECT * FROM
    OPENROWSET(
        BULK 'C:\dev\workspace\Portfolio\DataAnalyst_2_Finances\RawData\BJKEOGH-MASTERCARD.csv'
        , FORMATFILE = 'C:\dev\workspace\Portfolio\DataAnalyst_2_Finances\OpenRowSet_BulkImport_Scripts\MASTERCARD_csv_file_import_set.txt'
        , FIRSTROW=2
        , FORMAT='CSV'
    ) AS TMP

The contents of the format file are as follows (i have added a blank line to the bottom of the file);

15.0
7
1 SQLCHAR 0 10 ',' 1 Date SQL_LATIN1_General_CP1_CI_AI
2 SQLCHAR 0 10 ',' 2 Type SQL_LATIN1_General_CP1_CI_AI
3 SQLCHAR 0 100 ',' 3 Description SQL_LATIN1_General_CP1_CI_AI
4 SQLCHAR 0 10 ',' 4 Value SQL_LATIN1_General_CP1_CI_AI
5 SQLCHAR 0 10 ',' 5 Balance SQL_LATIN1_General_CP1_CI_AI
6 SQLCHAR 0 20 ',' 6 Name SQL_LATIN1_General_CP1_CI_AI
7 SQLCHAR 0 50 '\r\n' 7 Account SQL_LATIN1_General_CP1_CI_AI

A sample of the CSV file is as follows;

Date, Type, Description, Value, Balance, Account Name, Account
09-Nov-21,Purchase,'AMZNMKTPLACE AMAZON.CO AMAZON.CO.UK GBR,17.99,,'B J KEOGH,'121212******1212
09-Nov-21,Purchase,'MARKS AND SPENCER PLC BIRMINGHAM GBR,10.85,,'B J KEOGH,'121212******1212
10-Nov-21,Purchase,'DVA LICENSING COLERAINE GBR,30,,'B J KEOGH,'121212******1212
10-Nov-21,Purchase,'TESCO STORES 5392 BIRMINGHAM GBR,1.3,,'B J KEOGH,'121212******1212
11-Nov-21,Purchase,'TESCO STORES 6227 HOCKLEY GBR,13.9,,'B J KEOGH,'121212******1212
12-Nov-21,Purchase,'Prime Video*QR1EA1PT5 353-12477661 GBR,7.99,,'B J KEOGH,'121212******1212

Am I missing something?

CodePudding user response：

The problem is your format file. Remove the single quotes, and the INSERT works fine:

15.0
7
1 SQLCHAR 0 0 , 1 Date SQL_LATIN1_General_CP1_CI_AI
2 SQLCHAR 0 0 , 2 Type SQL_LATIN1_General_CP1_CI_AI
3 SQLCHAR 0 0 , 3 Description SQL_LATIN1_General_CP1_CI_AI
4 SQLCHAR 0 0 , 4 Value SQL_LATIN1_General_CP1_CI_AI
5 SQLCHAR 0 0 , 5 Balance SQL_LATIN1_General_CP1_CI_AI
6 SQLCHAR 0 0 , 6 Name SQL_LATIN1_General_CP1_CI_AI
7 SQLCHAR 0 0 \r\n 7 Account SQL_LATIN1_General_CP1_CI_AI

Once I did that, your data was opened without issue.