One of those simple but indispensable tricks

Back to a “real” post now after all the book stuff, but it’s going to be a short one while I get back on my feet.

Let’s say you have multiple worksheets (or workbooks) that all contain the same sort of data:

image  image  image

Multiple Worksheets (or Workbooks), All Contain The Same Type of Data

You Want to Combine ALL of Them Into a Single PowerPivot Table

These worksheets all come to you separately, but really you just want them as one big table.

Naturally, if it’s a small number of sheets, and each sheet isn’t massive, you can just copy paste them all into one table in Excel, then copy/paste into PowerPivot, or link the table into PowerPivot, or export as CSV so you can import it.

And you could also use Paste Append to directly paste into PowerPivot.

But if the combined data set exceeds 1 million rows, you won’t be able to combine the sheets into one – you will exceed the worksheet row limit.  And a data set of that size is not something you can paste into PowerPivot directly with Paste Append – pasting large data sets into PowerPivot takes forever, if it completes at all.

Here’s what I do when I find myself in this position:

1) Remove the Header Row

image

Delete the Header Row – We Don’t Want The Header
Duplicated Multiple Times in the Combined Data Set

My goal is to have the data start in cell A1:

image

Important that the Data Starts in A1

2) Save each sheet as CSV.

When you save as CSV, it saves only the active sheet, so you have to repeat this for each sheet (or each workbook).

This yields, for instance:

image

Now you Have a Bunch of Individual CSV Files

3) Open a Command Prompt

image

Start –> All Programs –> Accessories

Yielding:

image

Yep, That’s a Cmd Prompt

4) Change Directory to the Folder Containing Your CSV Files

image

This Isn’t a Cmd Prompt Tutorial, but the CD Command Changes Folder

5) Copy Them All Into One File

image

Copy *.csv combined.csv

This part is really the only “trick” in the whole process.

This command merges all of your CSV files into a single CSV file.  If I look back in Windows Explorer now:

image

 

6) Now Import that CSV into PowerPivot

image

Last Step

Bonus:  Macro to Save All Workbooks in a Folder as CSV

If you have a bunch of workbooks in a folder and you need to save them all as CSV, here’s a macro that will do it all for you:

Sub ProcessWorkbooksInFolder()

    Dim sPath As String
    Dim sFile As String
    Dim sDir As String
    Dim oWB As Workbook
   
    ‘***Set this to your folder of workbooks***
    sPath = “C:\Users\rob\desktop\csvfiles”
   
    If Right$(sPath, 1) <> “\” Then sPath = sPath & “\”
    sDir = Dir$(sPath & “*.xlsx”, vbNormal)
   
    Do Until LenB(sDir) = 0
        Set oWB = Workbooks.Open(sPath & sDir)

        SaveAsCSV       
       
        oWB.Close False
        sDir = Dir$
    Loop

End Sub

Sub SaveAsCSV()
   
    Dim sNewPath As String
    sNewPath = GetFileName(ActiveWorkbook.FullName)
    sNewPath = sNewPath & “.csv”
    ActiveWorkbook.SaveAs Filename:=sNewPath, FileFormat:=xlCSV, CreateBackup:=False

End Sub

Function GetFileName(sFullPath As String) As String
    GetFileName = Replace(sFullPath, “.xlsx”, “”, 1, -1, vbTextCompare)
    GetFileName = Replace(sFullPath, “.xlsb”, “”, 1, -1, vbTextCompare)
    GetFileName = Replace(sFullPath, “.xlsm”, “”, 1, -1, vbTextCompare)
    GetFileName = Replace(sFullPath, “.xls”, “”, 1, -1, vbTextCompare)
End Function