Solved

Error bulk insert on Date DataType using Format File - XML

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

734 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