Solved

BULK INSERT with NULLs

Posted on 2003-11-14
7
793 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
[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
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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

738 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