Importing awkwardly set up Excel data

[ejgallagher]

I have 250 excel spreadsheets that i want to put into ACCESS.

Each sheet represents the financials of a different location.

Each sheet is formatted the same.

Here are the problems:

1. The data is formatted inversely - category headers are on the side - going down the page and the time data is pulled (months) goes across the top. How can I import this?

2. There are ilnes added in to separate category groups -this is done for aesthetics. Will this screw me up?

Help is greatly appreciated!

Thanks,

Ed

 

[BrianB]

I do a lot of this sort of thing. Found the best method is to set up an Access macro that uses TransferText and import the the files into an Access table ensuring that everything is TEXT. Then use a macro to convert to another table formatting cells as number or date.

This might seem longwinded but it solves all sorts of problems, not the least of which is Access "doing its own thing". Also, the raw text file is still untouched for checking if necessary. I routinely hand tables of 350,000 records this way, and it is still reasonably fast (on my hard drive, not server).

Regarding your strange file setup, hopfully you will be able to Copy/Paste Special/Transpose before import ?