Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

BULK INSERT with NULLs

Posted on 2003-11-14
7
Medium Priority
?
797 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

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…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

715 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