BCP OUT Fixed-Length Record: Truncated Length 
Author Message
 BCP OUT Fixed-Length Record: Truncated Length

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



Mon, 19 Feb 2001 03:00:00 GMT
 BCP OUT Fixed-Length Record: Truncated Length

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.
Quote:

> 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



Tue, 20 Feb 2001 03:00:00 GMT
 BCP OUT Fixed-Length Record: Truncated Length

Quote:

> 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



Tue, 20 Feb 2001 03:00:00 GMT
 BCP OUT Fixed-Length Record: Truncated Length
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
~~~~~~~~~~~~~~~~~


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



Sun, 25 Feb 2001 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

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

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

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

4. Using BCP for fixed length transfer

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

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

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

8. Problems with bcp format file for Fixed Length file

9. BCP w/ Fixed length fields ?

10. fixed length output with bcp

11. Bcp in null values with fixed length fields?


 
Powered by phpBB® Forum Software