Opening a text file

K

Khawajaanwar

I have 390 MB data text file, i try to open this file with MS Excel only a
part of the file was open. Please help me open this file with MS Excel or MS
Access
Thanks

Khawaja anwar
 
H

Harald Staff

That is huge. Do you need all the data in one single place and/or at the
same time? What are you going to do with the data ? What does it contain ?

Best wishes Harald
 
K

Khawajaanwar

Thanks for your quick reply, It dosen't matter whether it is open in a single
file or more, i have to edit these data and the remaing file will much much
shorter.

Thanks once again

Khawaja Anwar
 
H

Harald Staff

Here is a macro that opens the text file and writes new parts with 20000
rows each in it. Those are written to the C:\Temp folder, shange to fit your
needs. You can of course also change 20000 to another number if you want
more / fewer rows in each file.

Sub SplitTextFile()
Dim F As Variant
Dim ToRead As String
Dim ToWrite As String
Dim ReadLine As String
Dim L As Long, M As Long
Dim iOne As Integer
Dim iTwo As Integer

F = Application.GetOpenFilename
If F = False Then Exit Sub

ToRead = CStr(F)
iOne = FreeFile
Open ToRead For Input As #iOne

L = 1

ToWrite = "C:\Temp\Part" & Format$(L, "00000000") & ".txt"
iTwo = FreeFile
Application.StatusBar = "Writing " & ToWrite
Open ToWrite For Output As #iTwo

While Not EOF(iOne)
Line Input #iOne, ReadLine
M = M + 1
If M > 20000 Then
Close #iTwo
DoEvents
L = L + 1
ToWrite = "C:\Temp\Part" & _
Format$(L, "00000000") & ".txt"
Application.StatusBar = "Writing " & ToWrite
Open ToWrite For Output As #iTwo
M = 0
End If
Print #iTwo, ReadLine
Wend

Close #iOne
Close #iTwo
Application.StatusBar = False
MsgBox L & " parts written"
End Sub

HTH. Best wishes Harald
 
D

Dave Peterson

To the OP:

Harald gave you the code to split that huge file into pieces that are no bigger
than 20k lines. If you're going to edit those files, it could take a bit of
time.

I have no idea what you're trying to do, but if you have certain rules that
define what to extract, I bet Harald can incorporate those changes into his code
<bg>.

So if you said you only need the records that contain "xxxxyyyyyzzzz" or start
with "abcdef", then the "editing" could be done for you.

If you're going to get this data into one worksheet in excel, it's got to be
less than 65536 lines, too. You may want to give a hint on how many rows/line
you're expecting.
 
K

Khawajaanwar

I actually working in the tax department in Pakistan, i have to extracts the
data where in a column A or B number 26, 12 and 13 are written. Please
explain in details since i am bigginner and using Microsoff Office XP 2000 or
is there any way i can open this complete file in MS Access

Thanks again
 
H

Harald Staff

This is impossible to answer detailed enough without knowing the structure
of the huge textfile. But try to open Access and create a new empty database
with the opening wizard. Then go menu
File > Get external data > Import
choose textfile as filetype, point to your enormous file and try to import
it.

This is really not a beginner task. You are given Office and an enormous
textfile without further instructions, and maybe or maybe not someone will
pay his tax if you can solve it ? We should do this, my friend, if only for
the fun of it. Did my previous macro solution make any sense, or is it
unknown territory ?

Best wishes Harald
 
K

Khawajaanwar

Please explain how I wrote Macros in C:\Temp folder since I am a bigginner
and i don't know how to write Macros in C:\Temp folder. My text file name is
"AOP". Please explain in details
 
H

Harald Staff

A macro is a piece of programming code. The one I sent you will open your
big text file and generate many new smaller ones, each containining 20000
rows from your original file.

To run this, do the following:
Open excel. Open a new blank workbook.
Open the Visual basic Editor (by Alt F11 or by menu Tools > Macro > VB
Editor).
Inside the VB editor, go menu Insert > Module.
You will see a white document now. This is a module. Copy-paste my
programming code into it, starting with the word Sub and ending with the
line End Sub.

Inside this code are two things you must consider:

1 The folder-file specifications to be written. In my code it says twice
"C:\Temp\Part"
You need a folder C:\Temp on your computer and you must be allowed to write
to it. If not then change this in the code so that it writes to anoether
location than C:\Temp

2 The number 20000 .This is the number of lines in the new textfiles. Change
if 20000 is too many or to few.

Now return to Excel. In its menu Tools > Macro > Run you can start the
macro. When you run it it asks you to open the textfile. Choose your big AOP
textfile in this dialog, or Cancel to abort the macro. The macro will run
for a while, and you will not see anything happening except an indicator
down left in the excel window.

After the macro you can hopefully open the file "C:\Temp\Part00000001.txt"
and its relatives in Excel.

This was hopefully suffif\cient macro details. Did you try to import the
textfile to Access ? That may be a far better way to solve this.

Best wishes Harald
 
Top