Importing from Excel to Access 1

[SaintAugustine]

Ugh.

I'm trying to import a file from Excel into Access, since the file has just gotten too darn big for Excel. The problem is, the importing isn't going well. I get an error message at the end, and it turns out that either 1) nothing has been imported or 2) most of the rows have been imported, but some were mysteriously left out.

Possible culprits:
1) The columns in Excel are a mix of data types - specifically, dates sometimes have notes to myself that are text instead of dates.
2) I have blank cells

Also, for some reason, when I'm going through the "import data" dialog windows, I'm *unable* to access the drop-down menu that allows me to pick how the column will be formatted in Access. I think I could fix a lot of problems if I could do this (ie, change the "date" column formatting from "Date/Time" to "text..."

Any help?

Thanks

 

[chifu]

The problem should be point 1.

When importing into a database, all the data in each column should be of the same data type.

The data in the first row (or second if you have a header row) determines the data type of that field. So if cell A1 is a date, then Access will expect all data in column A to be dates. If any A cells are not dates Access will show an import error. It is possible to force the import by making sure the first row contains general text in all columns, but this is not very useful if you want dates to be dates.

So you will need to clean up the speadsheet before you can import it into Access.

 

[SaintAugustine]

Thanks Chifu - time to get out the Windex and work on cleaning my data, I guess.

 

[SaintAugustine]

Also - how does the import command treat blank cells? As long as I have the "required" field set to "no" in the Design view, I should be ok, right?

 

[lbpsfl]

yes, but be careful. Only one of 'Is Null (blank_col)' and 'blank_col = ""' will be true. I think it's the second one.