Search code examples
mysqlsqldatabasecsvimport-from-excel

2 Dimensional Excel Table to CSV for SQL Insert

This is probably a dumb question but I'm new to database design and am working to move a convoluted excel spreadsheet into a SQL database.

Here is an excerpt of what I have:

Excel ugliness

And here is what I want to turn that into, creating records with the X,Y coordinates of any cell that contains an R:

Pretty database

The cells I've circled in the first image are the example records I've inserted into the second. There is a large number of these, so I'd like to find the most efficient way instead of manually entering each one. Thanks for any help; it's greatly appreciated!

Solution

  • Here's some quick VBA that should do what you need. 

    Sub flatten()
    Dim rng2Dim As Range
    Dim rngCol As Range
    Dim rngRow As Range
    Dim intRow As Integer
    Dim outPutSheet As Worksheet


    'Set this to the range that has your two dim data.
    Set rng2Dim = Sheets("Sheet1").Range("A1:G6")

    'Set this to the sheet that you will write this data to
    Set outPutSheet = Sheets("Sheet2")

    'This is the first row we will write to in the outputsheet
    intRow = 1

    'Loop through each column in the range
    For Each rngCol In rng2Dim.Columns

        'Loop through each Row in the range (so Column 1, Row 1. Then Row 2. Then Row 3...)
        For Each rngRow In rng2Dim.Rows

                'See if the Row/Column has an "R"
                If rng2Dim.Parent.Cells(rngRow.Row, rngCol.Column).Value = "R" Then

                    'Save the Row and Column values from the sheet and the "R"
                    outPutSheet.Cells(intRow, 1).Value = rngCol.Cells(1, 1).Value
                    outPutSheet.Cells(intRow, 2).Value = rngRow.Cells(1, 1).Value
                    outPutSheet.Cells(intRow, 3).Value = "R"

                    'increment this so we write to the next row when we find another "R"
                    intRow = intRow + 1
                End If
        Next
    Next rngCol
End Sub