Re: Import Errors

From: Nikos Yannacopoulos (nyannacoREMOVETHISBIT_at_in.gr)
Date: 03/22/05 

Date: Tue, 22 Mar 2005 10:14:23 +0200

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 spread***, 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

Johnb wrote:
> 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
>