I am trying to upload dummy data which would help in uploading bigger files. The dummy data is as follows:
+---------+-------+----------+------------+------+---------------------------------------------------------------+
| Name | Class | Subject | ExamDate | Mark | Description |
+---------+-------+----------+------------+------+---------------------------------------------------------------+
| Prabhat | 4 | "Math" | 02/10/2013 | 25 | "Test data for prabhat.' |
| Murari | 5 |"Science" | 02/11/2013 | 24 | "Test data for his's test, where we can test 2nd ROW, Test." |
| sanjay | 4 | "Science"| | 25 | "Test Only." |
| xyz | 5 |"Science,"| | | "" |
+---------+-------+----------+------------+------+---------------------------------------------------------------+
The problem that forces me to upload data through BULK INSERT is that I do not want to change the data type of ExamDate column to date after uploading it.
Therefore, I created a table and uploaded data through bulk insert using following piece of code:
CREATE TABLE test_data (
[Name] NVARCHAR(MAX) NULL,
[Class] NVARCHAR(MAX) NULL,
[Subject] NVARCHAR(MAX) NULL,
[ExamDate] date NULL,
[Mark] INT NULL,
[Description] NVARCHAR(MAX) NULL
)
BULK INSERT test_data
FROM 'D:\TP - Churn\text.csv'
WITH
(
FIRSTROW = 2,
FORMAT = 'CSV',
FIELDQUOTE = '"',
FIELDTERMINATOR = '"\"', --CSV field delimiter
ROWTERMINATOR = '\n', --Use to shift the control to next row
TABLOCK
)
The above query gives the an error as follows Incorrect syntax near 'FORMAT'.
Removing FORMAT = 'CSV' line and executing would give the same error but this time with FIELDQUOTE.
The point of introducing FIELDQUOTE = '"' is to deal with the last row, where in subject column there is a , after science. This entry in csv looks like "Science," .
As per Incorrect syntax near format in BULK INSERT? I think we can not use FIELDQUOTE while using SQL Server 2016.
Edit: So far I've managed to this with a format file as follows:
If that is true than how do we deal with the issue stated above?
11.0
6
1 SQLCHAR 0 8000 "," 1 Name SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 40 "," 2 Class SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 40 "\"," 3 Subject SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 40 "," 4 ExamDate SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 40 "," 5 Mark SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1000 "\"\r\n" 6 Description SQL_Latin1_General_CP1_CI_AS
The results are as follow:
+---------+-------+-----------+------------+------+-------------------------------------------------------------+
| Name | Class | Subject | ExamDate | Mark | Description |
+---------+-------+-----------+------------+------+-------------------------------------------------------------+
| Prabhat | 4 | "Math | 10/02/2013 | 25 | "Test data for prabhat. |
| Murari | 5 | "Science | 11/02/2013 | 24 | "Test data for his's test, where we can test 2nd ROW, Test. |
| sanjay | 4 | "Science | NULL | 25 | "Test Only. |
| xyz | 5 | "Science, | NULL | NULL | " |
+---------+-------+-----------+------------+------+-------------------------------------------------------------+
The quotes at the beginning are not being removed in both Subject & Description column. Furthermore, last row and last column was empty but in the results its not NULL.
Result of file opened in notepad:
Prabhat,4,"Math",02/10/2013,25,"Test data for prabhat."
Murari,5,"Science",02/11/2013,24,"Test data for his's test, where we can test 2nd ROW, Test."
sanjay,4,"Science",,25,"Test Only."
xyz,5,"Science,",,,""
