June 26, 2020 at 9:19 am
Good day,
I would like to import a flat file (pipe delimited) into a SQL table using the script below. For the first file, it run successfully, but the second file gave this error:
Bulk load failed due to invalid column value in CSV data file Jobs.txt in row 477, column 15.
The contents of the column include HTML tags. However, the file that run successfully also contained HTML tags.
The FMT line is blow:
15 SQLCHAR 0 0 "|" 15 sOverview Latin1_General_CI_AS
BULK INSERT [dbo].[Jobs]
FROM 'Jobs.txt'
WITH (
DATA_SOURCE = 'AzureBlob',
FORMAT = 'CSV',
FIRSTROW = 2,
FIELDQUOTE = '\',
FORMATFILE='Jobs.fmt',
FORMATFILE_DATA_SOURCE = 'AzureBlob'
);
Why would it work for 1 file but fail for another?
June 26, 2020 at 12:37 pm
Why would it work for 1 file but fail for another?
The flippant and rather obvious answer is "because the data in the file that fails contains something which the BULK INSERT cannot handle."
The difficult bit is ascertaining what that 'something' is. Without seeing the data, I'm not sure that anyone here will be able to help you.
If you can post a row of data which works alongside one which fails, we will have more chance of providing useful input.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 26, 2020 at 8:16 pm
To add to what Phil said, the error you got seems to indicate the problem:
Bulk load failed due to invalid column value in CSV data file Jobs.txt in row 477, column 15.
So in the file Jobs.txt, on line 477, column number 15, it has an invalid value. Now what this invalid value is, we have no idea, but as you have access to the file, it should be fairly easy to open it up and have a look to see what is wrong, no? Go to line 477, look at column 15 and compare it to the lines above which succeeded.
Best guess - mismatched data types (such as storing a string value (NA, NULL, etc) in a numeric column) or there is no column 15 on line 477.
But as Phil pointed out, without seeing the file (or at least a successful line and the failed line), it is impossible to know what caused the error.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply