Excel import into access

[fllopez65]

Hello
I've had to import some excel spreadsheets into access. The columns in the spreadsheets are very long ie: Total population of females with postsecondary qualifications by major field of study - 20% Sample Data.
Invariably, these titles are being truncated or in some cases access doesn't like the titles at all and are subsutituting its own names such as Field 19, 58 etc...
The spreadsheets have many columns(up to 70) and except for the titles, the cells are numbers.

I think to clean up the spreadsheets data before importing into access, I would think I need to:

1. To fix the titles, remove the % symbol from the titles.

When I import the spreadsheet, I've been using numbers as opposed to text and I would like to avoid having to change every column when importing.

Are there any other suggestions to clean up the spreadsheet to make sure I could have a clean import into access.

Tks
fllopez65

 

[apr pillai]

You need to standardize the titles to get a clean import with proper field names. Create a 70 Column Title, appropriate for the data columns, somewhere on a separate worksheet. Copy paste it over the existing titles before attempting to import into access.

This way you need to create the titles manually only once, copy paste many times.

Or

Create a separate sheet with standardized headings. Link this Sheet directly into Access database as a table.

Copy paste data from other worksheets under the titles on the linked sheet.
You can create an Append Query on the linked table as input and point the output to the target table.
This way as soon as you introduce fresh data on the linked sheet, underneath the titles, you can run the append query to move the data from worksheet to the target table.
The copy paste action must be repeated for each set of data separately.

OR

You may copy paste all input worksheet's data one set below the other on the linked worksheet and run the append query once to take all the data together in one step.

 

[fllopez65]

tks apr pillai,will try your suggestions.