Strange behavior importing from excel sheet

  • Thread starter Thread starter lasse.takvam
  • Start date Start date
L

lasse.takvam

Hello!!

I have a database made with Access 2000 and I use to import price
lists made in Excel. I use
Function TransferSpreadsheet acImport......... and an update query. It
has always worked fine but recently I´m starting to have problems. I
might be sins i started to use Excel 2007 to make the lists. (i used
Excel 2000 earlier)

An example: I have a list with 822 items. When i Import it i only get
622 items imported. No error message or anything. When i study the
list i can se that item number 623 are the first item in the list with
letters in the part number. I then made a new list with only the items
that was not imported and it worked. All the items got in just fine.

I tried again with the whole list but this time I sorted it different
and this time the whole list was imported. I then tried different
sorting options and it seems that it fails only when the list are
sorted by part number. The part number is the primary key and
duplicates are not allowed. I emptyed the table before I imported
anything so it has nothing to do with duplicates.

Kan anyone of you think of any reason for this behaviour? The thing is
that i distribute the price lists to a number of users and I need to
be sure it will work for the end user. I noticed this problem thanks
to an awake end user.

Thanks in advance.

Lasse T
--------------------
 
Hello!!

I have a database made with Access 2000 and I use to import price
lists made in Excel. I use
Function TransferSpreadsheet acImport......... and an update query. It
has always worked fine but recently I´m starting to have problems. I
might be sins i started to use Excel 2007 to make the lists. (i used
Excel 2000 earlier)

An example: I have a list with 822 items. When i Import it i only get
622 items imported. No error message or anything.  When i study the
list i can se that item number 623 are the first item in the list with
letters in the part number. I then made a new list with only the items
that was not imported and it worked. All the items got in just fine.

I tried again with the whole list but this time I sorted it different
and this time the whole list was imported. I then tried different
sorting options and it seems that it fails only when the list are
sorted by part number. The part number is the primary key and
duplicates are not allowed. I emptyed the table before I imported
anything so it has nothing to do with duplicates.

Kan anyone of you think of any reason for this behaviour? The thing is
that i distribute the price lists to a number of users and I need to
be sure it will work for the end user. I noticed this problem thanks
to an awake end user.

Thanks in advance.

Lasse T
--------------------

I just discovered that i get a table called import error after
importing a list sorted by part number. It has all the rows thass not
imported an it says type conversion error. If I sort the list
differently no error table and no errors.

Lasse
-------------------
 
I just discovered that i get a table called import error after
importing a list sorted by part number. It has all the rows thass not
imported an it says type conversion error. If I sort the list
differently no error table and no errors.

Lasse
-------------------

Sorry to disturb you like this but i found the answer myself in this
group. Perheaps this thread can help others. What bugged me the most
was that it seemed to have anything to do with the sort order. What
happend was that the first 20 or so rows had only numbers in the part
number so Acces treated the whole column as numbers. So when the part
numbers with letters came it caused type conversion errors. When I
changed sort order a cell with letters and numbers was placed before
row 20 Access treated it as a text cell and the import works.

Lasse
------------
 
Back
Top