Question on bulk loading of fixed length/fixed field records

Question on bulk loading of fixed length/fixed field records

Post by Dbutle » Mon, 12 Jul 1999 04:00:00



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) ****

 
 
 

Question on bulk loading of fixed length/fixed field records

Post by ckvese » Wed, 14 Jul 1999 04:00:00



Quote:>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) ****

Hi, Dwayne

Yes, you can load from a file with fixed format if you are using a
format file (the -f parameter in bcp-command).

You just give the lengt of each column in the format file without giving
a delimeter for the columns.

If you have the documentation for bcp, you can see how a the contents
of a format-file.

Claus

 
 
 

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

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

2. US-VA-ORACLE PROGRAMMER

3. Load fixed length records.

4. Language Support for Portuguese?????

5. BCP OUT Fixed-Length Record: Truncated Length

6. Help with error message please

7. BCP: Fixed record length but no field terminator (Fixed field lengths) - Possible?

8. OPEN INGRES AVAILABILITY

9. ADOX: Extending a fixed length Jet string field length

10. Importing a fixed fieldwidth, fixed row length file

11. Re : Bulk Insert - Fixed Length text file

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