Exporting and reformatting data out of MS Excel
- by Matt H
I have a huge Excel spreadsheet containing telephone calling rates to a number of different countries.
The format of the columns is:
Country, RateLocality, Prefixes, Rate, Wholesale
e.g.
Afganistan, Default, 93;930;931;9321;9322;9323;9324;9325;9326;9327;9328;9329;9331;9332;9333;9334;9335;9336;9337;9338;9339;9341;9342;9343;9344;9345;9346;9347;9348;9349;9351;9352;9353;9354;9355;9356;9357;9358;9359;9361;9362;9363;9364;9365;9366;9367;9368;9369;9371;9372;9373;9374;9376;938;939;
$ 1.023, $0.455
These rates change every so often and I need to get them into another system that can import them using CSV.
The eventual format is:
LD PREPEND CODE ie. 00 or 011,CountryCode,Area Code,Comment,Connect Cost,Included Seconds,Per Minute Cost,Pricelist,Increment
So to convert that above line I'd have
00,"Afganistan",93,"Default",1.023,60,1.023,10
00,"Afganistan",931,"Default",1.023,60,1.023,10
...
00,"Afganistan",939,"Default",1.023,60,1.023,10
Where 00, 60 and 10 are hard coded and merged with the other data from excel.
How can I export this data into the required format given that I need to reformat it as it goes.
Should I export to XML and use XSLT or some other process to massage the data into CSV?
If that is the case, how do I do it simply and quickly.
