I have a large Excel spreadsheet that has telephone calling rates for many different countries.
The format of the column: Country, Drlokyta, prefix, rate, bulk
eg Afghanistan, 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 are changed every time and they need to be brought to another system which they can import using CSV.
The final format is: <00 p>
LD prejudic code i.e. 00 or 011, country code, area code, comment, connect cost, second seconds, cost per minute, precyclix, increase
so 00 I have to change the line up, "Afganstan", 93, "default", Lk023,60,lk023,l0 00, "Afghanistan", 931, "default", 1.023,60, 1.023,10 ... 00, "Afghanistan", 9 3 9, "default", 1.023,60,1.023,
Where 00, coded 60 and 10 hard and other data from Excel with Ily is.
How can I export this data in the required format, which I need to reformat it as it goes.
Should I export to XML and use XSLT or some other process to massage data in CSV? If this is the case, how can I do this easily and quickly?
You can use VBA to write data for a new workbook then in the form of CSV saves some such:
sub ConvertRates () dim strCountry string as Double dim dblWholesale double dim vntCodes as dim as the string strRateLocality dim strCodes string dim dblRate As the version dim, I dimension the Range Data as the dim RNGRow until the integer dim lngRow range in the RangeRow Workbook dim wksNew form worksheet set as WKBKNew = ThisWorkbook.Worksheets ("Sheet1") set as 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, ",") = 0 for I VntCodes if vntCo des (I) & lt; & Gt; "" 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 = Strength losality wksNew.Cells (LNGRO, 5). Value = dBLRETEt wksNew.Cells (LNGRO, 6) .Value = 60 wksNew.Cells (LNGRO, 7). Value = dBLTRT wksNew.Cells (lngRow, 8). Value = 10 LNGRO = LNGRO + 1 and If Next I Next RNGRO Over Sub You probably will need a statement whether you want to write '00 or '011, and clearly Changing the code to use relevant worksheets and categories
I have not created too many tests, to ensure that the check code is not empty except as you think your code One at the end of the list There is a hypochondrium, but you may wish to check a bit more before writing data in a new workbook.
Comments
Post a Comment