Help importing certain columns of data from Excel to Access

  • Thread starter Thread starter Tommy
  • Start date Start date
T

Tommy

I have two excel sheets containing data (downloaded as a .csv). One is
called 'PHD' and the other is 'XANS'. The data in each sheet has
different column headings. The data changes daily, and the size is
always the same number of columns, but a variable number of rows.

Within Access, I have defined 10 fields where the values are common to
both sets of data and I would like to be able to automatically export
certain columns/ranges e.g. C4 to last non-empty cell in C in 'PHD'
data sheet into one of the fields in the Access database.

Typically, I will be fetching data from both the PHD and XANS sheets
and there will be a column of data in each sheet that will have to go
into the same field in Access.

How do I go about doing this?

Thanks,

Tom
 
Tom,

There's no obvious reason to bring Excel into this: Access can import
or link CSV files at least as easily as it can data on Excel sheets.

Basically the way to go is to create one linked table connected to PHD
and another connected to XANS. Then use update and/or append queries
to move the data; join each linked table to your Access table on the
common fields.

(It's not quite clear what your're doing with the data. If the CSV
files are updated daily, why not use linked tables above, but replace
the Access table with a single select query that joins the two tables
on the common fields?)
 
Thanks John, a little more information to clarify:

The CSV files are emailed to me daily, as such I am not aware of their
location, although I could always save them to somewhere. The problem
with the CSV sheets is that the PHD and XANS data in them is
different, i.e. in different columns e.g. one column is a value
showing a 'tagname' e.g. 06TI394.PV . In PHD, this is column N, while
in XANS it is column O. Also, both have different heading names in
each sheet. I need the data to be stored in Access because I have a
lot of queries that I need to run to compare data between PHD and XANS
and I need the CSV data to be merged together as they show the same
thing. I am basically querying discrepancies between PHD and XANS
because some data that is appearing in PHD is not in XANS when it
should be, and I need to verify which data is consistent in both.

Eventually, I will like to call my results from queries into excel as
they will be used in some calculations.
 
Back
Top