Solved

Error bulk insert on Date DataType using Format File - XML

Posted on 2009-04-12
5
824 Views
Last Modified: 2012-05-06
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!
0
Comment
Question by:soapygus
5 Comments
 
LVL 41

Expert Comment

by:pcelba
ID: 24126844
Do you have column length defined in XML?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24126945
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
 

Author Comment

by:soapygus
ID: 24127094
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 24131449
>>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
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 250 total points
ID: 24135086
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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question