2 Dimensional Excel Table to CSV for SQL Insert

Question

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!

Which SQL database are you using? Databases such as MySQL have functions to import CSV data directly. So you shouldn't have to do anything fancy. See, for example, [this question](http://stackoverflow.com/questions/3635166/how-to-import-csv-file-to-mysql-table) and the [LOAD DATA INFILE documentation](http://dev.mysql.com/doc/refman/5.1/en/load-data.html) — lurker, Mar 05 '15 at 20:06
I'm using MySQL in Phpmyadmin; I do already know how to import CSVs, it's turning the data from img1 into the format in img2 so I can then CSV export and import that is stumping me - I don't know the most efficient way to do this. Shown in img1 is only a snippet of a very large table, and I want to avoid transforming the data manually, I just don't know how to do this. Thanks — Oggy, Mar 05 '15 at 20:12
OK. I can't read your images since our firewall blocks those sites. — lurker, Mar 05 '15 at 20:16
If you are purely using SQL to format the data then export it back to a csv then you could possibly take a different approach. You could record/write a macro in excel to handle this logic which may be easier. — Jared, Mar 05 '15 at 20:17

score 0 · Accepted Answer · answered Mar 05 '15 at 20:25

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

Going to try this now, first time using VBA so we'll see how this goes. Thanks for the response — Oggy, Mar 05 '15 at 20:39

2 Dimensional Excel Table to CSV for SQL Insert

1 Answers1