How do I export data in a fixed legth format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to take about 6 fields I am receiving in a comma delimited file and
place them into a file to be imported into a Kewill Software system. The
Kewill file needs to be fixed length and they have told me they want a .dat
extension. This file has a ton of fields, of which I will only be populating
a handfull.

How do I get the data into the kewill file and how do I export it in a fixed
length file?
 
Import the data into an Access table. Create a query with the 6 needed fields
in the right order and formatted correctly. Run the query then go to File,
Export, and in Save as Type scroll down to Text. Click on Export All.

Here comes the important part. When the dialog box opens, click the Advanced
button. In the next window select Fixed Width as the File Format. Below you
can see the width options for each field. After making any other changes, do
a Save As and give it a name that you will remember. You have now created an
Export Specification that you can use later. Run the export and make
adjustments as needed. You can get to the Export Spec by doing the above and
selecting Specs button.

Now to automate the process. It can best be done by code; however, a macro
like below will work.

Action: TransferText
Transfer Type: Export Fixed Width
Table Name: qryDates <<< name of your query
File Name: C:\Dates.txt << where you want the file and not with a .dat.

On my computer with A03, naming the file with a .dat cause an error. So you
may need to rename the file with a .dat after the export. Same thing happened
with the following code:

DoCmd.TransferText acExportFixed, "QryDateSpec", "qryDates",
"C:\Temps\Kewill.dat", False, ""
 
Back
Top