Vfrdtyky

I was trying to import a large .csv file into Excel. The file has close to 4 million rows and 329 columns so it far exceeded Excel's 65536 1M row limit. I found a VB script online to import text files into Excel where once the row limit was exceeded the script would create a new worksheet till the total rows in all worksheets equal the total rows in the original text file.

Sub ImportLargeFile()

'Imports text file into Excel workbook using ADO.

'If the number of records exceeds 65536 then it splits it over more than one sheet.



Dim strFilePath As String, strFilename As String, strFullPath As String

Dim lngCounter As Long

Dim oConn As Object, oRS As Object, oFSObj As Object



'Get a text file name

strFullPath = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")



If strFullPath = "False" Then Exit Sub  'User pressed Cancel on the open file dialogue



'This gives us a full path name e.g. C:tempfolderfile.txt

'We need to split this into path and file name

Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")



strFilePath = oFSObj.GetFile(strFullPath).ParentFolder.Path

strFilename = oFSObj.GetFile(strFullPath).Name





'Open an ADO connection to the folder specified

Set oConn = CreateObject("ADODB.CONNECTION")

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

           "Data Source=" & strFilePath & ";" & _

           "Extended Properties=""text;HDR=Yes;FMT=Delimited"""



Set oRS = CreateObject("ADODB.RECORDSET")



'Now actually open the text file and import into Excel

oRS.Open "SELECT * FROM " & strFilename, oConn, 3, 1, 1

While Not oRS.EOF

    Sheets.Add

    ActiveSheet.Range("A1").CopyFromRecordset oRS, 65536

Wend



oRS.Close

oConn.Close



End Sub

However, when I run it in Excel 2007 I get the following error:

Run-time error '-2147217900 (80040e14)':



Syntax error in FROM clause.

On clicking debug it points to this line

    oRS.Open "SELECT * FROM " & strFilename, oConn, 3, 1, 1

I have non-existent VB skills so any help here would be appreciated.

Thanks!

Edit: I found this http://support.microsoft.com/kb/193514 but this doesn't work in this case, in fact, I am not even sure where to place the square brackets. Any place I put the brackets in that line, Excel refuses to give me the run option.

Edit 2: I tried running this on a different machine which just has a lot more juice in it to make memory not an issue. This computer also has Office 2013 on it (no Office 365 with it though). I ran the same macro on that and it gave the following error.

Run-time error '3706':

Provider cannot be found. It may not be properly installed.

When I click Debug it points to this line

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

       "Data Source=" & strFilePath & ";" & _

->     "Extended Properties=""text;HDR=Yes;FMT=Delimited"""

Excel 2007 has no 65K limit, as far as I know. Just try importing the csv directly.

EDIT: Comments tell it has a 1M limit, so there is need in splitting the file. When it is simple (no quoted text with linebreaks), then split and head would help. They are availiable under Windows too.

Have you looked into PowerPivot? It is a free add-in from Microsoft. It will allow you to connect to the original data source, or use your entire CSV, and load millions of rows (beyond the cell limits). You can summarize the data in Pivot Tables, apply functions to the data, etc. PowerPivot was tailor-made for situations like yours.

Download