CSVs in a comma-free land and other woes

ChrisM666

Registered Idiot
I am currently using files with extension .cdb - which are just .csvs with the usual(?) commas and some added vendor-specific rows. I want to open them in Excel (2008) as though they are .csvs. Ideally, they would open via 'File>Open' - or at least on being selected in Finder - with each item falling into the correct column, there to await my formatting macro.

This doesn't happen because:
  • 'Finder>Open (With)' does not invoke the formatting Wizard for unknown file extensions and dumps all rows in column A. Excel File>Open does invoke it but defaults to highlighting 'Tab' as the delimiter. What happened to comma separated variables?
  • Worse, in Europe where I am, the default delimiter is a semicolon and this is set by var Application.International (xlListseparator) to be so (a comma in the US).
  • If I rename all such files as .csv the opening would still fail as Excel expects semicolons...
I need to write a script to rename a file on being opened - maybe via a context menu option(?) - to csv and then rewrite all commas as semicolons. Mercifully, the files have a definite row/column limit so time would not be wasted looping through thousands of empty rows! Could Applescript handle this?

I have a macro to do the final formatting - could this code be incorporated in or called by the initial script?
 
Top