Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

BULK INSERT with NULLs

Posted on 2003-11-14
7
Medium Priority
?
802 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
Independent Software Vendors: 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!

 

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

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…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

824 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