January 5, 2022 at 11:05 pm
Hey there, new to SQL Server and the forum.
I am trying to bulk import a CSV file with this line
BULK INSERT [dbo].[TestTracingImportSP]
FROM 'D:\TestTracingImport\CleanTest.csv'
WITH (FIRSTROW=2, LASTROW=1000, FIELDTERMINATOR=',', ROWTERMINATOR='\r');
It does not give any errors but it also does not import anything. Is there any way I can figure out why this is happening and how to get it work?
I have already tried changing the ROWTERMINATOR to every possible combo, I have tried DATAFILETYPE = 'char' and FORMAT='CSV" but those don't work
January 6, 2022 at 2:06 pm
Can you confirm the row terminator through something like Notepad++?
The code seems ok but it does depend a lot on the actual import file formatting which we can't see.
January 6, 2022 at 2:52 pm
yes, it shows CRLF and I have tried changing it to both unix and mac using the EOL converter but no luck.
January 6, 2022 at 3:01 pm
yes, it shows CRLF and I have tried changing it to both unix and mac using the EOL converter but no luck.
A shot in the dark here ..
Since you have confirmed that the row terminator is CrLf
Should this ROWTERMINATOR='\r')
not be ROWTERMINATOR='\r\n'
January 6, 2022 at 3:07 pm
I have tried \n \r \r\n and \t , none of them give me an error and say query completed successfully but 0 affected rows.
I have tried moving the file and if i do it doesnt find it. I checked file permissions, next I am going to try manually creating a file to see if that works and there is something funky with the file I receive.
January 6, 2022 at 3:11 pm
railman wrote:yes, it shows CRLF and I have tried changing it to both unix and mac using the EOL converter but no luck.
A shot in the dark here .. Since you have confirmed that the row terminator is CrLf
Should this ROWTERMINATOR='\r')
not be ROWTERMINATOR='\r\n'
Looking at the docs it would appear that ROWTERMINATOR='\n') might do the trick
Note
Due to how Microsoft Windows treats text files (\n automatically gets replaced with \r\n).
January 6, 2022 at 3:17 pm
UPDATE
I copied the data lines only out of the file and created a new file and it successfully imported ( using \r)
Then i copied the header and several data lines and removed the " " quotations from around the column header names and it also imported.
I can not control the format of the file I receive but obviously I can write some kind of windows script to strip these quotes out but I would rather not have to. Does anyone know why these quotes would cause it to fail and how to fix it.
BTW, thanks for the help, I am having a lot of fun getting into SQL server and hope to learn a lot more and become much more proficient.
January 6, 2022 at 3:23 pm
I have never worked with BULK INSERT, but maybe try adding
FIELDQUOTE = 'quote_characters'
January 6, 2022 at 3:30 pm
OK, I tried
WITH (FIRSTROW=2, LASTROW=1000, FIELDTERMINATOR=',', ROWTERMINATOR='\r', FIELDQUOTE='quote_characters');
But got the following error
Invalid quote character specified for bulk load. Quote character can be one single byte or Unicode character.
If you don't mind me asking, if you don't work with BULK INSERT but have every imported files how do you do it?
January 6, 2022 at 3:35 pm
OK, I tried
WITH (FIRSTROW=2, LASTROW=1000, FIELDTERMINATOR=',', ROWTERMINATOR='\r', FIELDQUOTE='quote_characters');
But got the following error
Invalid quote character specified for bulk load. Quote character can be one single byte or Unicode character.
If you don't mind me asking, if you don't work with BULK INSERT but have every imported files how do you do it?
What is your quote_characters? Single quotes, double quotes?
For double quotes, I would try FIELDQUOTE='"'
Hoping one of the heavy hitters from the site might show us the way.
The last time I did file imports was about 15 years ago. We used SSIS to do the imports. We had a windows process that separated files of specific formats into diff folders. Then we had a separate SSIS package for each folder. It was a nightmare.
January 6, 2022 at 4:24 pm
the quote characters are double quotes. I tried using FORMAT = 'CSV' but got these errors
Msg 4879, Level 16, State 1, Line 1
Bulk load failed due to invalid column value in CSV data file D:\TestTracingImport\TCIX-_Trace_1070220210700.csv in row 6, column 13.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)"
My understanding is that one of the problems with the BULK INSERT is that it is kind of an all or nothing type process.
==================================
My plot thickens some of the files will import fine with
BULK INSERT [dbo].[TestTracingImportSP]
FROM 'D:\TestTracingImport\TCIX-_Trace_5070220210700.csv'
WITH (FIRSTROW=2, LASTROW=1000, FIELDTERMINATOR=',', ROWTERMINATOR='\r', MAXERRORS = 50);
But a few of the files give the error:
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
And in those files I have tried taking out the first line, last lines, a bunch of lines in between but nothing seems to work. These files come from the same source and look identical in every way. I have inspected the end of line characters, removed lines etc.
I can not understand why one will import and the other will not. I have even had one file import sometimes and not other times without any changes. Is there any other method for importing that is a bit more forgiving or maybe I can specify the columns and do an insert another way?
January 6, 2022 at 4:33 pm
using "\n" worked fine for me with your sample file.
WITH (FIRSTROW=2, LASTROW=1000, FIELDTERMINATOR=',', ROWTERMINATOR='\n');
January 6, 2022 at 4:47 pm
yes, I have tested several files now and have found that even though the files come from the same source and look identical and have the same EOL Characters there is something different about them.
The ones that work will work with \n or \r , the ones that don't work won't work with anything. I have created a stored procedure and it works with the good files, but not with the bad. So I clearly have some kind of issue with a few of the files. Each day I get six files and it appears that three of them work and three do not.
These files are all the same so I have no idea why one of them works and one doesnt. I have tested and found out that it appears to be the first, fifth and sixth file that are always the ones not working. So I will try to dig deeper and see if I can find what the difference is and report back to see if there is a work around for those files.
Thanks so much for all the help so far. If anyone cares to look I have included one working and one not working file. The file TCIX_Trace_1010620220700.csv does NOT work and the one where the numbers start with 2 DOES work.
January 6, 2022 at 4:52 pm
both files worked for me
if object_id('dbo.TestTracingImportSP') is not null
drop table dbo.TestTracingImportSP
create table dbo.TestTracingImportSP
(
[Initial] varchar(200)
,[Number] varchar(200)
,[Location City] varchar(200)
,[State] varchar(200)
,[Month] varchar(200)
,[Day] varchar(200)
,[Time] varchar(200)
,[L or E] varchar(200)
,[Event] varchar(200)
,[Train ID] varchar(200)
,[Destination City] varchar(200)
,[State1] varchar(200)
,[Reporting RR] varchar(200)
)
truncate table dbo.TestTracingImportSP
BULK INSERT dbo.TestTracingImportSP
FROM 'C:\downloads\TCIX-_Trace_2010620220700.csv'
WITH (FIRSTROW=2, LASTROW=1000, FIELDTERMINATOR=',', ROWTERMINATOR='\n');
select count(*)
from TestTracingImportSP
truncate table dbo.TestTracingImportSP
BULK INSERT dbo.TestTracingImportSP
FROM 'C:\downloads\TCIX-_Trace_1010620220700.csv'
WITH (FIRSTROW=2, LASTROW=1000, FIELDTERMINATOR=',', ROWTERMINATOR='\n');
select count(*)
from TestTracingImportSP
output in both = 999 rows Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply