Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Error bulk insert on Date DataType using Format File - XML

Posted on 2009-04-12
5
Medium Priority
?
846 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 43

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 1000 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 1000 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

Python: Series & Data Frames With Pandas

Learn the basics of Python’s pandas library of series & data frames and how we can use these tools for data manipulation.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

715 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