Solved

Error bulk insert on Date DataType using Format File - XML

Posted on 2009-04-12
5
827 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 42

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Substring works but need to tweak it 14 31
sql query 5 43
sql select total by week ending 3 23
Why is this SQL bringing back extra rows? (parsing XML data) 4 35
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

740 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