Importing Excel chart into Access

[brumy]

I have an Access table that has roughly 20 fields. I would like to import data that I have for about 5 of the fields from an Excel chart. Can I append the data into the database if the Excel columns do not exactly match the field headings in Access? Also ... if the "name" column in the Excel combines first, middle, last, but is separated by commas, can I cause that data to be split into three fields when imported into Access?

Thanks for any help -- Darren

 

[tunsarod]

In general Access expects the imported data columns to be in the correct order that matches the intended fields. If your five source data columns are not intended for the first five columns or fields in an Access table then you must pad the source data with empty columns, by adding extra commas if the data is contained in a CVS or TXT file or in the case of an XLS file by adding extra columns to the excel table, which can then be hidden. Adding empty columns directly into the data set will disrupt the accuracy of the graph display so set up a replication of the data set on a separate area of the spreadsheet where extra columns can be added without disturbing the graphing data.

If you intend to repeat the import frequently then you could, indeed should, create an intermediate table with five fields and create an import specification to import the data into that table. Then create an Append query in Access to copies the data from the intermediate table to the appropriate target table fields and finally a Delete query that deletes the records from the intermediate table. The import specification, the append query and the delete query can all be called from a single Macro anytime you want to get new data - without making any alterations to the Excel source data.

Alternatively you can simply create a linked table in Access, specifying the Excel file that containes the source data as the linked file. You will be able to copy the data directly from the Excel table into your target table using Access queries.

If your source data is formatted in comma delimited file format then a field that contains text interspersed with commas will inevitablly be split into separate fields on import into Access. However when importing from an XLS file the commas have no significance and will not be seen as field separators.