Solved

BULK INSERT with NULLs

Posted on 2003-11-14
7
780 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now