Excel Spreadsheet will not import into Table (1 Viewer)

[TBlatnica]

Hi,

I am failry new to Access, and am trying to import an Excel spreadsheet into a Table. I go all the way through the import wizard, and it gives me an error that it cannont create, and then it goes back to the last screen, and I have no Idea what is wrong.

I have looked under many help's and online tips, and searched this site to no avail. As far as I can tell everything should be acceptable to import. I have unique column header titles, no blank rows, nothing over 255 characters, and it still does not work. Can anyone give me some advice on what is wrong in this spreadsheet causing it not to be imported?


Thanks!

Tony

 

[damonc]

Hi Tony,
I have tried to import your spreadsheet into a new dbase and also receive the error message. Don't quite know what's wrong, but I did notice some points about your data.

You really should be using several tables to store your information, rather than just one.

You should have:

tblItems
itemID (PK)
coil id
description
configuration
serial number
field strength
tblManufacturer.manufacturerID (FK)
part number
date
tblLocations.locationID (FK)
tblArea.areaID (FK)

tblManufacturer
manufacturerID (PK)
name
address
contact etc

tblLocations
locationID (PK)
location
details etc

tblArea
areaID (PK)
area
details etc

This is called normalizing your data, meaning you only have to store one copy of repetitive information (manufacturer details etc). If this doesn't make sense, search these forums for "normalize" "relationships" "lookup".

Getting back to your import problem. If there are only 24 records, it might be better to setup your table sturcture properly and then manually enter data...?

HTH and Good Luck!

 

[TBlatnica]

damonc,

Thanks for the input! I had already tried to parse it down from a huge spreadsheet into 3 tables, but now I see more are needed. I am still new to this DB thing.

I wish there were only 23 records; we currently have over 224 coils and the list is ever growing and changing, and moving around.

Any suggestions on how to import the data once I create all the new fields under the design view mode? (also I am new to this (fk) thing, so to creat an (fk) do I use the lookup wizard under the datatype to define the relationship?)

Thanks again! I might ask you more as I get further along, if you don't mind.

Thanks!

Tony

 

[damonc]

Any suggestions on how to import the data once I create all the new fields under the design view mode?

I would hardly call myself an Access expert, and I might be frowned upon by others in this forum for suggesting this, because the solution I'm going to suggest is far from ideal...but: Once you have created your table in design mode, you can easily copy and paste the information from Excel. As long as your fields in Access are in the same order as your columns in Excel, it should be fine.

You could well run into problems if the data in your spreadsheet was not kept to strict formating (dates etc), because Access is less forgiving in this regard.

Also, this method may not work well with the lookup fields. One way to get around this is to have tempory fields in your table for pasting the old data, then manually going through and making sure all your lookups are correct.

So, in summary, this method will work but you have to nurse it through. I'm sure there must be a better solution which some other user can put forward.

so to creat an (fk) do I use the lookup wizard under the datatype to define the relationship?)

That's correct. The wizard should help you through.

I might ask you more as I get further along, if you don't mind

That's what the forum is here for!

 

[f_disk]

your worksheet

I removed the two blank excell sheets you had and
also removed the "titles" of your columns and got your
spreadsheet to import fine. I also did not designate the
first column..

If you can get it into your db then you can always add
the headings later...

Hope this helps..

 

[brucesilvers]

Save as Text

I've run into the very same problem many times. One solution is to open the file in Excel and save it as comma or tab delimited text - I've never had problems importing such text files.

Another issue involves excel files downloaded from the web - some web applications actually save them as XML rather than XLS files. The fix is similar - Save As from within Excel and select .XLS as the file type.

 

[gemma-the-husky]

i find that you have far more control importing a csv file than an excel file. eg if you import the file as a csv, you can use an import spec to "lose" some columns and control the format of others.

if you natively import excel the wizard assumes properties of columns I have found numbers that should be doubles importing as integers and dates particularly importing wrongly.