asked on

Convert Excel data to a flat file

I have an excel sheet that I want to flatten out into a CVS so that I can import into Google calendar.

The purpose of this spreadsheet is to take a class roster and assign individuals tasks. In order to bring it into Google Calendar, it has to be flattened out.

Here is the format of the sheet.

Date Topic 1 Topic 2 Topic 3 Topic 4
3/1/14 John Ed Bob Jim
3/2/14 Steve Joe Mary Larry

Results should look like this:

3/1/14 Topic 1 John
3/1/14 Topic 2 Ed
3/1/14 Topic 3 Bob
3/1/14 Topic 4 Jim
3/2/14 Topic 1 Steve
3/2/14 Topic 2 Joe
3/2/14 Topic 3 Mary
3/2/14 Topic 4 Larry

Hope this is enough information to help
'
Thanks
Craig

[ fanpages ]

Hi,

I have an excel sheet that I want to flatten out into a CVS...

Do you mean a CSV (Comma-Separated Values) file?

That is, "results" such as...

3/1/14,"Topic 1","John"
3/1/14,"Topic 2","Ed"
3/1/14,"Topic 3","Bob"
3/1/14,"Topic 4","Jim"
3/2/14,"Topic 1","Steve"
3/2/14,"Topic 2","Joe"
3/2/14,"Topic 3","Mary"
3/2/14,"Topic 4","Larry"

Thanks for your clarification.

BFN,

fp.

OnsiteSupport

ASKER

Yes. Funny when I typed it didnt look quite right and I stayed with the drugstore instead of CSV.

Your results are what I'm assuming for.

Bill Prew

If this is a one time exercise you don't need a script to do this, it can be done right in Excel, then save the result as a CSV format file:

http://office.microsoft.com/en-us/excel-help/unpivot-columns-HA104053356.aspx

~bp

Patrick Matthews

The following appears to be working for me:

Sub MakeCSV()
    
    Dim NewFilePath As Variant
    Dim LastR As Long, LastC As Long
    Dim arr As Variant
    Dim RowCount As Long, ColCount As Long
    Dim fso As Object
    Dim ts As Object
    
    NewFilePath = Application.GetSaveAsFilename(, "CSV Files (*.csv), *.csv", , "Save CSV file as...")
    If NewFilePath = False Then
        MsgBox "No file, I quit!", vbCritical, "Aborting"
        Exit Sub
    End If
    
    With ActiveSheet
        LastR = .Cells(.Rows.Count, 1).End(xlUp).Row
        LastC = .Cells(1, .Columns.Count).End(xlToLeft).Column
        arr = .Range("a1").Resize(LastR, LastC)
    End With
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.CreateTextFile(NewFilePath, True)
    
    For RowCount = 2 To LastR
        For ColCount = 2 To LastC
            If arr(RowCount, ColCount) <> "" Then
                ts.WriteLine Format(arr(RowCount, 1), "m/d/yy") & "," & """" & arr(1, ColCount) & """,""" & arr(RowCount, ColCount) & """"
            End If
        Next
    Next
    
    ts.Close
    Set ts = Nothing
    Set fso = Nothing
    
    MsgBox "Done"
    
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

[ fanpages ]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

OnsiteSupport

ASKER

yes, but your code is structured and Patrick's isn't LOL

Patrick Matthews

Unstructured how? My code and Nigel's are nearly identical...

[ fanpages ]

yes, but your code is structured and Patrick's isn't LOL

That is an odd comment.

I know Patrick will not be interested in the allocation of points but, to be fair, the recognition that either proposed solution addresses your requirements should be made.

OnsiteSupport

ASKER

I was referring to "Fanpages" use of Tabs in your code. I didn't intend to minimize either of your expertise in VB or the time that you spent writing it. This was more a product of my annoyance in having to work on this project at all :)

However, after running both solutions, I prefer Patrick's code because it applies to the current document. versus a cut and paste of data into your attached example. As a side,

I would be interested to find out what would cause Fanpages to code to work on any sheet. I'm guessing it's because you've hardcoded the spreadsheet name?

Worksheets("Q_28373141").Select

[ fanpages ]

Yes, that's correct. Simply remove that single code statement & the code will run on the active (selected) worksheet in the active (selected) workbook.

The code I supplied was not meant to be written for your own requirements; simply to demonstrate how the extraction to a CSV format could be achieved.

In future, if you require code specifically written to fit your own workbook, I would suggest providing this file within your question.