Exporting and reformatting data out of MS Excel

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,开发者_如何学JAVA 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.

You could use VBA to write the data to a new workbook then save as CSV. Something like this:

Sub ConvertRates()

    Dim strCountry As String
    Dim strRateLocality As String
    Dim strCodes As String
    Dim dblRate As Double
    Dim dblWholesale As Double
    Dim vntCodes As Variant
    Dim i As Integer
    Dim lngRow As Long

    Dim rngData As Range
    Dim rngRow As Range
    Dim wks As Worksheet
    Dim wkbkNew As Workbook
    Dim wksNew As Worksheet

    Set wks = ThisWorkbook.Worksheets("Sheet1")
    Set rngData = wks.Range("A2:B3")

    Application.Workbooks.Add
    Set wkbkNew = ActiveWorkbook
    Set wksNew = ActiveSheet

    lngRow = 1

    For Each rngRow In rngData.Rows
        strCountry = wks.Cells(rngRow.Row, 1).Value
        strRateLocality = wks.Cells(rngRow.Row, 2).Value
        strCodes = wks.Cells(rngRow.Row, 3).Value
        dblRate = wks.Cells(rngRow.Row, 4).Value
        dblWholesale = wks.Cells(rngRow.Row, 5).Value
        vntCodes = Split(strCodes, ";")

        For i = 0 To UBound(vntCodes)
            If vntCodes(i) <> "" Then
                wksNew.Cells(lngRow, 1).Value = "'00"
                wksNew.Cells(lngRow, 2).Value = strCountry
                wksNew.Cells(lngRow, 3).Value = vntCodes(i)
                wksNew.Cells(lngRow, 4).Value = strRateLocality
                wksNew.Cells(lngRow, 5).Value = dblRate
                wksNew.Cells(lngRow, 6).Value = 60
                wksNew.Cells(lngRow, 7).Value = dblRate
                wksNew.Cells(lngRow, 8).Value = 10
                lngRow = lngRow + 1
            End If
        Next i

    Next rngRow

End Sub

You will probably need an if statement to determine whether you want to write '00 or '011, and obviously change the code to use the relevant worksheet and range.

I haven't built in a lot of testing, other than the check to make sure that the code is not empty, as you seem to have a semi-colon at the end of your code list, but you may wish to do a bit more checking before writing the data to the new workbook.

I don't see why you wouldn't use file->save as, changing the format to csv and modifying the csv with a script afterwards. Most scripting languages have nice tools for reading in CSV files and modifying them (R, python, etc)