Very Computer

1. 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

2. 'eliminating duplicates' in table

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

4. (no subject)

5. Load fixed length records.

6. German Word Breaker

7. how to import fixed length record from text files

8. BLOB documentation

9. Fixed Record Length data

10. Exporting files with fixed length records.

11. Importing Fixed-Length RECORDS longer than 255 chars

12. Fixed-Length File Import Inserts Bogus Records

13. Creating a fixed length record in Progress v7.3