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:
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
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:
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:
Now you Have a Bunch of Individual CSV Files
3) Open a Command Prompt
Start –> All Programs –> Accessories
Yep, That’s a Cmd Prompt
4) Change Directory to the Folder Containing Your CSV Files
This Isn’t a Cmd Prompt Tutorial, but the CD Command Changes Folder
5) Copy Them All Into One File
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:
6) Now Import that CSV into PowerPivot
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:
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)
sDir = Dir$
Dim sNewPath As String
sNewPath = GetFileName(ActiveWorkbook.FullName)
sNewPath = sNewPath & “.csv”
ActiveWorkbook.SaveAs Filename:=sNewPath, FileFormat:=xlCSV, CreateBackup:=False
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)