vba - Very large excel file - how to copy data between sheets?

Question

I need to to import some csv files into excel 2010 and create a very simple, but very large database.
The whole story will be - five columns and thousands of rows.
VBA is also simple - copy data from one sheet to another - and vice versa.
But I need to care about memory requirement, because of potentially very large file size.

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim r1 As Range
Dim r2 As Range
Set ws1 = Sheets("01")
Set ws2 = Sheets("02")
Set r1 = ws1.Range("A1:B10") ' for example
Set r2 = ws2.Range("C5:D14")
r1.Copy Destination:=r2 'first way
r2.Value = r1.Value ' second way

Is there any differences between this two methods, in the scope of memory/time consuming?
At the and I will have over 10,000 rows. What will be the size of the file?

Answer 1

You can utilize ADO to query text files as if they were a database table. This allows you to write SQL queries to pull data out of your text files. You can do this any text file or even .xls files if you wanted to.

The code/process for doing so is fairly simple. You'll need to reference the Microsoft ActiveX Data Objects 2.X Library first and then use something like the following:

Dim cn as New ADODB.Connection
Dim rs as New ADODB.Recordset
Dim i as Integer

With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=C:SomeFolder;" & _
                        "Extended Properties=""text; HDR=Yes;FMT=Delimited"""
    .Open

    With rs
        .Open "SELECT * from fileName.txt", cn

        'Loop through each row in query
        While Not (.EOF Or .BOF)
            'Loop through each column in row
            For i = 0 to .Fields.Count - 1
                Debug.Print .Fields(i).Value 'Print value of field to Immediate Window
            Next i

            .MoveNext
        Wend

        .Close
    End With

    .Close
End With

Set rs = Nothing
Set cn = Nothing

This will loop through your text file and display the value of the first column in your VBA immediate window. It also assumes that your file has header rows. If it does not then you need to alter HDR in your ConnectionString to No.

The code will automatically try and infer types for you but if you're running into issues with it not discovering the correct type (such as leading zeros) then you can explicity define a schema for your file. It's important to note that if you go the schema route then your ConnectionString arguments like HDR and FMT WILL BE IGNORED. They will retain their default settings as defined in the Registry unless you override them in the schema definition. More info on schema.ini files can be found here: http://msdn.microsoft.com/en-us/library/windows/desktop/ms709353(v=vs.85).aspx.

Here is another useful link: http://msdn.microsoft.com/en-us/library/ms974559.aspx. It's an article written by the Microsoft Scripting Guys and is how I originally learned about the process.

Lastly, if you ever use this process with .xls files then you should know that you should NEVER query an OPEN .xls file. There's a nasty memory leak bug with OPEN .xls files (more info here: http://support.microsoft.com/default.aspx?scid=kb;en-us;319998&Product=xlw). As long as you query CLOSED .xls documents then you shouldn't have any issues whatsoever =D. The syntax in the SQL FROM clause is a bit different since you have to target particular sheet but IIRC the Scripting Guys article I linked explains how to do so.