Solved

BULK INSERT with NULLs

Posted on 2003-11-14
7
788 Views
Last Modified: 2008-02-26
I am trying to load a flat file into a table.  I have outlined all the parts below.  The problem is that BULK INSERT treats NULL as the string "NULL".  How do I produce an actual NULL?  The example is in the first record (ID 12).

------------------------------------------
bcp.fmt
------------------------------------------
8.0
4
1       SQLCHAR       0       10      ""                        1     idfield                      ""
2       SQLCHAR       0       20      ""                        2     idtext                       ""
3       SQLCHAR       0       10      ""                        3     othertext                    ""
4       SQLCHAR       0       10      "\n"                      4     othernum                     ""
==========================================

------------------------------------------
testing.csv
------------------------------------------
0000000012NULL                other     0000000001
0000000018try this            other     0000000001
0000000024DIFFER              other     0000000001
0000000032Friendly            other     0000000001
0000000019snak                other     0000000001

==========================================

------------------------------------------
SQL Table
------------------------------------------
create table bcptest
(
      idfield int not null,
      idtext varchar(20) null,
      othertext varchar(10) null,
      othernum int null
)
==========================================

------------------------------------------
SQL Statement
------------------------------------------
bulk insert DATDB001.dbo.bcptest from 'c:\testing.csv'
      with
      (
            FORMATFILE = 'c:\bcp.fmt'
      )
==========================================

Thank you.
0
Comment
Question by:jessy_houle
7 Comments
 
LVL 10

Expert Comment

by:NeoTeq
ID: 9748525
Why not execute a second query after that:

update DATDB001.dbo.bcptest
   set [yourfieldname] = Null
   where [yourfieldname] = "Null"

It's an easy workaround...

-Neo
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9749017
In the format file use SQLVARIANT for NULL values.

In csv file use "FIND/Replace" functionality in text editior to replace all nulls with empty string.
0
 
LVL 34

Expert Comment

by:arbert
ID: 9750029
NeoTeq, and update query can be quite intensive if you're talking about millions of rows.....

 namasi_navaretnam, "In csv file use "FIND/Replace" functionality in text editior to replace all nulls with empty string."  Not a very good option if you're going to be receiving lots of files or they're too big to edit....

jessy_houle, personally, I would checkout using the Bulk Insert task in DTS.  You have much greater flexibility than just using format files.....
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:jessy_houle
ID: 9750599
NeoTeq:

I will do this.  However, I may get NULL for integer values as well.

namasi_navaretnam:

Changing the column type to SQLVARIANT resulted in the following error:

Disallowed implicit conversion from data type sql_variant to data type varchar, table 'DATDB001.dbo.bcptest', column 'idtext'. Use the CONVERT function to run this query.

Thank you.
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9754231
Jessy,

Are these files coming from external sources? If you also have to handle integers then replacing NULL will empty space will not help. Null is infact defined as '\0'. Either you can wtite a C program to pass these input files and get the output file in desired format.

Using DTS also a good option where you trsfor the data the way you need. I tried this with you sample data and worked well.

Here are the steps,
1) From Enterprise manager right mouseclick on "Data Transformation Services" folder and then select "All tasks/Import Data"
2) For Source select "Text File"  at the bottom and point to your text file. The dislog that pops up will let you define length etc. As an alternative you can also define a DSN that points to a text file.
3) Select destination Database or DSN and click on Next
4) Thrid column is "Tranform". Click on "..." button underneath.
5) Click on "Tranformations" tab  and edit the code to something looks like below.
This code works with you text file that you posted.

Function Main()
      DTSDestination("Col001") = DTSSource("Col001")
                If  Left(DTSSource("Col002"), 4) = "NULL" Then
                     DTSDestination("Col002") = NULL
                Else
                   DTSDestination("Col002") = DTSSource("Col002")
                End If
      DTSDestination("Col003") = DTSSource("Col003")
      DTSDestination("Col004") = DTSSource("Col004")
      Main = DTSTransformStat_OK
End Function

6) Click on next and select apporiprite values and let the DTS package run immediately.

You will see the data transformed as you wish.

select * from testing where COL002 is null
will return null data.

HTH
0
 
LVL 34

Accepted Solution

by:
arbert earned 250 total points
ID: 9754715
"Using DTS also a good option where you trsfor the data the way you need. I tried this with you sample data and worked well"


Like I said above....
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9756404
Bret,

Thanks for the right direction...
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

777 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