Solved

Convert Excel data to a flat file

Posted on 2014-02-24
10
479 Views
Last Modified: 2014-11-12
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
0
Comment
Question by:OnsiteSupport
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39883858
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.
0
 

Author Comment

by:OnsiteSupport
ID: 39883871
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.
0
 
LVL 53

Expert Comment

by:Bill Prew
ID: 39884049
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
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39884051
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

0
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
ID: 39884077
:)

Patrick just beat me to a working code sample, but here is mine anyway...

Option Explicit
Public Sub Q_28373141()

  Dim objCell                                           As Range
  Dim objColumn                                         As Range
  Dim objCreateTextFile                                 As Object
  Dim objScripting_FileSystemObject                     As Object
  Dim strFilename                                       As String
  
  Worksheets("Q_28373141").Select
  
  strFilename = Environ("USERPROFILE") & "\Desktop\Q_28373141.csv"
  
  Set objScripting_FileSystemObject = CreateObject("Scripting.FileSystemObject")
  Set objCreateTextFile = objScripting_FileSystemObject.CreateTextFile(strFilename, 2&, True)
  
  For Each objCell In Range([A2], Cells(Cells.Rows.Count, 1).End(xlUp))
  
      For Each objColumn In Range(objCell.Offset(, 1), Cells(objCell.Row, Cells.Columns.Count).End(xlToLeft))
      
          objCreateTextFile.WriteLine objCell.Text & "," & Chr$(34) & Cells(1&, objColumn.Column).Text & Chr$(34) & "," & Chr$(34) & objColumn.Text & Chr$(34)
          
      Next objColumn
      
  Next objCell

  objCreateTextFile.Close
  
  Set objColumn = Nothing
  Set objCell = Nothing
  Set objCreateTextFile = Nothing
  Set objScripting_FileSystemObject = Nothing
  
  If Len(Trim$(Dir$(strFilename, vbNormal))) > 0 Then
     Call Shell("notepad " & strFilename, vbNormalFocus)
  End If ' If Len(Trim$(Dir$(strFilename, vbNormal))) > 0 Then
  
End Sub

Open in new window


This code is within the "basQ_28373141" code module of the attached workbook.

Note that Patrick's code allows you to select the output file, my code simply writes to the file "Q_28373141.csv" of the local user's desktop.

BFN,

fp.
Q-28373141.xls
0
 

Author Comment

by:OnsiteSupport
ID: 39884083
yes, but your code is structured and Patrick's isn't LOL
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39884157
Unstructured how?  My code and Nigel's are nearly identical...
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39884801
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.
0
 

Author Comment

by:OnsiteSupport
ID: 39885251
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
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39885263
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.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question