BCP OUT Fixed-Length Record: Truncated Length

BCP OUT Fixed-Length Record: Truncated Length

Post by John B. Morro » Fri, 04 Sep 1998 04:00:00



Here's my BCP OUT format file -- it should explain my field structure:

6.0
13
1  SQLCHAR   0   10      ""      1     id
2  SQLCHAR   0   5       ""      2     prefix
3  SQLCHAR   0   25      ""      3     first
4  SQLCHAR   0   20      ""      4     middle
5  SQLCHAR   0   25      ""      5     last
6  SQLCHAR   0   5       ""      6     suffix
7  SQLCHAR   0   35      ""      7     address1
8  SQLCHAR   0   35      ""      8     address2
9  SQLCHAR   0   25      ""      9     city
10 SQLCHAR   0   2       ""      10    state
11 SQLCHAR   0   5       ""      11    zip5
12 SQLCHAR   0   4       ""      12    zip4
13 SQLCHAR   0   10      "\r\n"  13    phone

Challenge:  I must export a fixed-length record to an outside client, and
all records must be exactly the same length.

Situation:  In the above structure, some of the trailing fields (like ZIP4
and PHONE) are null

Problem:  BCP is truncating the record if, for instance, PHONE field is
null.  The record length should be 208 including CR/LF at end; if phone is
null, recl = 198.

Question:  Does anybody know of a way to set BCP to export the "pure" fixed
length of each and every field, including any final fields which may
contain nulls?  

I realize I could force-fill those nulls with zeroes or some such filler,
but I don't wanna pump filler into a 4-million record table -- that ain't
my style.  In the meantime, my only solution is to set up this file
structure in Foxpro, import the BCP-ed file, and then export it.  On
export, Fox will not truncate a record just because trailing fields are
empty.  But this step will be time-consuming if I'm working with a million
records.

Any ideas?  

John Morrow
WKA
Winston-Salem NC

 
 
 

BCP OUT Fixed-Length Record: Truncated Length

Post by Greg Druia » Sat, 05 Sep 1998 04:00:00


This might work: create a VIEW of your table in which NULLS are replaced by
filler characters.  This will relieve you of the need to add filler to your
large table.  Then BCP OUT the view.

> Here's my BCP OUT format file -- it should explain my field structure:

> 6.0
> 13
> 1  SQLCHAR   0   10      ""      1     id
> 2  SQLCHAR   0   5       ""      2     prefix
> 3  SQLCHAR   0   25      ""      3     first
> 4  SQLCHAR   0   20      ""      4     middle
> 5  SQLCHAR   0   25      ""      5     last
> 6  SQLCHAR   0   5       ""      6     suffix
> 7  SQLCHAR   0   35      ""      7     address1
> 8  SQLCHAR   0   35      ""      8     address2
> 9  SQLCHAR   0   25      ""      9     city
> 10 SQLCHAR   0   2       ""      10    state
> 11 SQLCHAR   0   5       ""      11    zip5
> 12 SQLCHAR   0   4       ""      12    zip4
> 13 SQLCHAR   0   10      "\r\n"  13    phone

> Challenge:  I must export a fixed-length record to an outside client, and
> all records must be exactly the same length.

> Situation:  In the above structure, some of the trailing fields (like ZIP4
> and PHONE) are null

> Problem:  BCP is truncating the record if, for instance, PHONE field is
> null.  The record length should be 208 including CR/LF at end; if phone is
> null, recl = 198.

> Question:  Does anybody know of a way to set BCP to export the "pure" fixed
> length of each and every field, including any final fields which may
> contain nulls?

> I realize I could force-fill those nulls with zeroes or some such filler,
> but I don't wanna pump filler into a 4-million record table -- that ain't
> my style.  In the meantime, my only solution is to set up this file
> structure in Foxpro, import the BCP-ed file, and then export it.  On
> export, Fox will not truncate a record just because trailing fields are
> empty.  But this step will be time-consuming if I'm working with a million
> records.

> Any ideas?

> John Morrow
> WKA
> Winston-Salem NC


 
 
 

BCP OUT Fixed-Length Record: Truncated Length

Post by John B. Morro » Sat, 05 Sep 1998 04:00:00



> This might work: create a VIEW of your table in which NULLS are replaced
by
> filler characters.  This will relieve you of the need to add filler to
your
> large table.  Then BCP OUT the view.

Greg,

A very elegant and very creative solution!  Especially since I'm a "view
guy" anyway; the export I'm doing is already a BCP from a view.

Question:  How would you write the code to do the null replacement?  I'm a
little fuzzy on that, since a view to me is simply a virtual snapshot of
real data; if I replace VIEW null fields with filler, am I not really
updating the originating data fields?

John

 
 
 

BCP OUT Fixed-Length Record: Truncated Length

Post by John B. Morro » Thu, 10 Sep 1998 04:00:00


Greg:

Thanks. Your suggestion prompted the following solution:

I added one element to my view statement -- the last "field" in my select
statement was simply a character (I used a zero) as filler.  [syntax was 0
as "filler"]

Every record ended with a zero in the last byte, which made every record
the exact same fixed length.  I simply adjusted my format file and the BCP
was perfect.

Gracias!

John Morrow
~~~~~~~~~~~~~~~~~


> This might work: create a VIEW of your table in which NULLS are replaced
by
> filler characters.  This will relieve you of the need to add filler to
your
> large table.  Then BCP OUT the view.


> > Here's my BCP OUT format file -- it should explain my field structure:

> > 6.0
> > 13
> > 1  SQLCHAR   0   10      ""      1     id
> > 2  SQLCHAR   0   5       ""      2     prefix
> > 3  SQLCHAR   0   25      ""      3     first
> > 4  SQLCHAR   0   20      ""      4     middle
> > 5  SQLCHAR   0   25      ""      5     last
> > 6  SQLCHAR   0   5       ""      6     suffix
> > 7  SQLCHAR   0   35      ""      7     address1
> > 8  SQLCHAR   0   35      ""      8     address2
> > 9  SQLCHAR   0   25      ""      9     city
> > 10 SQLCHAR   0   2       ""      10    state
> > 11 SQLCHAR   0   5       ""      11    zip5
> > 12 SQLCHAR   0   4       ""      12    zip4
> > 13 SQLCHAR   0   10      "\r\n"  13    phone

> > Challenge:  I must export a fixed-length record to an outside client,
and
> > all records must be exactly the same length.

> > Situation:  In the above structure, some of the trailing fields (like
ZIP4
> > and PHONE) are null

> > Problem:  BCP is truncating the record if, for instance, PHONE field is
> > null.  The record length should be 208 including CR/LF at end; if phone
is
> > null, recl = 198.

> > Question:  Does anybody know of a way to set BCP to export the "pure"
fixed
> > length of each and every field, including any final fields which may
> > contain nulls?

> > I realize I could force-fill those nulls with zeroes or some such
filler,
> > but I don't wanna pump filler into a 4-million record table -- that
ain't
> > my style.  In the meantime, my only solution is to set up this file
> > structure in Foxpro, import the BCP-ed file, and then export it.  On
> > export, Fox will not truncate a record just because trailing fields are
> > empty.  But this step will be time-consuming if I'm working with a
million
> > records.

> > Any ideas?

> > John Morrow
> > WKA
> > Winston-Salem NC

 
 
 

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. .DB format: Where is it documented?

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

4. Trouble using identity ranges for subscribers of a merge replication

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

6. Using value returned by scalar function

7. Using BCP for fixed length transfer

8. CA-sacremento plsql, pro*c needed

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

10. BCP'ing a fixed-field-length text file

11. Problem with bcp import - how to force fixed length

12. Problems with bcp format file for Fixed Length file