Error bulk insert on Date DataType using Format File - XML

Hello Experts,

I have a fixed width data file with 500k records that I am importing using Bulk Insert.  I have created an xml format file and I am running into an issue when trying to import date fields.

The data.txt file has a date column that stores it's values like this: 19741022  (yyyymmdd)

I'm using SQL2008 and the target column in the database table is of datatype: date

My formatfile.xml for this column is:
 <COLUMN SOURCE="18" NAME="DOB" xsi:type="SQLDATETIME" />

When I run the insert I receive the following error:
type mismatch or invalid character for the specified codepage

Any help is appreciated!
soapygusAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
>>I can change the datatype of the column in the table to 'ntext' and the import is fine.<<
I would not use ntext, but rather char(8) or varchar(8)

>>Is it hacky to import the data and then do a conversion after the import?<<
Not really.  It should be going into a staging table in any case, before going into the final production table(s).

>>I can't find any format file documentation regarding date data types on MSDN.<<
You are right, It should be possible to do it.  Hopefully someone will step up to the plate.
0
 
pcelbaCommented:
Do you have column length defined in XML?
0
 
Anthony PerkinsCommented:
I would have to guess that the format of the date is the problem.  So if you cannot change that, then attempt to change it to:
<COLUMN SOURCE="18" NAME="DOB" xsi:type="SQLCHAR" />

SQL should convert it implicitly to datetime.
0
 
soapygusAuthor Commented:
I have the length set on the <field> element in the format file like so:
<FIELD ID="18" xsi:type="CharFixed" LENGTH="8"/>

If I change the value of the <column> element in the format file to
<COLUMN SOURCE="18" NAME="DOB" xsi:type="SQLCHAR" />

I receive the error: Conversion failed when converting date and/or time from character string.

I can change the datatype of the column in the table to 'ntext' and the import is fine.  Is it hacky to import the data and then do a conversion after the import?

FYI - I must use a format file.  But for testing purposed I made the txt file a .csv file and imported it without a format file and the date values were imported without issue.  Unfortunately, I can't find any format file documentation regarding date data types on MSDN.


0
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
There is some doco on http://msdn.microsoft.com/en-us/library/ms186724.aspx

and http://msdn.microsoft.com/en-us/library/bb630352.aspx

the default string type is yyy-mm-dd but surprised that yyyymmdd isn't working - even as a char(8) sqlchar should convert without issue (as you have witnessed when importing as a CSV).

What if you have it as a datetime in the destination table ?

Generally, though, I always import into a staging table first as acperkins also says above. No matter how clean the data, can do all the validation under the sun and then commit "cleansed" and "approved" transactions and also have the opportunity to output an exception file for subsequent reprocessing.

Little bit more overhead, but in the long run, works so much better, and does overcome these types of issues.

If you don't mind trying the datetime, and maybe posting some non-working sample data with your XML format file, wouldn't mind having a closer look.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.