Fixed Width - No delimiters - ASCII Text File

[Novice2000]

Hello I have Access 2007.

I need to export data to a Fixed Width - No delimiters - ASCII Text File. I
have specific guidelines on this export to meet left justified, specific
character lenght fields. It is not to be delimited in anyway. It is straight
text.

I have been told that when I output to the .txt file type from Access, I
will either need to build a table first, where the fields are text and the
lengths match the layout and then you export from it.

Or I can build a formatting query and export from that. The formatting
query should use the built-in formatting functions within Access to match the
export criteria.

Once I have either built a table or a query I am to utilize the export
functionality within Access. And within the ‘Export’ functionality, choose
is to export a ‘Fixed’ file not a ‘Delimited’ file.

So far so good. I think the formatting query would be the way to go. Can
anyone tell me how to create this. I do not see where I can specify the
character lengths and the left justification in a query?

Any help is much appreciated.

 

[Douglas J. Steele]

Let's assuming you're trying to left-justify a ten character field. You'd
use the following as a computed field in your query:

Left([MyField] & Space$(10), 10)

If you want to right-justify a twelve character field, you'd use

Right(Space$(12) & [MyField], 12)

 

[Novice2000]

Thanks but I do not know where to include this? Is this something taht goes
under criteria?

Let's assuming you're trying to left-justify a ten character field. You'd
use the following as a computed field in your query:

Left([MyField] & Space$(10), 10)

If you want to right-justify a twelve character field, you'd use

Right(Space$(12) & [MyField], 12)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello I have Access 2007.

I need to export data to a Fixed Width - No delimiters - ASCII Text File.
I
have specific guidelines on this export to meet left justified, specific
character lenght fields. It is not to be delimited in anyway. It is
straight
text.

I have been told that when I output to the .txt file type from Access, I
will either need to build a table first, where the fields are text and the
lengths match the layout and then you export from it.

Or I can build a formatting query and export from that. The formatting
query should use the built-in formatting functions within Access to match
the
export criteria.

Once I have either built a table or a query I am to utilize the export
functionality within Access. And within the 'Export' functionality,
choose
is to export a 'Fixed' file not a 'Delimited' file.

So far so good. I think the formatting query would be the way to go. Can
anyone tell me how to create this. I do not see where I can specify the
character lengths and the left justification in a query?

Any help is much appreciated.

 

[Clifford Bass]

Hi,

Basic question: Why not use the External Data ribbon, Export section,
Text File item? In the wizard you can choose a Fixed Width type of export.
Choose Next button. Drag the field-divider lines as needed. Click Advanced
button. Choose Save As button. Give it a name, then OK 2X, then Finish.
You can then use this export specification in code if running the process
from code. Or you can simply choose it next time you export by going to the
Advanced button again. If you need right-justified numbers, you can use
Douglas's process. Place it in your query in the top line where the fields
go like so:

MyFieldRightJustified: Right(Space$(12) & [MyField], 12)

This will cause the calculated value to have the name of
MyFieldRightJustified, so use whatever is appropriate for your situation.

Clifford Bass

 

[Novice2000]

Thanks did that. But I have difficulty figuring out the start number. So, for
instance the first field is 11 characters so start = 1 and width = 11 so the
next start would be = 12 and with witdth = 50, the next start =62?

Hi,

Basic question: Why not use the External Data ribbon, Export section,
Text File item? In the wizard you can choose a Fixed Width type of export.
Choose Next button. Drag the field-divider lines as needed. Click Advanced
button. Choose Save As button. Give it a name, then OK 2X, then Finish.
You can then use this export specification in code if running the process
from code. Or you can simply choose it next time you export by going to the
Advanced button again. If you need right-justified numbers, you can use
Douglas's process. Place it in your query in the top line where the fields
go like so:

MyFieldRightJustified: Right(Space$(12) & [MyField], 12)

This will cause the calculated value to have the name of
MyFieldRightJustified, so use whatever is appropriate for your situation.

Clifford Bass

Hello I have Access 2007.

I need to export data to a Fixed Width - No delimiters - ASCII Text File. I
have specific guidelines on this export to meet left justified, specific
character lenght fields. It is not to be delimited in anyway. It is straight
text.

I have been told that when I output to the .txt file type from Access, I
will either need to build a table first, where the fields are text and the
lengths match the layout and then you export from it.

Or I can build a formatting query and export from that. The formatting
query should use the built-in formatting functions within Access to match the
export criteria.

Once I have either built a table or a query I am to utilize the export
functionality within Access. And within the ‘Export’ functionality, choose
is to export a ‘Fixed’ file not a ‘Delimited’ file.

So far so good. I think the formatting query would be the way to go. Can
anyone tell me how to create this. I do not see where I can specify the
character lengths and the left justification in a query?

Any help is much appreciated.

 

[Clifford Bass]

Hi,

If you were only given the field lengths, just fire up Excel and enter
them in a column (say starting in A1). Enter 1 into B1.

In B2 use this formula:

=C1+1

Copy that formula down to the bottom on the list.

In C1 use this formula:

=A1+B1-1

Copy that formula down to the bottom of the list.

You now have all the start and end locations.

Clifford Bass

 

[Novice2000]

Thank you Clifford you really made my day!!!

Hi,

If you were only given the field lengths, just fire up Excel and enter
them in a column (say starting in A1). Enter 1 into B1.

In B2 use this formula:

=C1+1

Copy that formula down to the bottom on the list.

In C1 use this formula:

=A1+B1-1

Copy that formula down to the bottom of the list.

You now have all the start and end locations.

Clifford Bass

 

[Clifford Bass]

Hi,

Glad to help and you are quite welcome!

Clifford Bass