How to check within a format file whether a date field has the value "00000000" and if so, replace with a null?

I have to convert several Oracle's CTL script files to a SQL Server 2014 format files.  I have 90% of the conversion complete, with the exception of the date fields.  Within the DAT file that we receive from our client, if a date value is not given, the value of "00000000" is specified (as oppose to leaving the field null).  

When I try to do a BULK INSERT, I get an error message indicating "Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row #, column 11 (ADMSN_DT)".  The cause of the error is the string value "00000000".

Is there a way of specifying the following Oracle statement in T-SQL of a format file?

ADMSN_DT                   DATE 	'YYYYMMDD' NULLIF ADMSN_DT="00000000"

Open in new window

My format file has the field defined as data type "DATE".  Is there a way of imbedding logic into the format file to null out the field if it reads the value "00000000" for a give date field (as done in Oracle)?  

thank  you.
Maria TorresData AnalystAsked:
Who is Participating?
Maria TorresData AnalystAuthor Commented:
Mark, thanks for clarifying what a staging table is.  I'm going to try it right after I try one more thing with the format file.  I just read a document on how to use a format file to skip a data field.  I'm going to try to see if I can skip the four date fields that have the value as "00000000".  If the format file does not work, then I will try the staging table.

I'll let you know the outcome.
Mark WillsTopic AdvisorCommented:
bring it in as a varchar. then check for ISDATE() and NULLIF(yuorcolumn,'000000')

Depending on what version of SQL Server - you can also do a TRY_CONVERT()  instead of isdate()

And you can embed them   try_convert(datatype,(nullif(yourcolumn,'000000')), stylecode)

where style code tells convert what format your string is :

nullif (2008):
isdate (2008):
try_convert (2012):

declare @datetime2 datetime2
declare @datetime datetime
declare @date date

set @date = try_convert(date,'00000000',100)
set @datetime = try_convert(datetime,'00000000',100)
set @datetime2 = try_convert(datetime2,'00000000',100)

select @date,@datetime,@datetime2 

Open in new window

You cannot tell the FORMAT FILE how to decipher the string. You will have to do it after the data is loaded.

So, import into a "staging" file first, then put away into the appropriate tables.
Maria TorresData AnalystAuthor Commented:
Hi Mark,

I'm not a SQL Developer, and I do not know what you mean by "import into a staging file".  Can you please explain or point me to a site where I can get further information on this?  Thank you.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
Easiest way is probably to use SSIS rather than BULK INSERT statement.

SSIS allows data conversion/transform step(s) so you could change the data prior to load.
Mark WillsTopic AdvisorCommented:
A staging table is a real table with a unique name so it doesnt become confused with the live / production data.

Even something as simple as the prefix TMP_STAGING_.... with the correct column names, just make any contentious datatypes VARCHAR or NVARCHAR so you can inspect and fix.

The other strategy is to make all columns the correct data type and address each error as you encounter them and try again. You can keep dropping / creating the table as often as needed, but that can get very frustrating.

The idea behind a staging table is you can import into that table, do any manipulations / transformations within that, and when happy with the data, update / insert into the live table

Does that make sense ?
Mark WillsTopic AdvisorCommented:
You can skip fields in a format file (use 0), but it will skip every occurrence - even the 'good' dates unfortunately.

How are you importing ?

If using OPENROWSET, you have the chance to intercept as part of the select and can use either try_convert() or NULLIF()

If you would like to share some sample data and your format file, I could set up a sample for you.
Maria TorresData AnalystAuthor Commented:
From my understanding the four date fields that are giving me trouble will always have the value "00000000", because they are considered to be confidential fields.  So, I can skip them without any concern.

However, I do have a question.  I ran the BULK INSERT.. format file and was able to load 500K records into the table.  Only three records failed to load.  I did get an error report which states the lines that failed and error code "HRESULT 0x80004005".  I looked through the web but all I found is that I should make sure that each line ends with carriage return code.  (I looked through my file with Notepad++ and all lines are ending correctly.)  Is there a more meaningful way of getting the name of the field and a description of why it failed (e.g., field size too large)?  In Oracle, we are able to populate an error report that is informative (i.e., line, field name, and error description).  

Any input is greatly appreciated.  Thank you.
Maria TorresData AnalystAuthor Commented:
The Microsoft document that I read provided me with the solution to my problem.  By specifying the value of zero to the format's columns "Host File Data Length" and "Server Column Name", I can bypass the specific date fields that have a string value "00000000" and proceed with the loading of the data.
Mark WillsTopic AdvisorCommented:
Yep, think I said you can skip fields...

Little bit disappointed that my contributions didnt assist resolving your problem. Ah well :)
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.