Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Opening a text file
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 |
#2
|
|||
|
|||
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 "Khawajaanwar" skrev i melding ... 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 |
#3
|
|||
|
|||
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 "Harald Staff" wrote: 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 "Khawajaanwar" skrev i melding ... 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 |
#4
|
|||
|
|||
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 "Khawajaanwar" skrev i melding ... 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 "Harald Staff" wrote: 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 "Khawajaanwar" skrev i melding ... 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 |
#5
|
|||
|
|||
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. Harald Staff wrote: 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 "Khawajaanwar" skrev i melding ... 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 "Harald Staff" wrote: 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 "Khawajaanwar" skrev i melding ... 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 -- Dave Peterson |
#6
|
|||
|
|||
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 "Dave Peterson" wrote: 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. Harald Staff wrote: 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 "Khawajaanwar" skrev i melding ... 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 "Harald Staff" wrote: 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 "Khawajaanwar" skrev i melding ... 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 -- Dave Peterson |
#7
|
|||
|
|||
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 "Khawajaanwar" skrev i melding ... 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 |
#8
|
|||
|
|||
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 "Harald Staff" wrote: 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 "Khawajaanwar" skrev i melding ... 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 |
#9
|
|||
|
|||
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 "Khawajaanwar" skrev i melding ... 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 "Harald Staff" wrote: 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 "Khawajaanwar" skrev i melding ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
opening text files that have more rows than 65536 | Excel Worksheet Functions | |||
Opening a file with a Macro | Excel Discussion (Misc queries) | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Make a text file from Excel workbook | Excel Discussion (Misc queries) | |||
Problem opening an XML file in Excel - getting "ns1:macrosPresent" | Excel Discussion (Misc queries) |