Importing more than 65,000 rows into excel?

NYSpeed Automotive NYSpeed Off Topic
1

Is there an easy way?

I would rather not cut/paste into individual csv files as it takes forever just to highlight the data? ( only 500,000 rows)

I do not know VB, but i’ll bet it would be easy to write a macro to import them?

Any thoughts?

2

upgrade to 2007

3

I thought you knew about the corporate life.

4

short answer: No.
You can however, bring in a 500k row CSV to Access.

5

:frowning:
Access not found.

6

get them to buy you 07

no other real options

7

I’m, in the 300k’s and 10 sheets now…
I’ll post the macro if it works and does not crash when done.

8

516621 rows successfully imported into one excel file with 11 sheets.:nerd:

I had to make a few changes to some code I found online, and I had to create a schema.ini file.

Sub ImportLargeFileADO()
'Imports text file into Excel workbook using ADO.
'If the number of records exceeds 65536 then it splits it over more than one sheet.
'danielklann.com

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 csv file...")

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

'This gives us a full path name e.g. C:	emp\folder\file.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

schema.ini

[data.csv]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=25
CharacterSet=ANSI

9

you’re not saving as a CSV are you? CSV’s require seperate files for each sheet.

10

nope.
I had a data logger that generates my .csv files.

But, it makes huge files. I need to analyze the data in excel.
The problem I had was getting from .csv to xls.