Importing data (beginners guide)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I have several excel spreadsheets, completed by the business (they are
not in the same format) I need to be able to import (as automatically as
possible) all these sheets into one main table to use in the database, I also
need the data to overwrite the existing data, so for record 0001, if someone
updates that on the excel sheet, when I import the data record 0001 is
overwritten with the new info, its not added as a new record in the table.

Would I be best doing this with temp tables, what are my options with
regards to automating it, would I need our systems team to run a scheduled
job or an administrator to click a macro button.

Sorry for the vagueness of my query, I am after some direction that I can
investogate further.

many thanks

Matt
 
When you say the spreadsheets are not in the same format, what do you mean?
To import spreadsheets without a lot of automation code, they need to be
columnar data. It is also best if they have meaningful column names in the
first row. There should be no blank rows within the data range. It is
easiest if the columns match up with the fields in the Access table. This is
if the Access table's first field is CustomerID, then the first column in the
spreadsheet should be the CustomerID. This is not an absolute requirement,
but it makes life easier.

If you are saying they are not in the same format as the Access table, we
can deal with that. If you are saying the spreadsheet A and spreadsheet B
have different columns with different data but need to go into the same
table, we can deal with that, too, but it takes a bit more.

Based on your description, one way to do it would be to link to the
spreadsheet as if it were a table. You can then use either Update or Append
queries to move the data into Access based on the business rules.

If you can be a little more specific, we can see what the options are.
 
Hi, the spreadsheets are columnar, but not the same columns as the DB tables,
so i would only want to import specific columns from different tables, can
this be automated.
 
Back
Top