July 17, 2018 at 7:17 am
Hi,
I am trying to import a Tab delimited file in to a table in SQL Server.
But I am getting this error message:
Msg 4864, Level 16, State 1, Line 6
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 96 (date_entered).
Msg 4864, Level 16, State 1, Line 6
Now the date files that they are talking about here is null, but so are other columns and this column is set to take a null value; so I am not sure what the issue is or how to fix it.
Any ideas I would apricate it.
Also, I would like to put the file address here in a variable but it will not take it any ideas how I can do that too
Thank you
This is the code I am using:
BULK
INSERT [GSCRU_Dev].[dbo].[tempImport_fromID_GS3]
FROM 'C:\Users\jim\Desktop\DTG201807051417_GS_out.txt'
WITH
(
FIRSTROW = 1,
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
);
GO
July 17, 2018 at 7:52 am
itmasterw 60042 - Tuesday, July 17, 2018 7:17 AMHi,
I am trying to import a Tab delimited file in to a table in SQL Server.
But I am getting this error message:
Msg 4864, Level 16, State 1, Line 6
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 96 (date_entered).
Msg 4864, Level 16, State 1, Line 6Now the date files that they are talking about here is null, but so are other columns and this column is set to take a null value; so I am not sure what the issue is or how to fix it.
Any ideas I would apricate it.
Also, I would like to put the file address here in a variable but it will not take it any ideas how I can do that too
Thank youThis is the code I am using:
BULK
INSERT [GSCRU_Dev].[dbo].[tempImport_fromID_GS3]
FROM 'C:\Users\jim\Desktop\DTG201807051417_GS_out.txt'
WITH
(
FIRSTROW = 1,
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
);
GO
When you say the date column is NULL do you mean it's empty in the input file or it actually has the value NULL in it?
July 17, 2018 at 7:58 am
t's empty in the input file
July 17, 2018 at 9:11 am
itmasterw 60042 - Tuesday, July 17, 2018 7:58 AMt's empty in the input file
Okay, does the file perfectly align with the table structure? With 96+ columns that might be harder to check...
July 17, 2018 at 9:52 am
Yes I was able to do this with SSIS so I know it is good. The problem is I am going to run this from a VB.Net application and calling this to load I it works better.
July 17, 2018 at 10:31 am
itmasterw 60042 - Tuesday, July 17, 2018 9:52 AMYes I was able to do this with SSIS so I know it is good. The problem is I am going to run this from a VB.Net application and calling this to load I it works better.
However, the question is whether there might be any "invisible" characters that crept into the file, such as an accidental line feed CHAR(10) or some other non-printable character...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 17, 2018 at 10:44 am
itmasterw 60042 - Tuesday, July 17, 2018 9:52 AMYes I was able to do this with SSIS so I know it is good. The problem is I am going to run this from a VB.Net application and calling this to load I it works better.
Can you post the first line of the file and the table structure?
July 17, 2018 at 11:13 am
If you have tab-delimited files exported with BCP, and loade them into Notepad++ or another editor that can highlight special characters, you may see a lot of NUL (char(0)) values. BCP ouputs an empty string to represent a NULL value, and a NUL character to represent an empty string value.
<tab><tab> A NULL column between two delimiters.
<tab><NUL><tab> An empty string between two delimiters.
July 17, 2018 at 11:38 am
Sorry I unfortunately would not be able to do that since it is private data.
can tell you that I have the following
--> between words and
_ --> _
_ --> __ --> __ --> __ --> _ N __ --> _ At the end
but what would I do if this is the case?
Thank you
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply