Create Specific CSV output from Formatted Excel Tab

Question

I have a spreadsheet that has a tab that is formatted like below:

|Item Number|Description|Unit of Measure|4/29/2016|4/30/2016|5/1/2016|etc..
|-----------|-----------|---------------|---------|---------|--------|
|          1|item #1    |units          |         |       10|        |
|          2|item #2    |units          |         |         |     540|
|          3|item #3    |units          |       45|       28|        |
|          4|item #4    |units          |      133|         |        |

I need to create a .CSV file from this file in this format:

1,10,30042016
2,540,01052016
3,45,29042016
3,28,30042016
4,133,29042016

The excel sheet can have date columns that go years in the future. The output takes Column A (Item Number) and creates as many lines as it has days with values, it will also tie a date (from the column) to those values. The output will be formatted as:

Item Number,Quantity, Date (DDMMYYYY format)

Any help on creating the CSV output would be really appreciated.

Answer 1

I think you'll need VBA for this to transform columns to rows. A Pivot Table will not create the desired format, although it might actually be possible to transform this excessively using formulas.

The VBA approach, on the other hand, can provide you with a clean solution.

The following will be necessary:

1. Loop over each row.
2. In each row, loop over all cells in the date columns.
3. For each non-empty cell found this way, create an output line with the value of the first column concatenated with the current cell's value and the column header, converted to the desired date representation.
4. Write every output line to a file.

This sample code shows one possible implementation. You have to paste this into a standard code module in the VBA editor and add a reference to the Microsoft Scripting Runtime library (add it via Tools --> References).
Assuming the 6-column layout in your question begins in cell A1, you can call the code by entering ExportToCSV ActiveSheet.Range("A1:F5"), 1, 4, "D:\output.csv" in the Immediate Window (open with CTRL+G). Adapt the parameters as necessary.

' exports the range rngTab to the file strFilename.
' lngColumnID defines the column in rngTab that contains the ID for each output line.
' lngColumnFirstDate defines the first date column in rngTab, assuming that all following columns are date columns as well.
Public Sub ExportToCSV(ByRef rngTab As Range, ByVal lngColumnID As Long, ByVal lngColumnFirstDate As Long, ByVal strFilename As String)
    Dim fso As Scripting.FileSystemObject
    Dim ts As Scripting.TextStream
    Dim rngHeader As Range
    Dim rngRow As Range
    Dim rngCell As Range
    Dim lngCurrentColumnInTab As Long

    ' open the output file
    Set fso = New FileSystemObject
    Set ts = fso.CreateTextFile(strFilename, False) ' toggle the second parameter to overwrite an existing file without warning

    ' define the first row as the header row
    Set rngHeader = rngTab.Rows(1)

    ' loop over all data rows beginning with the second one, assuming the first contains the header
    For Each rngRow In rngTab.Resize(rngTab.Rows.Count - 1).Offset(1).Rows

        ' loop over all cells in the current row beginning in column lngColumnFirstDate.
        ' lngCurrentColumnInTab is a counter that keeps track of the current column so that the appropriate header value can be retrieved.
        lngCurrentColumnInTab = lngColumnFirstDate
        For Each rngCell In rngRow.Resize(, rngRow.Columns.Count - lngColumnFirstDate + 1).Offset(, lngColumnFirstDate - 1).Columns

            ' if a non-empty cell was found, create the output line by concatenating (comma separated) the following:
            '   * the ID contained in the cell in column lngColumnID
            '   * the current cell's content
            '   * the date in the current cell's header row; it is first converted to a date and then formatted for the desired output
            ' the constructed output line is then written to the file opened as text stream ts.
            If rngCell <> "" Then ts.WriteLine rngRow.Cells(, lngColumnID) _
                                       & "," & rngCell _
                                       & "," & Format(ConvertToDate(rngHeader.Cells(, lngCurrentColumnInTab)), "ddmmyyyy")
            lngCurrentColumnInTab = lngCurrentColumnInTab + 1

        Next rngCell

    Next rngRow

    ' everything was written, close the file
    ts.Close
End Sub


' helper function to convert a string in the format m/d/y to a date
Private Function ConvertToDate(ByVal strDate As String) As Date
    ConvertToDate = DateSerial(Split(strDate, "/")(2), Split(strDate, "/")(0), Split(strDate, "/")(1))
End Function

I hope the comments make it clear what the respective code fragments do.

This code is not fail-proof if you choose inconsistent parameters. If you want you can expand the code to catch all invalid parameter constellations and provide verbose error messages.