Import Errors

[Johnb]

I am importing data from a SAP system into Excel XP and
then altering a dog's dinner of a layout into 5 neat
columns. I then import the columns into Access XP table.
I've automated it and it works fine apart the middle
column/field which report 11 conversion errors in 850
records when it arrives in Access. The data is correct
when it is imported to a table in Access. If I append
say "#" to the first character position to this middle
field it imports without any errors at all. I've tried
using Trim and Copy-Paste Special and values but no
success. All cols are text data types.

Any suggestions ??

TIA johnb

 

[Nikos Yannacopoulos]

John,

Just guessing here, not having seen some example data, but I suspect the
problem has to do with Access "guessing" a column's data type during an
import, based on the first X number of rows (X is defined, I believe, in
a registry key), so when it finds some data further down that don't
match the guess, it errs. So, even if you have set up a column as text
in your spreadsheet, if Access finds numbers in the first X rows it
assumes it is a number column and starts importing it as such, so it
errs when it comes accross the first row containing non-numeric
characters in that column. Preceding all column entries with a "#" (or
whichever non-numeric character, for that matter) simply forces Access
to assume the column is text (this is what it finds in the first X
rows), so it imports correctly.
So much for the explanation of what is (possibly) happening, but this
doesn't solve your problem, does it? To that end, it would help a lot if
you posted some sample data - a few rows that import correctly, and a
few that err.
By the way, "altering a dog's dinner of a layout into 5 neat columns"
suggests you might not be extraxting the report from SAP in the best
possible way; if you want to discuss this (what report? how do you
transfer it to Excel?) I would suggest you send me a private e-mail,
since this falls outside the scope of the NG... extracting data from SAP
contributes a good part of my paycheck, so I might be able to come up
with something.

HTH,
Nikos