export large file into excel

straus

Board Regular
Joined
Oct 7, 2002
Messages
192
Hello,

I have a database of 75000 rows in text format which has to be converted to excel, but the limitation in excel is only 65536. hence, I want to a macro that will copy the 1st 65536 rows into the 1st sheet in excel. The remaining rows should be copied into the second sheet. I know that it can be done with access. but i dont want to use access.
I have got a code to do this, but it takes much too long. if the code could be modified to make it faster, pls help. thanks a lot

code:

Sub LargeFileImport()

'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
'Ask User for File's Name
FileName = InputBox("Please enter the Text File's name, e.g. S:RevenueStrausBATCHOCCMUM121002.txt")
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FileName For Input As #FileNum
'Turn Screen Updating Off
Application.ScreenUpdating = False
'Create A New WorkBook With One Worksheet In It
Workbooks.Add template:=xlWorksheet
'Set The Counter to 1
Counter = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & FileName
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr
End If

'For xl97 and later change 16384 to 65536
If ActiveCell.row = 16384 Then
'If On The Last Row Then Add A New Sheet
ActiveWorkbook.Sheets.Add
Else
'If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If
'Increment the Counter By 1
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop
'Close The Open Text File
Close
'Remove Message From Status Bar
Application.StatusBar = False

End Sub
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Hi,

Try this code. It uses the Microsoft ActiveX Data Objects (ADO) to import the file into Excel. This code will only work with Excel 2000 onwards.

Let me know how you get on.

Code:
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 (*.txt),*.txt", , "Please selec text 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: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

Edit - I've changed this code from what I posted originally.

_________________<font face="Impact">Hope this helps,
Dan</font>
This message was edited by dk on 2002-10-30 05:54
 

straus

Board Regular
Joined
Oct 7, 2002
Messages
192
thanks for the code...i just wanted to know how well it works. how long does it take to import a file of say 75000 rows and 4 columns? I have not tried it yet as i have excel 97. i will only move to excel 2000 if this code works well. So pls let me know...thanks a ton in advance

straus
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
It took 5 seconds on my machine - 1ghz with 128mb of RAM.
 

straus

Board Regular
Joined
Oct 7, 2002
Messages
192

ADVERTISEMENT

thanks a ton for the advice...i'm going to install it reght away. grateful for your advice

regards
straus
 

gmirsky

New Member
Joined
Dec 9, 2002
Messages
4
On 2002-10-30 05:15, dk wrote:
Hi,

Try this code. It uses the Microsoft ActiveX Data Objects (ADO) to import the file into Excel. This code will only work with Excel 2000 onwards.

Let me know how you get on.

Code:
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 (*.txt),*.txt", , "Please selec text 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: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

Edit - I've changed this code from what I posted originally.

_________________<font face="Impact">Hope this helps,
Dan</font>
This message was edited by dk on 2002-10-30 05:54

Thanks! It gives me a great place to start. Now all I need to do is chase the other users off of Office 95 and Office 97. If I can get this working for Office 2000 maybe it will be an incentive for them to upgrade.
 

Vulak

New Member
Joined
Oct 26, 2005
Messages
2

ADVERTISEMENT

Hello


I made macro from this code and im trying to use it on 200gb CSV excel file.

I renamed that CSV file to TXT file but when im trying to run this macro, it tells me that object cant be found.

What can I do or do I need to make very special macro for CSV files?
 

ddo

New Member
Joined
Jul 19, 2006
Messages
1
I found that the set of code from Dan was great; it's exactly the topic that i'm struggling with. However when I execute the macro to the line :

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

I got an error message:

"Run-time error '-2147217900 (80040e14)'
Syntax error in FROM clause."

I have no clue what's was wrong with this line of code.
My environment:
Pentium 4 CPU, 3 GHz, 512 MB RAM, Win XP Pro, Office 2003 Pro.

Please help!
:rolleyes:
 

vanclute

Board Regular
Joined
Oct 23, 2002
Messages
218
Code doesn't quite do the job...

This worked for me insofar as it imported a single column of words and split it into multiple sheets, BUT... the data in question was a csv file, and I expected each sheet to have all the data in separate columns. It appears instead to have imported ONLY the first column of data, and ignored all the rest. Anyone have a solution like this but for multiple columns of data?
 

Forum statistics

Threads
1,143,737
Messages
5,720,566
Members
422,291
Latest member
Deveshk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top