Exporting Access Table to text file

[Mike Bailey]

I put this in the Import / Export section, but never got a response. Any help?

I frequently import text files into Access 2007, then export again with some
format changes. The input and outputs are both tab delimited. I have created
and saved import and export specifications to create the results I need.

The issue is that Access 2007 rearranges the columns. I don't have the
problem with Access 2003 when I use the same specification.

How can I maintain the column order through the import and export processes?

 

[Dirk Goldgar]

I put this in the Import / Export section, but never got a response. Any
help?

I frequently import text files into Access 2007, then export again with
some
format changes. The input and outputs are both tab delimited. I have
created
and saved import and export specifications to create the results I need.

The issue is that Access 2007 rearranges the columns. I don't have the
problem with Access 2003 when I use the same specification.

How can I maintain the column order through the import and export
processes?

I haven't observed this, but it may just not have happened for me. Could
you give an example of what's happening?

Does it happen for all tables you handle this way, or only some of them? Do
you import and export into/from existing tables, or do you delete and
recreate the tables each time?

 

[Mike Bailey]

Dirk,

I've only observed it when I import or export text files with many columns /
fields. The one I'm working on right now has 24. It doesn't seem to matter
whether I delete the table in database prior to import. I have to say that I
haven't followed this through to nail down whether it happens in during
import or export specifically. I do know that I've verified the source text
file is in the correct order, but that the output file is not.

My workaround at this point is to open database with Access 2003 (I kept it
when I installed 2007).

Thanks.

 

[Dirk Goldgar]

This isn't the answer you want but I'll tell you anyway. According to
proper
relational database design, column order doesn't matter. If your database
application depends on column order, the design needs some additional
work.

My impression is that, if Mike is regularly importing text files,
manipulating them, and exporting them again, using import/output
specifications, then the output column order is not his to control. This is
determined by the requirements of some external application. Maybe *that*
application's design needs some work. Sure, Mike can fix this by exporting
a query that selects and orders the columns as desired, but I'd still like
to know if Access 2007 is behaving differently than Access 2003 in an
undocumented way.

I'm not sure yet that we're seeing such a discrepancy in this case, though.
It's possible that the column order has been changed in the table datasheet,
but the field order hasn't been changed in the table's design view. When
Access exports a table, the fields are according to the table's design view,
not the column orders that are used in the table's datasheet view.

 

[Dirk Goldgar]

Dirk,

I've only observed it when I import or export text files with many columns
/
fields. The one I'm working on right now has 24. It doesn't seem to matter
whether I delete the table in database prior to import. I have to say that
I
haven't followed this through to nail down whether it happens in during
import or export specifically. I do know that I've verified the source
text
file is in the correct order, but that the output file is not.

What happens if, as Chris O'C suggested, you export a query that explicitly
selects the fields in the desired order?

In a table that is (mis-)behaving this way, please open the table in design
view and check the field order there. That's the field order I'd expect to
be exported, not necessarily the column order you see in datasheet view.

My workaround at this point is to open database with Access 2003 (I kept
it
when I installed 2007).

Are you saying the exact same database file -- presumably an .mdb, not an
..accdb -- behaves differently in this regard when opened with A2003 vs.
A2007? Or are you talking about two different versions of the database?

 

[Mike Bailey]

The files I build are submitted to a large data system at the US Department
of Education. The files are required to be contructed according to
specifications provided, including field attributes (size, text versus
numeric) and order.

I've looked through the tables that caused me difficulties last week, and
found that two which had 13 fields were rearranged on import, while two with
12 fields were as constructed.

When I import, I bring in field names, which makes it easier to build import
specs and verify results. I strip off the names on export.

As I've mentioned, the process works well in Access 2003, but has unexpected
results in Access 2007. My feeling is that this is an issue with 2007 only.

I'm working on some larger files (20 to 25 fields), and I'll watch closely
through the process to see what happens every step of the way. It may be next
week before I have any more information.

I need to empasize that I can open the import database in 2003 with the same
text files and get the correct results. I can't count on this in 2007.

Thanks.

 

[Dirk Goldgar]

I've looked through the tables that caused me difficulties last week, and
found that two which had 13 fields were rearranged on import, while two
with
12 fields were as constructed.

Are you saying that the field orders are rearranged on *import*? I thought
it was export that was causing the problem?

I'm working on some larger files (20 to 25 fields), and I'll watch closely
through the process to see what happens every step of the way. It may be
next
week before I have any more information.

I look forward to learning what you discover.

I need to empasize that I can open the import database in 2003 with the
same
text files and get the correct results. I can't count on this in 2007.

So then you're saying the exact same database file -- not alternate versions
of it -- is showing different behavior in A2007 vs. A2003. Yes? This is an
important point, so I want to make sure.

 

[Mike Bailey]

Dirk,

You read correctly: I found that 2 files were reordered on import, while the
other 2 were reordered on export.

And yes: I am opening the SAME database in Access 2003 and getting different
results than I get in Access 2007.

 

[Mike Bailey]

Problem solved. The database I was using was saved in Access 2000 format. I
saved in Access 2007 format and the column shuffling stopped.

 

[Dirk Goldgar]

Problem solved. The database I was using was saved in Access 2000 format.
I
saved in Access 2007 format and the column shuffling stopped.

Interesting, and it's nice to know that it could be resolved this way. That
makes it sound even more like a bug.