Exporting Fixed Width Text File

[Jen Scott]

I've been trying to export a fixed width text file with some fields that
contain blank values. When I specify the start position and length of the
fields, Access 2007 won't export the blank fields with the correct number of
spaces I need. Each record/row must be a certain width (224 characters).
When I put in a length of 28 for one of my blank fields, it only puts 11
spaces in that field -- I've tried increasing the length, but still only get
11 spaces in the .txt file. What am I doing wrong? Thanks in advance!

 

[Guest]

I've been trying to export a fixed width text file with some fields that
contain blank values. When I specify the start position and length of the
fields, Access 2007 won't export the blank fields with the correct number
of
spaces I need. Each record/row must be a certain width (224 characters).
When I put in a length of 28 for one of my blank fields, it only puts 11
spaces in that field -- I've tried increasing the length, but still only
get
11 spaces in the .txt file. What am I doing wrong? Thanks in advance!

 

[John W. Vinson]

I've been trying to export a fixed width text file with some fields that
contain blank values. When I specify the start position and length of the
fields, Access 2007 won't export the blank fields with the correct number of
spaces I need. Each record/row must be a certain width (224 characters).
When I put in a length of 28 for one of my blank fields, it only puts 11
spaces in that field -- I've tried increasing the length, but still only get
11 spaces in the .txt file. What am I doing wrong? Thanks in advance!

A Table field trims trailing blanks. Try exporting from a Query rather than
from the table. In 2003 and earlier you can create an Export Specification, I
don't have 2007 installed so I'm not sure how or whether to do so there; but
you can put in a calculated field

ExpField1: [Field1] & String(" ", 27 - Len([Field1]))

to tack the needed number of blanks on to the field value.

 

[Jen Scott]

When I put this into my query it runs, but returns "Error#" in the field
name. I think this might work when there are values in the fields, but my
field is blank and I need to export a certain # of blank spaces (16 to be
exact) before the first value in the fixed width text file. If I try to
export it with the function, it gives me another error and won't export.
Thanks!
--
Jen Scott

I've been trying to export a fixed width text file with some fields that
contain blank values. When I specify the start position and length of the
fields, Access 2007 won't export the blank fields with the correct number of
spaces I need. Each record/row must be a certain width (224 characters).
When I put in a length of 28 for one of my blank fields, it only puts 11
spaces in that field -- I've tried increasing the length, but still only get
11 spaces in the .txt file. What am I doing wrong? Thanks in advance!

A Table field trims trailing blanks. Try exporting from a Query rather than
from the table. In 2003 and earlier you can create an Export Specification, I
don't have 2007 installed so I'm not sure how or whether to do so there; but
you can put in a calculated field

ExpField1: [Field1] & String(" ", 27 - Len([Field1]))

to tack the needed number of blanks on to the field value.

 

[John W. Vinson]

When I put this into my query it runs, but returns "Error#" in the field
name. I think this might work when there are values in the fields, but my
field is blank and I need to export a certain # of blank spaces (16 to be
exact) before the first value in the fixed width text file. If I try to
export it with the function, it gives me another error and won't export.

If the field is ALWAYS blank don't export it at all - just use a calculated
field ExpBlank: String(16, " ")

You can use the NZ() function to replace a string with a null value, or an
expression like IIF(IsNull([fieldname]), String(16, " "), <expression>)