Importing into one table from multiple sources

[Guest]

Hi Guys,

I have a number of suppliers who send me out data in excel files. There are
some fields that are common to all the excel files, and some fields that
exist in only one file.

I want to import the data easily into a table in my database. I tried
several times with some test data but i got varying results with little
success.

I can import the data into a new table ok, but cant import the data into an
existing table, I get subscript out of range, and even trying to import a
small sample of the data causes the same error.

Whats the best way to handle this situaiton?

Thanks in advance,

-Al

 

[Aaron Kempf]

I'd pick up a book on SQL Server DTS or SSIS

Access isn't an integration tool

sorry

 

[Guest]

One of the reasons that it may not import into an existing table is Access
may think that you have extra fields in the spreadsheet.

First thing to do is make sure that ALL of the fields in the spreadsheet are
in your access table (you can have more in Access, but you cannot have less).
Second thing would be to delete all blank columns starting to the right of
the last one with data in it. Do not clear, make sure that you delete.
Sometimes Access thinks that there is data in those blank columns and gives
the columns names like field1, field2, etc, which are not in the table, so
the import fails.

Please let me know if I can provide more assistance.

 

[Guest]

Since all the excel files need to go into one table, you have to use at least
a two step process. First, import the excel sheet into an intermediate table
used for the import and second, use an append query to append the imported
records into the final table.

Since different suppliers have different columns and different names, there
will not be a "one size fits all" solution. The easiest may be to have
different append queries for each different excel file layout.