Importing large Text files

[Ron Brookes]

Does anyone have or know were to get a macro which imports
a large .CSV file (500000 rows) into multiple 65000 row
sheets or workbooks of an excel spread sheet. It is
similar to the excel knowledge base article Q120596 but it
only works with txt files, not CSV files. It needs to be
modifed somehow. Help!!

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. test.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 = 50000 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

[Dave Peterson]

Rename your .csv to .txt and it'll work.

With this amount of data, you may want to look to another application
(Access???).

--

Dave Peterson
ec3...@msn.com

[Bob Flanagan]

Ron, when you say it doesn't work, is it crashing? Or is the data most
likely not parsed? Just run another macro after inporting that does a text
to column conversion. You can record a macro that helps you find the needed
statements.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Ron Brookes" <Rbro...@ocdus.jnj.com> wrote in message
news:102e01c1c07c$eb84a120$a4e62ecf@tkmsftngxa06...

[Ron Brookes]

This macro reads the data in as a I/O file because of its
size. It cannot be opened as a normal file without being
truncated to 65000 rows. It works fine when using a tab
delimited text file but not a comma delimited file. Its
all in the lines:

Open FileName For Input As #FileNum or:
Line Input #FileNum, ResultStr : the macro stops at this
line

When I look at the ResultStr variable it is not just the
first line but it is filled with all the data in the file.
I think this is because the CSV format does not have a
carriage return at the end of each line. Just changing the
file extension from csv to txt does not work because it is
still a comma delimited file and not a tab delimited.
I am trying to understand the utility as it appears useful
for other things.

Any help would be appreciated.

>.
>

[jaf]

Hi Ron,
Are there commas in the data?
If you open the file in Word and click the show/hide button (looks like a
backwards P) you will see the puncuation marks.
They may be tabs or spaces. If they are spaces the data may be "X" chars
long and not tab delim.
Just one long string. Each "record" 20 chars.?

John

"Ron Brookes" <rbro...@ocdus.jnj.com> wrote in message
news:94e601c1c131$beb07c40$3aef2ecf@TKMSFTNGXA09...

[Dave Peterson]

My answer to change the extension to .txt from .csv was bad (very bad!). (I
thought that you were just opening the file as a workbooks.open kind of thing.)

But I think you hit the nail on the head with missing carriage return at the end
of each line.

By chance, does this file originate on a Unix box? They have different ways to
deal with end of line markers.

I have a program at work that uses Unix data. But before I open it in excel, I
shell to a Unix2DOS.exe utility (searched via www.shareware.com) that converts
the EOL markers to DOS/Windows stuff.

(I actually grabbed a couple (out of hundreds of similar versions) of
Unix2DOS.exe's. I wanted one that worked as a commandline and worked fast.
There were a few GUI versions and some that worked pretty slow.)

After it's converted, I can import everything into excel. (Actually, I open the
file and just read in the records that I want. 300K records total, but I only
want about 300.)

=====
If this actually matches what you're doing (hahaha), then you'll probably want a
procedure that allows you to ShellAndWait for the external process to finish
(watchout for linewrap):

Option Explicit

Private Const PROCESS_QUERY_INFORMATION As Long = &H400
Private Const STILL_ACTIVE As Long = &H103

Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess _
As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long

Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess _
As Long, lpExitCode As Long) As Long

''' Arguments: szCommandLine The command line to execute using Shell.
''' iWindowState (Optional) The window state parameter to pass
''' to the Shell function. Default = vbHide.
''' See Shell function help for other options.
Sub ShellAndWait(ByVal szCommandLine As String, Optional ByVal iWindowState _
As Integer = vbHide)

Dim lTaskID As Long
Dim lProcess As Long
Dim lExitCode As Long
Dim lResult As Long

''' Run the Shell function.
lTaskID = Shell(szCommandLine, iWindowState)

''' Get the process handle from the task ID returned by Shell.
lProcess = OpenProcess(PROCESS_QUERY_INFORMATION, 0&, lTaskID)

''' Loop while the shelled process is still running.
Do
''' lExitCode will be set to STILL_ACTIVE as long as the shelled
''' process is running.
lResult = GetExitCodeProcess(lProcess, lExitCode)
DoEvents
Loop While lExitCode = STILL_ACTIVE

End Sub

--

Dave Peterson
ec3...@msn.com