Importing excel data to access?

[Geir Baardsen]

Hi!

From one of my manufacturers I get new prices in an excel spreadsheet.
Now I want to import this data to my access db.

However, there is a difference:

The items in MyAccess.db (tblItems) are like: OIL hy-123456789
and the items in NewPrices.xls are like: OIL 123456789.

(The "hy" is for separating different items with different values)

How can I write some code to import data from NewPrices.xls (about
5000 items!), have them identify same items in MyAccess.mdb and update
each item with new prices?

Hope anybody have a clue!

Me.Name

[Keith Wilby]

ge***********@hotmail.com (Geir Baardsen) wrote:

The items in MyAccess.db (tblItems) are like: OIL hy-123456789
and the items in NewPrices.xls are like: OIL 123456789.

(The "hy" is for separating different items with different values)

How can I write some code to import data from NewPrices.xls (about
5000 items!), have them identify same items in MyAccess.mdb and update
each item with new prices?

Does the process need to be automated? If not, one approach would be to
import the data 'as is' and then do a 'replace' on the field inquestion,
replacing " " with "-". You can then join the two recordsets in a query and
update as necessary.

Seems too simple ... were you looking for an automated solution?

Regards,
Keith.
www.keithwilby.com

[aaj]

Hi Geir

as well as what Keith has already said, if you are looking at automating,
you can use VBA to open the excel document and read the data in from the
sheet. You could then automatically make the changes as your streaming the
data in, or read it to a temporary table and process the data afterwards,
plenty of choices...

If you are reasonable OK with VBA, its not difficult to do, and I can paste
you some bits of code to help you on your way.

let us know

Andy

"Keith Wilby" <ke*********@AwayWithYerCrap.com> wrote in message
news:Xn************************@10.15.188.42...

ge***********@hotmail.com (Geir Baardsen) wrote:

The items in MyAccess.db (tblItems) are like: OIL hy-123456789
and the items in NewPrices.xls are like: OIL 123456789.

(The "hy" is for separating different items with different values)

How can I write some code to import data from NewPrices.xls (about
5000 items!), have them identify same items in MyAccess.mdb and update
each item with new prices?

Does the process need to be automated? If not, one approach would be to
import the data 'as is' and then do a 'replace' on the field inquestion,
replacing " " with "-". You can then join the two recordsets in a query
and
update as necessary.

Seems too simple ... were you looking for an automated solution?

Regards,
Keith.
www.keithwilby.com

[Geir Baardsen]

Hi!

I found the answer. I just marked the column in excel and choosed
"Edit->Replace" - tool and replaced all "." (dots) with "," (commas).

However, a new and hairraising problem withstands:

The new imported data to my access 2000 db is about 14.000 new
rows. But the column (Category) in the tblItems says 0 (null).

How can I simply change to the wright category without having to
manually click on each of the 14.000 rows in order to set the right
category?

Can I do it programatically from the front-end application, if
so...how?

Category is a field in tblItems which gets its data from
tblCategory, and is joined to tblItems.

(The db is split with only the tables as a backend on the server)