Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help in importing text file
Hi
I have a text file which has data(sample data below), i cannot import data directly into the excel sheet because its a large file and it limits the excel rows for import. I need to import from the text file phase by phase, the data required. Presume I have in the first line '1BOSTON', i need to get all the data until again i hit the other string '1NEW YORK' in the row and this data has to be parsed on fixedlenth or each of the rows between '1BOSTON' to '1BOSTON' and looping thru and parsed imported. Then the string'1NEWYORK' to string '1NEWYORK' untill it hits another new string... Can any one help me with the code please!!!! I started the coding but could not figure out where i should go... Sub MetroDetail() Dim oCell As Range, xCell As Range Dim x As Integer, Count As Integer, z As Integer Dim qt, MetConn As String Dim sinput_path As String Dim REGIONFILE As String 'Part of the code Application.DisplayAlerts = False Application.ScreenUpdating = False 'Windows("Main.xls").Activate sinput_path = "C:\DATA\" REGIONFILE = "LCSD02R.TXT" InFile = FreeFile() Open sinput_path & REGIONFILE For Input Access Read As InFile Do While Not EOF(InFile) Line Input #InFile, y If (InStr(1, y, "1BOSTON", vbTextCompare) 0) Then ActiveCell.Value = y 'need to write the code for extracting and parsing End If Loop Close #InFile [A1].Select End sub Sample Data for Review (Note: for easy understanding i have added the linenumbers as "L01:" which are not part of data) L01:1BOSTON 1 L02:- 2002 CALENDAR YEAR SALES L03:- SHOWING PRODUCE WHO SOLD INTO ACTON MARKET AREA 12B202 L04:0PURCHASER TOWN/ BOOSTING DEALER CHRISTMAS HOTCAKE L05: POST OFFICE SLR DEALER NAME DEALER MARKET AREA L06: COMMUNITY NO LOCATIONS L07: 12B202 Acton MERCY-KRANK In Acton MA 57 704 5 13 41 116 23 622 10 34 L08: TOTAL IN MARKET 57 704 5 13 41 116 23 622 10 34 L09:0 12B073 Clark & White Inc Newton MA 1 12 1 2 L10: 12B386 Sentry HEART-FORD S Medford MA 1 12 2 3 3 81 1 1 5 L11: 12B491 North Shore D-T Inc Peabody MA 1 1 L12:1BOSTON 2 L13:- 2002 CALENDAR YEAR SALES L14:- SHOWING PRODUCE WHO SOLD INTO ACTON MARKET AREA 12B202 L15:0PURCHASER TOWN/ BOOSTING DEALER CHRISTMAS HOTCAKE L16: POST OFFICE SLR DEALER NAME DEALER MARKET AREA L17: COMMUNITY NO LOCATIONS L18: 12D487 Gervais MERCY- KRANK Lowell MA 2 500 2 L19: 999999 UNKNOWN 1 250 1 L20: TOTAL POST OFFICE PURCHASES 21000 1 3 41000 4 L21:1NEW YORK 377 L22:- 2002 CALENDAR YEAR SALES L23:- SHOWING PRODUCE WHO SOLD INTO AL-SCH-TRY MARKET AREA 14B981 L24:0PURCHASER TOWN/ BOOSTING DEALER CHRISTMAS HOTCAKE L25: POST OFFICE sLR DEALER NAME DEALER MARKET AREA L26: COMMUNITY NO LOCATIONS L27: 14B402 Nemith CHOLO Corp Latham NY 60 150 10 13 83 37 219 1 7 48 L28: 14B539 Fuccillo HUMBLE-MET Schenectady NY 58 145 2 6 7 73 13 77 3 16 L29: 14B561 Lazare DETPLUB I Albany NY 76 190 16 50 142 92 544 18 113 L30: TOTAL IN MARKET 194 485 2 32 70 298 142 840 1 28 177 L31:1NEW YORK 377 L32:- 2002 CALENDAR YEAR SALES L33:- SHOWING PRODUCE WHO SOLD INTO AL-SCH-TRY MARKET AREA 14B981 L34:0PURCHASER TOWN/ BOOSING DEALER CHRISTMAS HOTCAKE L35: POST OFFICE SLR DEALER NAME DEALER MARKET AREA L36: COMMUNITY NO LOCATIONS L37: 14B402 Nemith CHOLO Corp Latham NY 60 150 10 13 83 37 219 1 7 48 L38: 14B539 Fuccillo HUMBLE-MET Schenectady NY 58 145 2 6 7 73 13 77 3 16 L39: 14B561 Lazare DETPLUB I Albany NY 76 190 16 50 142 92 544 18 113 L40: TOTAL IN MARKET 194 485 2 32 70 298 142 840 1 28 177 Here I need to import the line numbers L01 thru L21 for "1BOSTON" data at the same time need to parse into each colums. Thanks very much |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help in importing text file
To save you time and effort import your text file into an
Access database table. Then write a simple query to extract the required records. Just use the import wizard to get you data in, then use the query wizard to select the records that you are after. From the query you can copy and paste into excel. If you don't have Access, suggest you ask the source of where your data came from and get them to separate the data for you. Excel is limited to 65536 rows. regards KM -----Original Message----- Hi I have a text file which has data(sample data below), i cannot import data directly into the excel sheet because its a large file and it limits the excel rows for import. I need to import from the text file phase by phase, the data required. Presume I have in the first line '1BOSTON', i need to get all the data until again i hit the other string '1NEW YORK' in the row and this data has to be parsed on fixedlenth or each of the rows between '1BOSTON' to '1BOSTON' and looping thru and parsed imported. Then the string'1NEWYORK' to string '1NEWYORK' untill it hits another new string... Can any one help me with the code please!!!! I started the coding but could not figure out where i should go... Sub MetroDetail() Dim oCell As Range, xCell As Range Dim x As Integer, Count As Integer, z As Integer Dim qt, MetConn As String Dim sinput_path As String Dim REGIONFILE As String 'Part of the code Application.DisplayAlerts = False Application.ScreenUpdating = False 'Windows("Main.xls").Activate sinput_path = "C:\DATA\" REGIONFILE = "LCSD02R.TXT" InFile = FreeFile() Open sinput_path & REGIONFILE For Input Access Read As InFile Do While Not EOF(InFile) Line Input #InFile, y If (InStr(1, y, "1BOSTON", vbTextCompare) 0) Then ActiveCell.Value = y 'need to write the code for extracting and parsing End If Loop Close #InFile [A1].Select End sub Sample Data for Review (Note: for easy understanding i have added the linenumbers as "L01:" which are not part of data) L01:1BOSTON 1 L02:- 2002 CALENDAR YEAR SALES L03:- SHOWING PRODUCE WHO SOLD INTO ACTON MARKET AREA 12B202 L04:0PURCHASER TOWN/ BOOSTING DEALER CHRISTMAS HOTCAKE L05: POST OFFICE SLR DEALER NAME DEALER MARKET AREA L06: COMMUNITY NO LOCATIONS L07: 12B202 Acton MERCY-KRANK In Acton MA 57 704 5 13 41 116 23 622 10 34 L08: TOTAL IN MARKET 57 704 5 13 41 116 23 622 10 34 L09:0 12B073 Clark & White Inc Newton MA 1 12 1 2 L10: 12B386 Sentry HEART-FORD S Medford MA 1 12 2 3 3 81 1 1 5 L11: 12B491 North Shore D-T Inc Peabody MA 1 1 L12:1BOSTON 2 L13:- 2002 CALENDAR YEAR SALES L14:- SHOWING PRODUCE WHO SOLD INTO ACTON MARKET AREA 12B202 L15:0PURCHASER TOWN/ BOOSTING DEALER CHRISTMAS HOTCAKE L16: POST OFFICE SLR DEALER NAME DEALER MARKET AREA L17: COMMUNITY NO LOCATIONS L18: 12D487 Gervais MERCY- KRANK Lowell MA 2 500 2 L19: 999999 UNKNOWN 1 250 1 L20: TOTAL POST OFFICE PURCHASES 21000 1 3 41000 4 L21:1NEW YORK 377 L22:- 2002 CALENDAR YEAR SALES L23:- SHOWING PRODUCE WHO SOLD INTO AL-SCH-TRY MARKET AREA 14B981 L24:0PURCHASER TOWN/ BOOSTING DEALER CHRISTMAS HOTCAKE L25: POST OFFICE sLR DEALER NAME DEALER MARKET AREA L26: COMMUNITY NO LOCATIONS L27: 14B402 Nemith CHOLO Corp Latham NY 60 150 10 13 83 37 219 1 7 48 L28: 14B539 Fuccillo HUMBLE-MET Schenectady NY 58 145 2 6 7 73 13 77 3 16 L29: 14B561 Lazare DETPLUB I Albany NY 76 190 16 50 142 92 544 18 113 L30: TOTAL IN MARKET 194 485 2 32 70 298 142 840 1 28 177 L31:1NEW YORK 377 L32:- 2002 CALENDAR YEAR SALES L33:- SHOWING PRODUCE WHO SOLD INTO AL-SCH-TRY MARKET AREA 14B981 L34:0PURCHASER TOWN/ BOOSING DEALER CHRISTMAS HOTCAKE L35: POST OFFICE SLR DEALER NAME DEALER MARKET AREA L36: COMMUNITY NO LOCATIONS L37: 14B402 Nemith CHOLO Corp Latham NY 60 150 10 13 83 37 219 1 7 48 L38: 14B539 Fuccillo HUMBLE-MET Schenectady NY 58 145 2 6 7 73 13 77 3 16 L39: 14B561 Lazare DETPLUB I Albany NY 76 190 16 50 142 92 544 18 113 L40: TOTAL IN MARKET 194 485 2 32 70 298 142 840 1 28 177 Here I need to import the line numbers L01 thru L21 for "1BOSTON" data at the same time need to parse into each colums. Thanks very much . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text File Importing | Excel Discussion (Misc queries) | |||
Importing CSV file (saved as Text) into XL as Text -- over 60 colu | Excel Discussion (Misc queries) | |||
Importing text file, only option to edit existing file | Excel Discussion (Misc queries) | |||
Importing text file | Excel Discussion (Misc queries) | |||
Importing text file | Excel Discussion (Misc queries) |