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