Trying to merge 500 files

[kenw]

Version: 2008
Operating System: Mac OS X 10.4 (Tiger)
Processor: intel

Hi, Guys

Need some help.

We have 500 customer records. Each is a separate xls file. The fields are either labels, eg Name, Address etc, and either text or number content.

The problem is that they are not set up as data files, laid out like a paper form, with centred / merged cells. Basically they have used the PC as a typewriter to fill in the form. [I'm on the Mac, the forms were created using Office04 Win].

Now I have to get all those data out of the files and load into a new database, which wants CSV or similar structured data - and does NOT want any formatting.

Any suggestions? Please?

Ken

I now need to combine all 500

 

[Peter]

AppleScript could open all the files, enter all the data into one master
spreadsheet and then export it in .csv format. It's debatable whether that
would be any quicker than doing it all manually if your not proficient in
AppleScript though.

 

[CyberTaz]

I hate to be a wet blanket here, Ken, but I don't think you're going to be
able to automate the process :-( The fact that there are merged cells
involved in going to make it quite dicey. The use of merged cells also
suggests that more than one data item has been included in them. If so, it
makes it all the more problematic to create any kind of script that
accommodates the variable data content of those cells - once it finds them.

You can probably avoid a lot of retyping by copying the data, but use Paste
Special> Values in order to prevent pasting the cell attributes.

Regards |:>)
Bob Jones
[MVP] Office:Mac