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.
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" |
+------------------+-----------+--------+---------------------+------------+