Access Export to a Flat File

DJM1216

Board Regular
Joined
Apr 15, 2002
Messages
102
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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

drjekyll325

New Member
Joined
Dec 30, 2003
Messages
24
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

Board Regular
Joined
Apr 15, 2002
Messages
102
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

Well-known Member
Joined
Apr 30, 2003
Messages
1,352
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.

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

bat17

Well-known Member
Joined
Aug 15, 2003
Messages
1,470

ADVERTISEMENT

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

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

HTH

Peter
 

mdmilner

Well-known Member
Joined
Apr 30, 2003
Messages
1,352
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

Well-known Member
Joined
Aug 15, 2003
Messages
1,470
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,161
Messages
5,768,542
Members
425,480
Latest member
br400821

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top