creating extract(flat file) by selecting from multiple tables 1

[nithink]

Hi,
Very much new to MS-Access and my first assignment in
Access.I've got a couple of questions.

I need to create an extract by selecting from multiple
tables and put it in a flat file. In the case of Oracle we
use to spool the output to a file. Is there anyway in
Access we can do anything like that ?

What I did was, created a temp table and inserted those
selected rows into it and did a export of the data ? Is
this a correct way of doing it ?

And the problem I have with exporting is, the output flat
file has to be formatted ie each of the selected fields
have to be of particular length whereas the exported file
doesn't look like it. SO is there anyway of formatting the
length of the field in the select query itself like using
rpad or lpad in Oracle ?

So my questions would be, how to create an extract file by
selecting from different tables with a definite format ?

Hope I addressed the problem correctly.

 

[BuGlen]

In your database, create a query that will return the rows you want to export.

Open the new query to display the results, and with the results showing on the screen, select "Export" from the file menu.

When the file dialog appears, select "Text" in the "Save as Type" field at the bottom and click "Export All".

An Access wizard will walk you through selecting delimited or fixed width (which is what I think you're looking for) and help you export the data.

That's all there is to it.

Hope this helps.

- Glen

Know thy data.

 

[nithink]

Thanks Glen for your response. I have created the query and
able to run it and fetch all the rows. And I exported it to
a file. But the output file has to be of a fixed format, I
mean the fields have to be of a fixed length but it isnt
rite now. Is there anything to make formatting the length
like rpad or lpad ? And while exporting there are no options for doing that ? So I guess it has to be done in the query itself. Is there anyway to do that ?
Thanks

 

[BuGlen]

Which version of Access are you running?

- Glen

Know thy data.

 

[nithink]

Glen, the version is Access 2002

Thanks.

 

[BuGlen]

OK, then try this:

1. Open the query in to display the returned rows
2. Select the "Export..." option from the File menu
3. In the Export dialog, select "Text Files (*.txt;*.csv;*.tab;*.asc)" from the drop-down list in the "Save as type:" field at the bottom of the dialog box
4. Click the "Export All" button and a "Export Text Wizard" dialog should appear
5. On the wizard dialog, click the "Fixed Width - ..." option and click the "Next" button
6. You should then be presented with a sample of the data with a series of verticle lines to define your field widths. Click on each line and drag it to adjust the width of each column.
7. Click the "Next" button and provide the path and name of the file in which to export the data.
8. Click the "Finish" button to export the data

This should produce the fixed width format that you are looking to generate. Also, here is an article at Microsoft that explains how to save the parameters of the export so you don't have to walk through the wizard every time:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209740

Hope this helps.

- Glen

Know thy data.

 

[nithink]

Thanks Glen.It worked fine.
-GR