MS 2019 OpenRowSet大容量插入CSV文件- (0行受影响)

Question

我试图用OPENROWSET大容量插入将CSV文件加载到MS上，并插入0行。

我在中使用以下代码；

-- 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

格式文件的内容如下(我在文件的底部添加了一个空行)；

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

CSV文件的示例如下；

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

我是不是遗漏了什么？

Answer 1

问题在于您的格式文件。删除单引号，INSERT就能正常工作：

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

一旦我这么做了，你的数据就被打开了，没有问题。

​

​