Importing multiple large .csv files for analysis (Run Time Error - 7)

Howdy,

Firstly, I am a total beginner when it comes to VBA programming but I am keen to learn and dont want to be dissuaded in doing so by a few bumps in the road.

I have 10 .csv files each containing 800,000+ rows (all in the same folder entitled 1.csv, 2.csv, 3.csv etc). I am looking to bring them into 1 excel workbook (each in a different worksheet) and from there pivot table each worksheet to produce an overall 'average' pivot table.

The first issue I encountered was being able to copy and paste such large .csv files into Excel (Excel 2010). The only way I have managed to do so manually was by importing the data through Data>Get Extrenal Data> From Text.

By way of viewing macros and similar codes online I have come up with the following:

--------------------------------------------------------

Sub TEST()
'
' TEST Macro
'

'
For i = 1 To 10
Sheets.Add
ActiveSheet.Name = i & ".csv"
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;D:\Erskine Bridge 2012\## Calibration ##\## Models ##\Base Model - Erskine Bridge 2012\Log\Path Journey Time Runs\" & i & ".csv" _
, Destination:=Range("$A$1"))
.Name = "1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
ActiveWorkbook.Connections("1").Delete
Next i
End Sub

----------------------------------------------------

At the moment Im just trying to investigate how to bring in the .csv automat

When I run the code it works for 1.csv, creates the sheet for 2.csv and gives me the error 'Run Time Error 7 - out of memory'. This is pretty much my first go at any VBA programming so Im trying not to give up on this so any help would be greatly appreciated.

1.csv, 2.csv, 3.csv files are each 150Mb

Thanks in advance

Richard

Norie,

I can write macros to pivot table the original .csv's and then bring them into one workbook as 10 individual pivot tables but I was looking for something to streamline the process from 11 macros (ie - 10 csv + 1 overall to import them) to a single macro. Ideally I would then attempt to set up a prompt to select the .csv folder location and a prompt for the number of runs and it can be transferable to all my projects.

As mentioned, all I am looking for is an average pivot table of the 10 individual pivot tables - I cant see this taking more than 15 mins to produce the 'average' whereas the long-winded method will take x5 longer (imo). Im interested in the least amount of user-input as well so I can send it to colleagues.

Richard

It takes about 5 mins to run the macro to create the pivot table.

I open 1.csv, the macro adds a calculated column, creates the pivot table and saves the whole thing as .xls

I assumed that you had to open up say 1.csv and then run the macro, then open up 2.csv then run the macro, open up 3.csv then run the macro...etc etc? then open a new .xls file and bring each of these pivots into individual worksheets and from there I can insert a new sheet which calculates the average.

If you are able to somehow open up an excel sheet and run a macro that opens up 10 csvs in the background, pivots them and then averages them then great, Im all for that !!?

Richard

This sounds promising.

So as I understand it:

1. I have a blank Excel workbook (WB1).
2. I write code in this workbook that will loop through my 10 csv's
3. The code opens up csv1, performs the pivot table and then will copy that pivot table into Sheet1 (renaming this csv1), close csv1.
4. once it has done this the loop will direct it toward csv2 which is then opened, same as above (sheet2 renamed as csv2), close csv2.

Could you point me towards any similar code (doesnt have to be csv or pivots, just something that shows how to loop through a directory)?

Thanks