Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strip records from large textfile with VBA
Dear members;
I'm stuck with the following chalange. I must read some (not all) records from a textfile into a workbook. But...... The Excel version is 2003 (can and may not be 2007) and the textfile contains over 250.000 records !! The fields from the textfile are seperated by semi-colons and I only need those records where the 5th field exceeds 0. The textfile is not fixed-width, so I can't fix this position in a record (but it is the position I need to find). What method should I use to extract those records using VBA. Is it possible to alter this module ? Private Sub Extract_Records() Open ThisWorkbook.Path & Application.PathSeparator & "Invoice.txt" For Input As #1 r = 1 Do While Not EOF(1) Line Input #1, Data 'Do not import a record that begins with Total If Not Left(Data, 5) = "TOTAL" Then '---- I think I should change this line of code, but How ? It must find the 5th field 0 'Import record r = r + 1 Cells(r, 1).Value = Data End If Loop Close #1 End Sub ---- I hope that this is clear enough !! -- Regards; Mark Rosenkrantz -- Spreadsheet Solutions Uithoorn Netherlands -- E: W: www.spreadsheetsolutions.nl -- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strip records from large textfile with VBA
try this code. I'm using split and texttocolumns. Private Sub Extract_Records() Open ThisWorkbook.Path & Application.PathSeparator & _ "Invoice.txt" For Input As #1 RowCount = 1 Do While Not EOF(1) Line Input #1, Data 'create a zero index base array splitdata = Split(Data, ",") 'Do not import a record that begins with Total If Not splitdata(4) = "TOTAL" Then Range("A" & RowCount) = Data RowCount = RowCount + 1 End If Loop Close #1 LastRow = Range("A" & RowCount).End(xlUp).Row Range("A1:A" & RowCount).TextToColumns _ Destination:=Range("A1"), _ DataType:=xlDelimited, _ Comma:=True End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=175311 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strip records from large textfile with VBA
Hi,
This works for me Private Sub Extract_Records() Open ThisWorkbook.Path & Application.PathSeparator & "Invoice.txt" For Input As #1 r = 1 Do While Not EOF(1) Input #1, Data$ If UCase(Left(Data$, 5)) < "TOTAL" And Val(Mid(Data$, 5, 1)) 0 Then r = r + 1 Cells(r, 1).Value = Data End If Loop Close #1 End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Spreadsheet Solutions" wrote: Dear members; I'm stuck with the following chalange. I must read some (not all) records from a textfile into a workbook. But...... The Excel version is 2003 (can and may not be 2007) and the textfile contains over 250.000 records !! The fields from the textfile are seperated by semi-colons and I only need those records where the 5th field exceeds 0. The textfile is not fixed-width, so I can't fix this position in a record (but it is the position I need to find). What method should I use to extract those records using VBA. Is it possible to alter this module ? Private Sub Extract_Records() Open ThisWorkbook.Path & Application.PathSeparator & "Invoice.txt" For Input As #1 r = 1 Do While Not EOF(1) Line Input #1, Data 'Do not import a record that begins with Total If Not Left(Data, 5) = "TOTAL" Then '---- I think I should change this line of code, but How ? It must find the 5th field 0 'Import record r = r + 1 Cells(r, 1).Value = Data End If Loop Close #1 End Sub ---- I hope that this is clear enough !! -- Regards; Mark Rosenkrantz -- Spreadsheet Solutions Uithoorn Netherlands -- E: W: www.spreadsheetsolutions.nl -- . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strip records from large textfile with VBA
No it doesn't I created my test .txt file badly formatted
-- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, This works for me Private Sub Extract_Records() Open ThisWorkbook.Path & Application.PathSeparator & "Invoice.txt" For Input As #1 r = 1 Do While Not EOF(1) Input #1, Data$ If UCase(Left(Data$, 5)) < "TOTAL" And Val(Mid(Data$, 5, 1)) 0 Then r = r + 1 Cells(r, 1).Value = Data End If Loop Close #1 End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Spreadsheet Solutions" wrote: Dear members; I'm stuck with the following chalange. I must read some (not all) records from a textfile into a workbook. But...... The Excel version is 2003 (can and may not be 2007) and the textfile contains over 250.000 records !! The fields from the textfile are seperated by semi-colons and I only need those records where the 5th field exceeds 0. The textfile is not fixed-width, so I can't fix this position in a record (but it is the position I need to find). What method should I use to extract those records using VBA. Is it possible to alter this module ? Private Sub Extract_Records() Open ThisWorkbook.Path & Application.PathSeparator & "Invoice.txt" For Input As #1 r = 1 Do While Not EOF(1) Line Input #1, Data 'Do not import a record that begins with Total If Not Left(Data, 5) = "TOTAL" Then '---- I think I should change this line of code, but How ? It must find the 5th field 0 'Import record r = r + 1 Cells(r, 1).Value = Data End If Loop Close #1 End Sub ---- I hope that this is clear enough !! -- Regards; Mark Rosenkrantz -- Spreadsheet Solutions Uithoorn Netherlands -- E: W: www.spreadsheetsolutions.nl -- . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strip records from large textfile with VBA
Hi Mark,
I normally associate a text file such as you describe here as being a 'data dump'. Reason is that I can't imagine using a text file for storing large amounts of data when a database has so much more to offer. However, if your text file includes the field names in the first row then you could use ADO and query it same as a database table, where the filename is the table name. You could only return those records you want by specifying criteria in your SQL statement. This way it only returns only the records you want, which is better than searching every record to see if it contains what you want. Be aware that Excel2003 only has 65,536 rows per sheet so you'll have to start a new sheet [or column] if all 250,000 records are returned. Excel2007 will handle it without parsing to another sheet [or column]. HTH Kind regards, Garry |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strip records from large textfile with VBA
Thank you all.
It didn't solve my problem, but it did give some interesting hints for possible workarounds. -- Met vriendelijk groet; Mark Rosenkrantz -- Spreadsheet Solutions Uithoorn Nederland -- E: W: www.spreadsheetsolutions.nl -- "Spreadsheet Solutions" wrote in message ... Dear members; I'm stuck with the following chalange. I must read some (not all) records from a textfile into a workbook. But...... The Excel version is 2003 (can and may not be 2007) and the textfile contains over 250.000 records !! The fields from the textfile are seperated by semi-colons and I only need those records where the 5th field exceeds 0. The textfile is not fixed-width, so I can't fix this position in a record (but it is the position I need to find). What method should I use to extract those records using VBA. Is it possible to alter this module ? Private Sub Extract_Records() Open ThisWorkbook.Path & Application.PathSeparator & "Invoice.txt" For Input As #1 r = 1 Do While Not EOF(1) Line Input #1, Data 'Do not import a record that begins with Total If Not Left(Data, 5) = "TOTAL" Then '---- I think I should change this line of code, but How ? It must find the 5th field 0 'Import record r = r + 1 Cells(r, 1).Value = Data End If Loop Close #1 End Sub ---- I hope that this is clear enough !! -- Regards; Mark Rosenkrantz -- Spreadsheet Solutions Uithoorn Netherlands -- E: W: www.spreadsheetsolutions.nl -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to NOT import some records in a large text file | Excel Programming | |||
XY Scatter-problems with large data records | Excel Discussion (Misc queries) | |||
Manipulate textfile with VBA | Excel Programming | |||
very large text and CONTINUE records | Excel Programming | |||
Export as Textfile with VBA | Excel Programming |