Exporting data to a .txt file

[sara]

I have what I think is a little strange...I have to get data from our
payroll system into a specific format (fixed record length) as a .txt
or .prn file only to upload to our 401k custodian.

I can get the data into Access (from Payroll) and write a query with
all the fields (inserting fields with spaces, as needed), but I can't
figure out how to get it to export to a .txt file - without the field
name header row.

I don't really understand Transfertext command - I don't get the
"specifications " parameter. And I can't find posts that explain that
to me. I think I have to do it for my own data to understand what it
all does.

SO, can anyone help? I have a query to output the data, and I think I
can write code to run the query. Or maybe it's just
DoCmd.TransferT ext - but my syntax and parameters are clearly wrong.

Thanks
Sara

My query is "qryB19Contribu tions" and I want to output it to
"TestFile.t xt" on my E: drive.

I don't want the header, so I thought just leaving out "Has Field
Names" would do that.

Do I have to write my data to a table first, then export the table?

DoCmd.TransferT ext acExportDelim, "qryB19Contribu tions", , "E:
\TestFile.txt")

[storrboy]

On Feb 28, 8:09 pm, "sara" <saraqp...@yaho o.comwrote:

I have what I think is a little strange...I have to get data from our
payroll system into a specific format (fixed record length) as a .txt
or .prn file only to upload to our 401k custodian.

I can get the data into Access (from Payroll) and write a query with
all the fields (inserting fields with spaces, as needed), but I can't
figure out how to get it to export to a .txt file - without the field
name header row.

I don't really understand Transfertext command - I don't get the
"specifications " parameter. And I can't find posts that explain that
to me. I think I have to do it for my own data to understand what it
all does.

SO, can anyone help? I have a query to output the data, and I think I
can write code to run the query. Or maybe it's just
DoCmd.TransferT ext - but my syntax and parameters are clearly wrong.

Thanks
Sara

My query is "qryB19Contribu tions" and I want to output it to
"TestFile.t xt" on my E: drive.

I don't want the header, so I thought just leaving out "Has Field
Names" would do that.

Do I have to write my data to a table first, then export the table?

DoCmd.TransferT ext acExportDelim, "qryB19Contribu tions", , "E:
\TestFile.txt")

I think the syntax you need is (watch for line wraps)...

DoCmd.TransferT ext acExportFixed, [SpecificationNa me],
"qryB19Contribu tions", "E:\TestFile.tx t"

However I *think* that to export a fixed-width file you must first
create an export specification. Easiest way to do this is to perform
an export once using the File>Export menu. Got through the motions of
picking the file path and name, click the Export button and a wizard
should open up. There should be an advanced button on it, click this
to open the Specifications tab. Make the neccessary changes to field
names, types etc. and click the Save As button to save the export
specification. Give it a name and remember it or write it down.
Continue the export. When you are done replace the [SpecificationNa me]
in my above example with the spec name you just saved - ie.
"MyExportSp ec". The TransferText method should work now.

[sara]

I am Stunned! And forever grateful! I think I now understand how to
create the Export Spec and I actually created a form to run the code
and I have a file!

Now, I have to test with the custodian, but I never could have gotten
this far without your help.

Many many thanks.
sara

[sara]

Maybe I spoke too soon?

While the .txt file is created, it does not have the leading zeroes
that I put in the spec. For example, and SSN that begins with 0 is
truncated.
The contribution amount, which the custodian wants as 9(07)v99, and I
set as 9 digits in the spec, comes over without the leading zeroes,
and with the decimal (which should not be included) and the data is
truncated:

11401750913 1435690 **A24.43 **A0
**A0 4

This should be (wrapping)
11401750913 001435690 **A000002443 **A000000000
**A000000000 4

The query DOES output the data correctly, as I have formatted the
fields in the query design grid: (001435690 is the SSN; 24.43 is the
contribution amount)

11401750913 001435690 ** A 000002443 ** A 000000000
** A 000000000 4

I don't know how to:
1. Get rid of the decimal to format the $ amount properly
2. "Keep" the leading zeroes

Any ideas?

thanks
sara

[Steve Cartnal]

Sara,

I am working on a very similar problem right now and I believe that the only
way you're going to keep the leading zeros in a number field is to convert
it to text somewhere prior to exporting it to the text file. (As you have
seen, the export specification wizard allows you to use leading zeros in
dates, but doesn't provide that option for numbers.) You could either enter
an SSN into a text field to begin with, as opposed to a number field, or you
can convert the number to text (which is where my problem began). I couldn't
get any of the conversion functions to convert a date into text. I could get
it to look like text, but come time to export to the text file, it was still
treated as a date. I think you may want to look at the CStr function if you
want to try to convert before exporting. If you have no reason to enter the
SSN's as actual numbers (sorting on that field is the only thing I can think
of that that would make a number field necessary), it may just be easier to
enter them as text from the start.HTH

Steve
"sara" <sa*******@yaho o.comwrote in message
news:11******** **************@ p10g2000cwp.goo glegroups.com.. .

Maybe I spoke too soon?

While the .txt file is created, it does not have the leading zeroes
that I put in the spec. For example, and SSN that begins with 0 is
truncated.
The contribution amount, which the custodian wants as 9(07)v99, and I
set as 9 digits in the spec, comes over without the leading zeroes,
and with the decimal (which should not be included) and the data is
truncated:

11401750913 1435690 **A24.43 **A0
**A0 4

This should be (wrapping)
11401750913 001435690 **A000002443 **A000000000
**A000000000 4

The query DOES output the data correctly, as I have formatted the
fields in the query design grid: (001435690 is the SSN; 24.43 is the
contribution amount)

11401750913 001435690 ** A 000002443 ** A 000000000
** A 000000000 4

I don't know how to:
1. Get rid of the decimal to format the $ amount properly
2. "Keep" the leading zeroes

Any ideas?

thanks
sara

[storrboy]

On Mar 1, 1:16 pm, "Steve Cartnal" <scart...@allte l.netwrote:

Sara,

I am working on a very similar problem right now and I believe that the only
way you're going to keep the leading zeros in a number field is to convert
it to text somewhere prior to exporting it to the text file. (As you have
seen, the export specification wizard allows you to use leading zeros in
dates, but doesn't provide that option for numbers.) You could either enter
an SSN into a text field to begin with, as opposed to a number field, or you
can convert the number to text (which is where my problem began). I couldn't
get any of the conversion functions to convert a date into text. I could get
it to look like text, but come time to export to the text file, it was still
treated as a date. I think you may want to look at the CStr function if you
want to try to convert before exporting. If you have no reason to enter the
SSN's as actual numbers (sorting on that field is the only thing I can think
of that that would make a number field necessary), it may just be easier to
enter them as text from the start.HTH

Steve

"sara" <saraqp...@yaho o.comwrote in message

news:11******** **************@ p10g2000cwp.goo glegroups.com.. .

Maybe I spoke too soon?

While the .txt file is created, it does not have the leading zeroes
that I put in the spec. For example, and SSN that begins with 0 is
truncated.
The contribution amount, which the custodian wants as 9(07)v99, and I
set as 9 digits in the spec, comes over without the leading zeroes,
and with the decimal (which should not be included) and the data is
truncated:

11401750913 1435690 **A24.43 **A0
**A0 4

This should be (wrapping)
11401750913 001435690 **A000002443 **A000000000
**A000000000 4

The query DOES output the data correctly, as I have formatted the
fields in the query design grid: (001435690 is the SSN; 24.43 is the
contribution amount)

11401750913 001435690 ** A 000002443 ** A 000000000
** A 000000000 4

I don't know how to:
1. Get rid of the decimal to format the $ amount properly
2. "Keep" the leading zeroes

Any ideas?

thanks
sara

As Steve mentioned you need to convert them into text, but don't use
CStr or other conversion functions on the actual field. The reason is
that even though you may have it formated in the table with leading
zeros, it's still just a number - formatting does NOT change the
value, just displays it differently. You can convert it in the query
by not including the actual field, but a calculated one based on it.
For example, assume fields [SSN] and [Amnt], and in the query design
create the following calculated fields...

TextSSN: Format([SSN], "000000000" )
TextAmnt Format(([Amnt]*100),"00000000 0")

(The amount field is multiplied by 100 to remove the decimal as per
your example. If your data contains a decimal place setting other than
two, change the multiplication accordingly.)

If the field values are SSN - 1234 and Amnt $25.98 then the query
should return 000001234 and 000002598. Since these query fields are
now strings (format function returns strings) it should export with
the leading zeros.

In case you did not encounter this before, run through the export
manually again, load the Spec you created last time, but watch for a
new window or the opportunity to size the fields. When I did it, a
window opened that placed a line between the fields based on their
fixed size but did not place a space between them. If your output
requires space between the fields, drag the line to the right so that
the required number of spaces are added.

Keep in the mind that doing this requires the importing program to be
able to convert these fields to appropriate data types. It should
easily convert them to numbers, but it would have to know that the
Amnt field should have a decimal somewhere in it.

Hope I didn't confuse you even more!

[sara]

On Mar 1, 1:58 pm, "storrboy" <storr...@sympa tico.cawrote:

On Mar 1, 1:16 pm, "Steve Cartnal" <scart...@allte l.netwrote:

Sara,

I am working on a very similar problem right now and I believe that the only
way you're going to keep the leading zeros in a number field is to convert
it to text somewhere prior to exporting it to the text file. (As you have
seen, the export specification wizard allows you to use leading zeros in
dates, but doesn't provide that option for numbers.) You could either enter
an SSN into a text field to begin with, as opposed to a number field, or you
can convert the number to text (which is where my problem began). I couldn't
get any of the conversion functions to convert a date into text. I could get
it to look like text, but come time to export to the text file, it was still
treated as a date. I think you may want to look at the CStr function if you
want to try to convert before exporting. If you have no reason to enter the
SSN's as actual numbers (sorting on that field is the only thing I can think
of that that would make a number field necessary), it may just be easier to
enter them as text from the start.HTH

Steve

"sara" <saraqp...@yaho o.comwrote in message

news:11******** **************@ p10g2000cwp.goo glegroups.com.. .

Maybe I spoke too soon?

While the .txt file is created, it does not have the leading zeroes
that I put in the spec. For example, and SSN that begins with 0 is
truncated.
The contribution amount, which the custodian wants as 9(07)v99, and I
set as 9 digits in the spec, comes over without the leading zeroes,
and with the decimal (which should not be included) and the data is
truncated:

11401750913 1435690 **A24.43 **A0
**A0 4

This should be (wrapping)
11401750913 001435690 **A000002443 **A000000000
**A000000000 4

The query DOES output the data correctly, as I have formatted the
fields in the query design grid: (001435690 is the SSN; 24.43 is the
contribution amount)

11401750913 001435690 ** A 000002443 ** A 000000000
** A 000000000 4

I don't know how to:
1. Get rid of the decimal to format the $ amount properly
2. "Keep" the leading zeroes

Any ideas?

thanks
sara

As Steve mentioned you need to convert them into text, but don't use
CStr or other conversion functions on the actual field. The reason is
that even though you may have it formated in the table with leading
zeros, it's still just a number - formatting does NOT change the
value, just displays it differently. You can convert it in the query
by not including the actual field, but a calculated one based on it.
For example, assume fields [SSN] and [Amnt], and in the query design
create the following calculated fields...

TextSSN: Format([SSN], "000000000" )
TextAmnt Format(([Amnt]*100),"00000000 0")

(The amount field is multiplied by 100 to remove the decimal as per
your example. If your data contains a decimal place setting other than
two, change the multiplication accordingly.)

If the field values are SSN - 1234 and Amnt $25.98 then the query
should return 000001234 and 000002598. Since these query fields are
now strings (format function returns strings) it should export with
the leading zeros.

In case you did not encounter this before, run through the export
manually again, load the Spec you created last time, but watch for a
new window or the opportunity to size the fields. When I did it, a
window opened that placed a line between the fields based on their
fixed size but did not place a space between them. If your output
requires space between the fields, drag the line to the right so that
the required number of spaces are added.

Keep in the mind that doing this requires the importing program to be
able to convert these fields to appropriate data types. It should
easily convert them to numbers, but it would have to know that the
Amnt field should have a decimal somewhere in it.

Hope I didn't confuse you even more!- Hide quoted text -

- Show quoted text -

No, you didn't - you explained a lot! I actually got it all to work!
I found another post that showed the string function (just like
yours), so I totally got it. I'm on the second set of files now, and
the custodian is looking over the first set.

Again, thanks so much for your help, and thanks to Steve too. I anm
pretty proud of myself to have gotten this, but I couldn't have done
it without this group, and your help specifically.

Sara