I'm working on SQL Server BULK INSERT process for multiple files into multiple tables. I've run into difficulty with my very first file.
My raw text file looks like this:
Date | Name | ID | Comment
11/11/2016 10:25:29 | Fake Name1 | aa12345 | Fake comment - stuff
14/11/2016 10:43:22 | Fake Name2 | bb23456 | Fake comment - stuff
14/11/2016 12:06:40 | Fake Name3 | cc34567 | Fake comment - stuff
15/11/2016 10:38:33 | Fake Name4 | dd45678 | Fake comment - stuff
My SQL statement like so:
BULK INSERT dbo.[FakeTable]
FROM '...\FakeTextFile.txt'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ' | ',
ROWTERMINATOR = '\r\n',
TABLOCK,
KEEPNULLS
)
Like this, the statement executes without error but fails to insert any records into the table. I added an ERRORFILE argument to try to capture the problem but no error output was returned:
ERRORFILE = '...\Fake_error.txt'
Made various alterations to the ROWTERMINATOR argument ('0x0a', '0x0d', '0x0a0x0d', '0x0d0x0a', 'CHAR(10)', 'CHAR(13)', 'CHAR(10)CHAR(13)', 'CHAR(13)CHAR(10)', '\r\n', '\n\r'). For most of these configurations, the statement executed without error but also without successfully inserting any rows into the table (0 rows affected).
Found that for '0x0a', '0x0d' and '\n' (note single backslash char), the statement inserted the first specified row into the table as expected but returned errors for all subsequent rows in the text file:
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 1 (Date).
Because this comprises part of a wider automated solution, the use of FORMATFILE is not practical if it can be avoided (multiple files, tables; single execution).
Given the data is of the same type in all rows, I suspect that the problem is somehow with the ROWTERMINATOR argument.
I have attempted the introduction of various CODEPAGE and DATAFILETYPE argument configurations but to no avail.
I'd very much appreciate any insights that might help to resolve this problem. Thanks for looking.
UPDATE: Created a format file (XML) like so:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="20"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="4000" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="4000" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="4000" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Date" xsi:type="SQLDATE"/>
<COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="ID" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="4" NAME="Comment" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>
Sadly, execution using this format file yielded the same result:
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 1 (Date).
After much fiddling with both XML and traditional format files, without success, I started to examine the actual values I was attempting to insert more closely.
From my raw data file, the first row with the date '11/11/2016 10:25:29' inserted successfully; the problems began with the second and subsequent rows (dates '14/11/2016 10:43:22', '14/11/2016 12:06:40', '15/11/2016 10:38:33').
SELECT CAST('11/11/2016 10:25:29' AS DateTime)
... resolves correctly and returns '2016-11-11 10:25:29.000'.
BUT...
SELECT CAST('14/11/2016 10:43:22' AS DateTime)
... returns error: 'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.'
In the end, it was just the simple but very annoying 'dd/mm/yyyy' vs. 'mm/dd/yyyy' date format.
Reworked the process outputting the text files to correct the date format and bulk insert process works fine (no format file required).
Thanks all for time and insights.