Fixing corrupted table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Seeking wisdom...

Have a group of individual Access 2000 databases (one for each client and,
no, I would not have built it that way) that all have the same basic
structure. In one of the tables in each database there are individual
records in which the data in the fields have become "split", causing an
offset in the data from one field to the following one, e.g. [FirstName] =
"John" and [LastName]="Jones" becomes [FirstName] = "Jo" and [LastName] =
"hn" and [NextField] = "Jones". As these errors continue and multiply, the
information appears to slant off to the right in datasheet view. After x
number of lines of this, it returns to normal.

There is some type of unprintable character (small box, believe it is either
a CR or an LF) where each offset occurs.

We're trying to import the data from these individual databases into a new
A2K database. The offsets are causing errors, most notably converting all of
the fields to text because of the stray characters in each column. I've
tried exporting the table to both Excel and comma-delimited formats. The
control character disappears from the output (meaning that I can't do a
search and destroy), but the fields are still split.

From what my user tells me, another dbms program (he doesn't know which) was
used to access the data and the corrupted records are all (he thinks) ones
that should have been deleted from the table.

Question 1: Has anyone seen this type of behavior before?
Question 2: If so, can anyone suggest a way to fix the tables, short of
manually editing each one? (There are about one hundred.)

Any advice greatly appreciated!

Thanks,
Bruce
 
If there is a consistent pattern in the data you could right your code to go
through the records one by one starting at the first and count each, then
based on the pattern of the data have it place correctly into a new table. If
there is only one hundred it may be quicker to just do it manually, rather
than writing the code, depending on the number of fields in the table,
However it would be more fun to wrrite the code to fix it! - SPARKER
 
Sparker,
Thanks for the input. Client was worried about loosing good records. Told
him it could be fixed, but would be quicker to manually edit. He choose the
cheaper alternative, of course!
Bruce

sparker said:
If there is a consistent pattern in the data you could right your code to go
through the records one by one starting at the first and count each, then
based on the pattern of the data have it place correctly into a new table. If
there is only one hundred it may be quicker to just do it manually, rather
than writing the code, depending on the number of fields in the table,
However it would be more fun to wrrite the code to fix it! - SPARKER

BruceS said:
Seeking wisdom...

Have a group of individual Access 2000 databases (one for each client and,
no, I would not have built it that way) that all have the same basic
structure. In one of the tables in each database there are individual
records in which the data in the fields have become "split", causing an
offset in the data from one field to the following one, e.g. [FirstName] =
"John" and [LastName]="Jones" becomes [FirstName] = "Jo" and [LastName] =
"hn" and [NextField] = "Jones". As these errors continue and multiply, the
information appears to slant off to the right in datasheet view. After x
number of lines of this, it returns to normal.

There is some type of unprintable character (small box, believe it is either
a CR or an LF) where each offset occurs.

We're trying to import the data from these individual databases into a new
A2K database. The offsets are causing errors, most notably converting all of
the fields to text because of the stray characters in each column. I've
tried exporting the table to both Excel and comma-delimited formats. The
control character disappears from the output (meaning that I can't do a
search and destroy), but the fields are still split.

From what my user tells me, another dbms program (he doesn't know which) was
used to access the data and the corrupted records are all (he thinks) ones
that should have been deleted from the table.

Question 1: Has anyone seen this type of behavior before?
Question 2: If so, can anyone suggest a way to fix the tables, short of
manually editing each one? (There are about one hundred.)

Any advice greatly appreciated!

Thanks,
Bruce
Click to expand...
Click to expand...
 
Back
Top