Exporting Table Data - Not all data is exported

  • Thread starter Thread starter Steve Vickers
  • Start date Start date
S

Steve Vickers

I have a table that has say 100 records in it. When I use
the file/export menu command and export the table data to
a text file it does not export the first 20 records.

I am using Access Professional 2003 sp1.

Furthermore...
I get similar results when using the docmd.TransferText
method to import data into the table. I even get fields
containing the wrong data. The text file is | delimited.

Does anyone have any ideas?
Is this an application bug?
Steve.
 
Hi Steve,

This isn't normal, but it will take more information to work out what's
going on.

When you say "the first 20 records", what order are they in? What do
they have in common that the rest don't have?

What software are you using to view the exported text file?

Is it possible that the records are being exported, but not in the order
you expect?

Does this just happen with one table, or with all tables? With one
database, or all?

As far as importing goes, the kind of problem you describe is most often
due to irregularities in the structure of the text file being imported
(e.g. it doesn't match the import specification; there are surplus or
missing "text qualifier" characters, and so on. I've seen one other
mention of a problem with the pipe character as field separator,
however, so it's worth changing the file to tab-separated and seeing
whether that imports cleanly.
 
Thanks John,
When I say the first 20 records I mean the entire export
is correct as far as order is concerned except the first
20 are missing. The entire output is fine except for these
20 records and they are no different to the rest.

I am just using ultra edit to view the file that is
exported.

Even in the export preview pane you get when exporting the
first 20 are missing it always starts at record 21.

I have migrated the table tro another database with the
same results.

It happens on multiple tables in different databases.

With importing there are noi irregularities as far as I
can see.

Here is a sample of the data records in the table you must
remove the quotes. I treat each line as a new record in
the table. This fdata goes on and on in the same format
for many client records.

"@FILENO|2001232"
"@DATE|04 September 2004"
"@TITLE|Mr"
"@INITIALS|J"
"@SURNAME|Citizen"
"@ADDRESS1|1 address Rd"
"@ADDRESS2|"
"@ADDRESS3|"
"@SUBURB|SOMEWHERE"
"@STATE|NSW"
"@POSTCODE|2000"

When I get a chance I will change it to tab separated to
see if the same results happen.
Thanks,
Steve.
-----Original Message-----
Hi Steve,

This isn't normal, but it will take more information to work out what's
going on.

When you say "the first 20 records", what order are they in? What do
they have in common that the rest don't have?

What software are you using to view the exported text file?

Is it possible that the records are being exported, but not in the order
you expect?

Does this just happen with one table, or with all tables? With one
database, or all?

As far as importing goes, the kind of problem you describe is most often
due to irregularities in the structure of the text file being imported
(e.g. it doesn't match the import specification; there are surplus or
missing "text qualifier" characters, and so on. I've seen one other
mention of a problem with the pipe character as field separator,
however, so it's worth changing the file to tab-separated and seeing
whether that imports cleanly.



I have a table that has say 100 records in it. When I use
the file/export menu command and export the table data to
a text file it does not export the first 20 records.

I am using Access Professional 2003 sp1.

Furthermore...
I get similar results when using the docmd.TransferText
method to import data into the table. I even get fields
containing the wrong data. The text file is | delimited.

Does anyone have any ideas?
Is this an application bug?
Steve.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Steve,

From what you've said it seems your table has a single column. In each
record, the one field contains a string formed like this:
@Attribute|Value
e.g.
@FILENO|2009999
@SURNAME|Smith
@STATE|VIC

If that's right, then it is meaningless to say that "the first 20 are
misssing". Remember that a table in a relational database has no
inherent order. The only way you can reliably retrieve the records in a
particular order - and therefore talk about "the first 20" or "the last
20" is by using a query that sorts them into that order on the basis of
the data itself.

With data like the sample you posted, there's nothing that can be used
to maintain the records in order - and therefore no way of keeping the
group of records that refer to a particular file or person together.

On the information to date, it seems more likely that all the records
are being exported but in a different order from what you expect, than
that 20 of them are not being exported.

But maybe there's something you haven't told us.


Thanks John,
When I say the first 20 records I mean the entire export
is correct as far as order is concerned except the first
20 are missing. The entire output is fine except for these
20 records and they are no different to the rest.

I am just using ultra edit to view the file that is
exported.

Even in the export preview pane you get when exporting the
first 20 are missing it always starts at record 21.

I have migrated the table tro another database with the
same results.

It happens on multiple tables in different databases.

With importing there are noi irregularities as far as I
can see.

Here is a sample of the data records in the table you must
remove the quotes. I treat each line as a new record in
the table. This fdata goes on and on in the same format
for many client records.

"@FILENO|2001232"
"@DATE|04 September 2004"
"@TITLE|Mr"
"@INITIALS|J"
"@SURNAME|Citizen"
"@ADDRESS1|1 address Rd"
"@ADDRESS2|"
"@ADDRESS3|"
"@SUBURB|SOMEWHERE"
"@STATE|NSW"
"@POSTCODE|2000"

When I get a chance I will change it to tab separated to
see if the same results happen.
Thanks,
Steve.
-----Original Message-----
Hi Steve,

This isn't normal, but it will take more information to work out what's
going on.

When you say "the first 20 records", what order are they in? What do
they have in common that the rest don't have?

What software are you using to view the exported text file?

Is it possible that the records are being exported, but not in the order
you expect?

Does this just happen with one table, or with all tables? With one
database, or all?

As far as importing goes, the kind of problem you describe is most often
due to irregularities in the structure of the text file being imported
(e.g. it doesn't match the import specification; there are surplus or
missing "text qualifier" characters, and so on. I've seen one other
mention of a problem with the pipe character as field separator,
however, so it's worth changing the file to tab-separated and seeing
whether that imports cleanly.



I have a table that has say 100 records in it. When I use
the file/export menu command and export the table data to
a text file it does not export the first 20 records.

I am using Access Professional 2003 sp1.

Furthermore...
I get similar results when using the docmd.TransferText
method to import data into the table. I even get fields
containing the wrong data. The text file is | delimited.

Does anyone have any ideas?
Is this an application bug?
Steve.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Back
Top