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

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

Post by John Campo » Tue, 21 Apr 1998 04:00:00



I'm having trouble loading a fixed length file into my sql server 6.5
table
using BCP "Bulk Copy".  Here are the details

--- Example of the data file.  The first 13 characters the record is an
--- 'nsn_number' and the last 5 characters is a 'cage_code'.

123456789012345678  <-- not part of file, used for column counter.

10750136100760NPZ0
107501361007671905
156000001250165273
15600000168850HM72
15600000168850TFG3

--- DB Table layout used for above data. "Notice primary key is an
identity key!"

CREATE TABLE dbo.survey_responses (
 response_id int IDENTITY (1, 1) NOT NULL ,
 nsn_number varchar (13) NOT NULL ,
 cage_code varchar (5) NOT NULL ,
 first_item char (1) NULL ,
 tech_data_package char (1) NULL ,
 prev_year_actual int NULL ,
 curr_year_estimate int NULL ,
 next_year_forecast int NULL ,
 max_estimate int NULL ,
 min_estimate int NULL ,
 material_inventory int NULL ,
 date_created smalldatetime NULL ,
 date_last_modified smalldatetime NULL )

---- BCP statement and format file used for BCP.

C:>bcp wicap..survey_responses in test.txt /f survey.fmt /e survey.err
   /U sa /S TDA_STAGE

               -- survey.fmt file --
6.0
3
1       SQLCHAR       0       1        ""         1       response_id
2       SQLCHAR       0       13      ""         2       nsn_number
3       SQLCHAR       0       5        "\n"      3       cage_code

                                                                ^
                                                                ^table
column order.

--- Below is the result of the BCP into the database table.  As you can
see the identity key
    increments correctly but the 1st character of the nsn_number is lost
resulting in
    the columns being off by 1 to the left.

response_id   nsn_number         cage_code
-----------    -------------         ---------
91        0750136100760  NPZ0
92        0750136100767  1905
93        5600000125016  5273
94        5600000168850  HM72
95        5600000168850  TFG3

Does anyone have a suggestion as to what I may be doing incorrect.  If I
use 0 for
respons_id in the table column order, I receive an error.

Thank you in advance

John Campos

 
 
 

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

Post by Andrew Prosse » Wed, 22 Apr 1998 04:00:00


Just ignore the identity, try it with;

C:>bcp wicap..survey_responses in test.txt /f survey.fmt /e survey.err
   /U sa /S TDA_STAGE /E

               -- survey.fmt file --
6.0
2
1       SQLCHAR       0       13      ""         2       nsn_number
2       SQLCHAR       0       5        "\n"      3       cage_code

/E
Is used when identity values are present within the table to be imported.
When importing data, an identity column is temporarily assigned an identity
value of 0. As the rows are inserted into the table, SQL Server assigns
unique values based on the seed and increment values specified during table
creation. Use the /E flag when identity values already exist in the file. If
SQL Server-generated identity values are preferred, place only non-identity
columns in the file.

Regards
--
Andrew Prosser

 
 
 

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

Hi all,

Is there any way using ASE 11.9 to load fixed form, char
data similar to the ASA 6.0 INPUT INTO command with the
COLUMN WIDTHS spec?  I'm new to ASE, and I noticed that
DBISQL has been replaced with ISQL, and the INPUT INTO
command no longer works.  I have looked at the doc for BCP,
but it looks like the fields have to be delimted and the
data I have to import to the database are all fixed field
records.

Thanks for any help anyone can provide.

Dwayne Butler
SPR Inc.

**** Posted from RemarQ - http://www.remarq.com - Discussions Start Here (tm) ****

2. Problem on Installation of SQL Server 6.5 HELP

3. trouble with bcp and fixed format load files

4. how do i update a datetime field

5. Problems with bcp format file for Fixed Length file

6. 6.4/05 and DEC Unix 4.0d (again!)

7. Re : Bulk Insert - Fixed Length text file

8. How Do I Revert This Freakin' Table?

9. Bulk Insert, fixed-length files, and Nulls

10. BULK INSERT fixed length text file

11. Using BCP for fixed length transfer

12. BCP OUT Fixed-Length Record: Truncated Length