Bulk Insert, fixed-length files, and Nulls

Bulk Insert, fixed-length files, and Nulls

Post by Kevin Park » Thu, 21 Nov 2002 19:19:08



Hi,

I've got an issue with using Bulk Insert with SQL Server 7.0.
I am using it to import fixed length ASCII files into SQL server
tables, in conjunction with bcp format files.

The inserts work fine, except that I need all empty values to appear
as Null (they are importing purely as empty values). The relevant
fields in the tables allow Nulls.
Below is a sample of one of the bcp format fields that I'm using:

=============================================================================
7.0
2
1       SQLCHAR       0       3       ""                        1    
CTCDE
2       SQLCHAR       0       15      "\r\n"                    2    
CNTRY

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

This is the T-SQL that I use:

BULK INSERT RealDB.dbo.[tb_country_ref_BISE_stg]
    FROM 'c:\dataimport\country.txt'
          WITH (FORMATFILE = 'c:\dataimport\country.fmt')

I've tried applying Null as the default value in the tables - still no
joy.

The only time that it works is for the last field in the file, so I
presume that the problem has something to do with the recognition of
field termination. The ASCII file contains no field termination on
internal fields.

Any help gratefully appreciated.

Cheers,
Kevin.

 
 
 

1. Re : Bulk Insert - Fixed Length text file

I am trying to import a fixed length text file into sql
server table using bulk insert.

I am using the below procedures where there file has comma
seperated fields and not fixed length fields. How do I
change it for fixed length fields and make it work.

-- Using BCP and .fmt file to import data into table



+ 'bcp "db1..t1" in "C:\data.txt" -f"c:\data.fmt" -t, -q -


---Using Bulk Insert
BULK INSERT temp_acdinterval FROM 'C:\data.txt' WITH
(FORMATFILE='C:\data.fmt',FIELDTERMINATOR = ',',
FIRSTROW=2)

---data.txt
Col1      Col2     Col3
   1  10/01/03       AB
   2  10/02/03       AC

2. replication problem

3. BULK INSERT fixed length text file

4. TN-Tennessee-94073--MS SQL Server-ORACLE-Informix-Database Design Consultant

5. Question on bulk loading of fixed length/fixed field records

6. VFP Recieving MAPI messages

7. having trouble loading a fixed length file using Bulk Copy (BCP)

8. rs.movelast doesn't work, I need a record count, help!

9. BULK INSERT - format file for fixed-width fields

10. BULK INSERT WITH A FIXED WIDTH FILE NO DELIMITED

11. BULK INSERT of Fixed Width Text - With Missing Data in Text File

12. Fixed-Length File Import Inserts Bogus Records