Excel import to Access problem (1 Viewer)

[LB79]

Hi all,

I have an import problem that’s driving me nuts.
I want to import an Excel file. The table contains header names in row 1.
When I import to excel, and I check that I want to use the header names, Access adds a phantom column (Field 13).
I've tried deleting columns that are blank, but this doesn’t solve it.
Does anyone know why this is happening?

Thanks

 

[dbDamo]

Do you get this message when you perform the import?

"The first row contains some data that can't be used for valid Access field names. In these cases, the wizard will automatically assign valid field names."

If you do then the reason your column is being named 'Field13' is because the field name in your Spreadsheet cannot be used in Access.

If you're not getting this message and your Spreadsheet field name is fine then I cannot help you!

 

[gemma-the-husky]

excel sometimes stores columns and rows wider than the visible area - i think if you enter a row/column, and delete it, excel still records the max row/max column, and when you load it into excel, you load everything, including these phantoms.

it shouldnt be a problem - just use the columns you need

the best way is to import to a new table, and then append new data to your exisitng tables - in this way you just ignore the F13's etc.

I actually check all the column headers to test the imported spreadsheet - and make sure they imported the correct spreadsheet - you cant trust users you know!

-------------

out of interest, i think you are actually better importing a csv, as you get more control, and often the csv is raw data from another system - and it is safer to use the untouched data, rather than after users have interfered with it in a spreadsheet

 

[ghudson]

Press Ctrl + End in the open Excel file to find the last cell. Does it take you to an empty column? If so, delete it and then resave the file. Then try the Ctrl + End keys again and it should take you to the last cell with a value.

 

[mayanktikku]

Do you get this message when you perform the import?

"The first row contains some data that can't be used for valid Access field names. In these cases, the wizard will automatically assign valid field names."

If you do then the reason your column is being named 'Field13' is because the field name in your Spreadsheet cannot be used in Access.

If you're not getting this message and your Spreadsheet field name is fine then I cannot help you!

Its been two days, I have been struggling with same problem, what I did was, I copied Excel file content into a new excel file and pasted as Values.
This eliminated the error and got imported flawlessly in to Access. Try this way as well.

 

[The_Doc_Man]

@mayanktikku - thanks for your contribution, but if you checked the dates at the top line of each post, you might note that this was a fourteen-year-old thread. Your answer STILL may be useful to others, so - seriously - thanks for your input. But just so you will become more accustomed to our forum, check the age of a post on the top line of the post's frame.

 

[Pat Hartman]

Since we have revisited this, I would also add that just because a column or row is "empty" doesn't mean that it doesn't exist. Most people just select the column or row and press the delete key. The visible values disappear and so to them the row/column is gone. However, as @gemma-the-husky pointed out, Excel has a long memory. Therefore, you need to use the correct method to actually delete the column/row. That is to select the column/row, right-click on the selection and choose the delete option. That way Excel knows you actually want to delete the selection, not just make it "empty".