Import from Excel - Messy (1 Viewer)

maw230 · Aug 3, 2010

Well, i have a problem that i dont see an easy fix too. I have an excel spreadsheet put together by a co-worker that needs to be imported into a newly created access database.
The problem lies in the way the data was input into the spreadsheet. Basically, it's a mess. Each tab is an individual store number. Within the tab there are several columns that contain various store info fields.

however when changing from tab to tab the field names (although referring to the same thing) may be spelled differently, in different order, etc.

the reason im stretching so far out on such a long limb is because if i dont find some way to import this data (or manipulate/group it in Excel somehow) I will be spending hours manually entering in each record.

ghudson · Aug 3, 2010

If the columns contain the same data within each worksheet and they are in the same order then you can delete row one and import the data without a header row into your database using the transfer spreadsheet method.

maw230 · Aug 3, 2010

i think i will try something similar to this. the columns are not in the same order but i think rearranging and importing will be much faster than entering each in.
i just hope the data is rife (sp?) with errors.

CanvasShoes · Aug 3, 2010

I hope you don't mind the hijack, but I have an excel import problem as well. I have an excel spreadsheet with exactly the same column heading names as the Access table into which I want to import it. But I keep getting the import error message "FIELD "F1" does not exist in destination table "Name".

Um... yes, yes it does. The field name in my Table is called say "Site Name" and so is the field name in the excel spreadsheet. They're in the same column space, they're exactly the same format, font type, etc, but something's haywire.

Thank you in advance.

gemma-the-husky · Aug 4, 2010

to MAW

massaging data to a normalised form from excel is often never easy. as soon as the data becomes unnormalised, then all of the automatic procedures you would use become less reliable. the bigger the spreradsheet, the bigger the problem really.

to CANVASSHOES
access has probably found a blank column that was used in excel. select a stack of columns to the right of the data you want to import, and delete them even though they appear to be empty. that should allow the import to proceed.

generally, it is better to import a csv if you have one. you get a lot more control.

CanvasShoes · Aug 4, 2010

gemma-the-husky said:
to MAW

to CANVASSHOES
access has probably found a blank column that was used in excel. select a stack of columns to the right of the data you want to import, and delete them even though they appear to be empty. that should allow the import to proceed.

generally, it is better to import a csv if you have one. you get a lot more control.

Thanks so much. It ended up that your assessment above was pretty close to right on the money. Not a blank column, but misplaced ones.

I had originally exported the table to excel, added my data (I SO wish dbases allowed an easier manipulation of the data!), and then was ready to import it back. Anyway, after I started messing about with it, (a coworker and I), we discovered that the original table had not, in fact, exported the columns in the same order they were in the database. Why would that be?

Irritating in the extreme, luckily I didn't have hundreds of records to deal with, so rather than risk a "sort" error, I just canned most of the spreadsheet and cut and pasted to the original table in my database.

But all that said, I really SO appreciate all of you Access Gurus' quick answers whenever I'm stuck!

maw230 · Aug 4, 2010

MAW

massaging data to a normalised form from excel is often never easy. as soon as the data becomes unnormalised, then all of the automatic procedures you would use become less reliable. the bigger the spreradsheet, the bigger the problem really.

would you recommend setting input masks and validation rules and inputting the data record by record into Access?

ghudson · Aug 4, 2010

Going forward, I would get rid of the process you have where users are manually updating a spreadsheet that you are importing into an Access table. Too many ways for a user to mess up the data and formats when you allow users to key into an Excel spreadsheet.

maw230 · Aug 4, 2010

ghudson,
Yes, that is the reason for the database in the first place! before, the user was entering all of his data into a terrible spreadsheet, and now I get the pleasure of re-entering it back into Access before i hand it over to him

.
thanks for the help again, though.

gemma-the-husky · Aug 5, 2010

the idea of having a database is to manage the data in the database, and not to maniplulate it outside the database. Once the dbs is set up, iys FAR easier to manage data in access, compared with excel

importing stuff would normally be done via a csv, generated from some external system. Excel is definitely the worst option for me , as there are too many ways for users to "mess up" a spreadsheet.

maw230 · Aug 5, 2010

right. this is the reason for the database.
however, i do have to re-enter all of the retroactive data from excel into access.

Import from Excel - Messy (1 Viewer)

maw230

somewhat competent

ghudson

Registered User.

maw230

somewhat competent

CanvasShoes

Registered User.

gemma-the-husky

Super Moderator

CanvasShoes

Registered User.

maw230

somewhat competent

ghudson

Registered User.

maw230

somewhat competent

gemma-the-husky

Super Moderator

maw230

somewhat competent

Similar threads

Users who are viewing this thread