export large file into excel

[straus]

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

 

[TYEZ]

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.

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

Hello, I want to use this file but with slight modifications. Can someone out there help me?
What I want to do is import a TXT file that has about 70000 rows of numbers into excel. I know it has a max of 65536 so I would like it to import the numbers and put them into 2 columns C and the rest in D.
Is this possible?
Thanks so much!

 

[NateO]

Duplicate threads by the same OP on the same topic are not allowed here.

http://www.mrexcel.com/board2/viewtopic.php?p=1190107#1190107

Please continue in your new thread. Thank you.

Edit: You're not the OP!

Continue in your new thread, and not here, anyway.

 

[p treanor]

Ive used Dan's code to import a large text file which contains semi-colons as a delimiter. It imports it really quickly over multiple sheets, but it splits some lines of data over two cells. It splits it everywhere there's a comma. The semi-colon delimiters are still in the data when it's imported, and i've checked that comma is not set as a delimiter in Text to Columns.

I've also had a look in the help files to see if there was some property i could change to stop this happening, maybe some ADO connection property, but i drew a blank.

Anyone got any ideas on how to stop this happening?

 

[bbbuffalo]

Sorry for sounding dumb, but if you could explain this in a step by step manner that any newbie could use that would be great.

I have a text file with 75,000 rows of data.

Do I open XL then copy and paste that script somewhere or what?
Thanks in advance.

 

[NateO]

The code I wrote here:

http://www.mrexcel.com/board2/viewtopic.php?p=1190896#1190896

Actually exports from Access to Excel. So, it belongs in an Access module.

Dan's code looks like it imports to Excel, so that code goes into an Excel module.

 

[louisjeorge75]

Hi i need a Pipe delimter to be set how can i add this to the code

 

[louisjeorge75]

i have the below macro to split text files and place it indifferent sheet

i get error in the below line

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

what might be the problem




Sub Large()

Dim strFilePath As String, strFilename As String, strFullPath As String
Dim oConn As Object, oRS As Object

strFullPath = Application.GetOpenFilename("Pliki tekstowe (*.txt),*.txt", , _
"Wybierz plik tekstowy...")

If strFullPath = "False" Then Exit Sub

strFilename = Dir(strFullPath)
strFilePath = Replace(strFullPath, strFilename, "")

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")
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