Excel标题作为单元格数据

Question

有人给了我一张Excel表格，里面有几千个条目要导入SQL数据库。

问题是它不是一个列表，它可以很容易地导入。

显示如下:

GROUP NAME
itemName Price Order

如

Desserts
Chocolate Cake 8.5 1
Butter Cookies 3.0 2
Refreshments
Coke 1.5 1
Water 1.0 2

我需要将上面的转换为

Chocolate Cake 8.5 1 Desserts
Butter Cookies 3.0 2 Desserts
Coke 1.5 1 Refreshments
Water 1.0 2 Refreshments

获取标题下所属组的所有行

有办法吗?

我可以很好地使用notepad++和SQL

谢谢

Answer 1

我不确定这是否能满足您的需求，但它应该能让您大致了解情况。这是一个VBA子例程，它将循环遍历您的数据并以您需要的格式将其写入另一个工作表。

Sub transformNastyData()
    Dim transformRange as range 
    Dim readRow as range
    Dim writeRow as range
    Dim groupHeader as string
    'change these to suit your needs
    Set transformRange = Sheets("Sheet1").Range("A1:C20000")
    'the row we'll start writing to in sheetOut
    set writeRow = Sheets("Sheet2").Rows(1)

    'loop through each row
    For each readRow in transformRange.Rows
        'check to see if the value in column B is blank (assuming this is how to identify a group header?)
        If readRow.cells(1, 2).value = "" Then
            'capture the group header
            groupHeader = readRow.cells(1, 1).value
        Else
            'we have a good record, so just write it out.
            writeRow.cells(1, 1).value = readRow.cells(1, 1).value
            writeRow.cells(1, 2).value = readRow.cells(1, 2).value
            writeRow.cells(1, 3).value = readRow.cells(1, 3).value
            writeRow.cells(1, 4).value = groupHeader
            'increment the writeRow
            set writeRow = writeRow.offset(1)
        End if      
    Next readRow
End Sub