ansaurus

Question

Exporting and reformatting data out of MS Excel

Answer 1

A:

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)

Lonnen 2010-05-18 03:48:35

That was how I was going to do it, but it turned out that dendarii's answer worked almost out of the box and was quick to implement.

Matt H 2010-05-19 06:29:16

Answer 2

+1 A:

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.

2010-05-18 10:43:14

Thanks, with only a little bit of tweaking that did the trick.

Matt H 2010-05-19 01:27:00

ansaurus

tags:

views:

answers:

Exporting and reformatting data out of MS Excel

related questions