Importing Excel fiel to Acess Table Opinion

[Dina01]

Hello,

I am not too sure were to start with this...Here is the situation...

I have different carriers that e-mail there new excel spreadsheet every 2 weeks, with new prices....But every carried has there one format...per example

Carries Bell might look like this

Country Code Price

Canada 514 0.1231
USA 305 0.458

And AT&T

Code Country Price

514 Canada 0.1231
305 USA 0.458

I need to be able to import these sheets into acces tables, but I want to customize the data.....Meaning maybe putting them all under

Country Code Price....

And is there a way to customize the import so instead of always manually importing every column and putting a check mark if I want to import the columns or not....Just create code that will automatically always keep the same format...

I have never done something like this andd I have no idea were to start ......Does anyone have a clue, or maybe an example....

Thanks

 

[Pat Hartman]

If all the spreadsheets have the same columns with the same column headings (even though they are in a different order), you can append the spreadsheets directly to the same table. If the spreadsheets don't have column names or the column names are inconsistant, you can link to each spreadsheet and use an append query to copy the data from each differently formatted spreadsheet into a commonly formatted table.

I think that since the spreadsheets don't have a column that identifies their source, you will have to go with plan B because you'll need the source id in your Access table.

 

[The_Doc_Man]

Pat is right. But there is one more factor to consider as to using an Append query...

If you have separate queries for separate carriers, you can include a "constant" code in a query column that will fill in a field in your table with the name or abbreviation for that carrier. So when you are finished, you not only know the rates, but who has those rates.