Access Export to a Flat File

[DJM1216]

Hi, I am trying to find if there is a way to set up an Access database which will export the data into a flat file.

I will have 10 columns of data that must have fixed number of characters per column. I need this exported into a text file with the appropriate number of characters per field, so for example for a field that has one character of data with a length of 5 characters, on the flat file I am expecting to see one character with four blank spaces.

If you need further information, please ask.

Thanks in advance for your help.

 

[drjekyll325]

I find the easiest method is to use the Export Text wizard to create a format file, and then use that format file as an argument in the TransferText method.

Look up "Export" in the Access built-in help and you will find all the details.

 

[DJM1216]

Thanks for the tip. The only thing that I am having trouble with is getting the preceeding zeroes to show.

For example, I have a column that is 10 characters wide, with a value of 256 entered into it.

When I export, I want the data in this column to read "0000000256", so that all 10 spaces are used.

Thanks in advance.

 

[mdmilner]

To get the right number of characters, what you can do is check the length of the specific field that you are sending and if it is not the right length, run a For...Next loop to add the calculated number of characters.

strVal = rs!fldname
intLen = len(strVal)
For y = intLen to 9
  strVal = "0" & strVal
Next y

 

[bat17]

You could use the format function in a query to clean up your data for export.

newField:format([oldField],"0000000000")

HTH

Peter

 

[mdmilner]

Will Format handle # of characters for strings?

My method is obviously wrong for #'s, but I've been using something similar for string spacing for awhile now. Always looking for a better method.

 

[bat17]

I don't think that you can force it to add the '#' symbol using format, if you just wanted to pad the string to a set length using spaces then you could use
newField:format([oldField],"@@@@@@@@@@")

If you do actualy need the '#' then you could construct a function in the query like

newField:Right(String(10,"#") & [oldField],10)

HTH

Peter