Bulk insert from CSV - country codes are not coming to SQL table-CodePudding

I have a simple CSV file including 60 rows of data like below:

"Name","City","Region","Primary Phone","Postal Code"
"MyCompany Inc.","Toronto","GTA"," 1 (416) 999-9999","M5G 1Z8"
...

In my SQL query, I read the file using bulk insert like below:

CREATE TABLE #RegionalOffices (
        [REIT] [NVARCHAR](255) NULL,
        [City] [NVARCHAR](255) NULL,
        [Region] [NVARCHAR](255) NULL,
        [PrimaryPhone] [NVARCHAR](255) NULL,
        [PostalCode] [NVARCHAR](255) NULL
    )
BULK INSERT #RegionalOffices
FROM 'C:\Scripts\regionaloffices.csv'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    CODEPAGE = '65000',
    TABLOCK
)

Here how my data seems in the table when i do select *:

Name               City       Region   PrimaryPhone      PostalCode
"MyCompany Inc."   "Toronto"  "GTA"    "(416) 999-9999"  "M5G 1Z8"

My problem is: The country code " 1" does not come to the table from the csv file, same happens for every other rows and I don't see why it is happening. Any help would be appreciated.

CodePudding user response：

It is working for me.

I modified CODEPAGE = '65001'

Starting from SQL Server 2017 onwards there is one more parameter BULK INSERT parameter FORMAT='CSV'. It removes double quotes delimiter for each column.

SQL

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.RegionalOffices;

CREATE TABLE dbo.RegionalOffices
(
    [REIT] [NVARCHAR](255) NULL,
    [City] [NVARCHAR](255) NULL,
    [Region] [NVARCHAR](255) NULL,
    [PrimaryPhone] [NVARCHAR](255) NULL,
    [PostalCode] [NVARCHAR](255) NULL
);

BULK INSERT dbo.RegionalOffices
FROM 'e:\temp\regionaloffices.csv'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    CODEPAGE = '65001',
    TABLOCK
)

-- test
SELECT * FROM dbo.RegionalOffices;

Output

 ------------------ ----------- -------- --------------------- ------------ 
|       REIT       |   City    | Region |    PrimaryPhone     | PostalCode |
 ------------------ ----------- -------- --------------------- ------------ 
| "MyCompany Inc." | "Toronto" | "GTA"  | " 1 (416) 999-9999" | "M5G 1Z8"  |
 ------------------ ----------- -------- --------------------- ------------