export large file into excel

S

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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
thanks a ton for the advice...i'm going to install it reght away. grateful for your advice

regards
straus
 
Upvote 0
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
Click to expand...

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.
 
Upvote 0
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?
 
Upvote 0
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!
:roll:
 
Upvote 0
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Help recreating MATLAB program in Excel - Parsing Binary Data
Replies
1
Views
270
tylererhard
T
A
  • Question Question
[Excel / VBA] How do align some columns exporting to TXT?
Replies
3
Views
301
Joe4
Joe4
N
  • Question Question
Slow vba with opening 60 files
Replies
7
Views
269
NickvdB
N
B
  • Question Question
VBA exports worksheet from XLSM to XLXS matching all source properties doesn't keep print area
Replies
0
Views
98
brentcole
B
M
  • Solved
cut/copy and paste row based on cell value not being equal to specific text
Replies
6
Views
492
mlan1604
M

Forum statistics

Threads
1,223,840
Messages
6,174,955
Members
452,593
Latest member
Jason5710

Share this page

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
Back
Top