Exporting

[Guest]

How do I maintain the format (leading zeros and alpha spacing) in Access? If
I try to export in a space delimited file, I lose the leading zeros. If I
export to a Comma delimited format, I keep the leading zeros, but lose the
spacing (specifically the alpha fields).

Any suggestions?

 

[David Seeto via AccessMonster.com]

Um, what do you mean by "alpha spacing"? The suggestion that comes to mind
is to use a query to explicitly format the presumably numeric field as a
string before exporting, like Format$([NumberField],"00.00").

If you could post a few records from your table and how you'd like them to
be exported, that would help too.

 

[Guest]

Thanks for responding.

Here is the sample you asked for:

The original data looks like this:


Field1 Field2 Field3 Field4 Field5 Field6
0005-25 00469 00005 00025 ML 000000000020.00
0012-06 00469 00012 00006 CAP 000000000001.56
0012-25 00469 00012 00025 ML 000000000010.00
0014-25 00469 00014 00025 TAB 000000000050.00

I would like it to look like this after the export

" 0005-25","00469","00005","00025"," ML","000000000020.00"
" 0012-06","00469","00012","00006"," CAP","000000000001.56"
" 0012-25","00469,"00012","00025"," ML","000000000010.00"
" 0014-25","00469","00014,",00025"," TAB","000000000050.00"

It is the text fields Filed 1 and 5) that I am having trouble with. All the
leading spaces disappear. By the way, I am using Access 2002.

Thanks

 

[David Seeto via AccessMonster.com]

If there are leading spaces in the fields in Access, then they should be
preserved when you export to any text file, so long as you keep the text
qualifier - if you omit the text qualifier and make the file space
delimited, that will throw out the columns, but you should be okay
otherwise.

This leads me to think that you are either opening the output file in
something like Excel, which will automatically format some of the file into
columns, or that fields 1 & 5 are missing the leading zeros in the Access
database. The second option seems more likely to me.

If that's the case, then you will need to build a query to insert the
leading spaces back into these fields before exporting. This looks like it
will be pretty easy for Field1 - it seems to have a constant length, so
just concatenating it to two spaces would be fine ie. NewField1: " " &
[Field1]

Field5 is trickier, since there seem to be a variable number of leading
spaces - is the text in this field meant to be right justified? In that
case, you should be able to do it with something like NewField5: Left("
",10-Len([Field5])) & [Field5], where 10 = the size of the field (and make
sure you have enough spaces for the Left function ie. at least 10).