| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need some help with in coding!!!! text file importing
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 1BOSTON 1 - 2002 CALENDAR YEAR SALES - SHOWING PRODUCE WHO SOLD INTO ACTON MARKET AREA 12B202 0PURCHASER TOWN/ BOOSTING DEALER CHRISTMAS HOTCAKE POST OFFICE SLR DEALER NAME DEALER MARKET AREA COMMUNITY NO LOCATIONS 12B202 Acton MERCY-KRANK In Acton MA 57 704 5 13 41 116 23 622 10 34 TOTAL IN MARKET 57 704 5 13 41 116 23 622 10 34 0 12B073 Clark & White Inc Newton MA 1 12 1 2 12B386 Sentry HEART-FORD S Medford MA 1 12 2 3 3 81 1 1 5 12B491 North Shore D-T Inc Peabody MA 1 1 1BOSTON 2 - 2002 CALENDAR YEAR SALES - SHOWING PRODUCE WHO SOLD INTO ACTON MARKET AREA 12B202 0PURCHASER TOWN/ BOOSTING DEALER CHRISTMAS HOTCAKE POST OFFICE SLR DEALER NAME DEALER MARKET AREA COMMUNITY NO LOCATIONS 12D487 Gervais MERCY-KRANK Lowell MA 2 500 2 999999 UNKNOWN 1 250 1 TOTAL POST OFFICE PURCHASES 21000 1 3 41000 4 1NEW YORK 377 - 2002 CALENDAR YEAR SALES - SHOWING PRODUCE WHO SOLD INTO AL-SCH-TRY MARKET AREA 14B981 0PURCHASER TOWN/ BOOSTING DEALER CHRISTMAS HOTCAKE POST OFFICE sLR DEALER NAME DEALER MARKET AREA COMMUNITY NO LOCATIONS 14B402 Nemith CHOLO Corp Latham NY 60 150 10 13 83 37 219 1 7 48 14B539 Fuccillo HUMBLE-MET Schenectady NY 58 145 2 6 7 73 13 77 3 16 14B561 Lazare DETPLUB I Albany NY 76 190 16 50 142 92 544 18 113 TOTAL IN MARKET 194 485 2 32 70 298 142 840 1 28 177 1NEW YORK 378 - 2002 CALENDAR YEAR SALES - SHOWING PRODUCE WHO SOLD INTO AL-SCH-TRY MARKET AREA 14B981 0PURCHASER TOWN/ BOOSING DEALER CHRISTMAS HOTCAKE POST OFFICE SLR DEALER NAME DEALER MARKET AREA COMMUNITY NO LOCATIONS 14B402 Nemith CHOLO Corp Latham NY 60 150 10 13 83 37 219 1 7 48 14B539 Fuccillo HUMBLE-MET Schenectady NY 58 145 2 6 7 73 13 77 3 16 14B561 Lazare DETPLUB I Albany NY 76 190 16 50 142 92 544 18 113 TOTAL IN MARKET 194 485 2 32 70 298 142 840 1 28 177 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need some help with in coding!!!! text file importing
Did you try importing it to Access?
|
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need some help with in coding!!!! text file importing
Sudhendra
Create an array with all of the starting points for the fields, like this Dim arrStart As Variant arrStart = Array(1, 59, 86, 115, 150, 169, 201, 450, 650, 850, 932) Make the last element in the array the length of the string. So in this example the string is 932 characters long and the fields start at positions 1, 59, 86, ... 850 for ten total fields. Then you can loop through the array and parse out the data like For i = LBound(arrStart) To UBound(arrStart) - 1 Sheet1.Range("a1").Offset(0, i).Value = _ Mid(y, arrStart(i), arrStart(i + 1) - arrStart(i)) Next i to put each field in a separate column. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "SUDHENDRA" wrote in message ... 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 1BOSTON 1 - 2002 CALENDAR YEAR SALES - SHOWING PRODUCE WHO SOLD INTO ACTON MARKET AREA 12B202 0PURCHASER TOWN/ BOOSTING DEALER CHRISTMAS HOTCAKE POST OFFICE SLR DEALER NAME DEALER MARKET AREA COMMUNITY NO LOCATIONS 12B202 Acton MERCY-KRANK In Acton MA 57 704 5 13 41 116 23 622 10 34 TOTAL IN MARKET 57 704 5 13 41 116 23 622 10 34 0 12B073 Clark & White Inc Newton MA 1 12 1 2 12B386 Sentry HEART-FORD S Medford MA 1 12 2 3 3 81 1 1 5 12B491 North Shore D-T Inc Peabody MA 1 1 1BOSTON 2 - 2002 CALENDAR YEAR SALES - SHOWING PRODUCE WHO SOLD INTO ACTON MARKET AREA 12B202 0PURCHASER TOWN/ BOOSTING DEALER CHRISTMAS HOTCAKE POST OFFICE SLR DEALER NAME DEALER MARKET AREA COMMUNITY NO LOCATIONS 12D487 Gervais MERCY-KRANK Lowell MA 2 500 2 999999 UNKNOWN 1 250 1 TOTAL POST OFFICE PURCHASES 21000 1 3 41000 4 1NEW YORK 377 - 2002 CALENDAR YEAR SALES - SHOWING PRODUCE WHO SOLD INTO AL-SCH-TRY MARKET AREA 14B981 0PURCHASER TOWN/ BOOSTING DEALER CHRISTMAS HOTCAKE POST OFFICE sLR DEALER NAME DEALER MARKET AREA COMMUNITY NO LOCATIONS 14B402 Nemith CHOLO Corp Latham NY 60 150 10 13 83 37 219 1 7 48 14B539 Fuccillo HUMBLE-MET Schenectady NY 58 145 2 6 7 73 13 77 3 16 14B561 Lazare DETPLUB I Albany NY 76 190 16 50 142 92 544 18 113 TOTAL IN MARKET 194 485 2 32 70 298 142 840 1 28 177 1NEW YORK 378 - 2002 CALENDAR YEAR SALES - SHOWING PRODUCE WHO SOLD INTO AL-SCH-TRY MARKET AREA 14B981 0PURCHASER TOWN/ BOOSING DEALER CHRISTMAS HOTCAKE POST OFFICE SLR DEALER NAME DEALER MARKET AREA COMMUNITY NO LOCATIONS 14B402 Nemith CHOLO Corp Latham NY 60 150 10 13 83 37 219 1 7 48 14B539 Fuccillo HUMBLE-MET Schenectady NY 58 145 2 6 7 73 13 77 3 16 14B561 Lazare DETPLUB I Albany NY 76 190 16 50 142 92 544 18 113 TOTAL IN MARKET 194 485 2 32 70 298 142 840 1 28 177 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need some help with in coding!!!! text file importing
THANKS for replying, I dont want that step (Access) to be
involved... -----Original Message----- Did you try importing it to Access? . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need some help with in coding!!!! text file importing
THANKS
-----Original Message----- Sudhendra Create an array with all of the starting points for the fields, like this Dim arrStart As Variant arrStart = Array(1, 59, 86, 115, 150, 169, 201, 450, 650, 850, 932) Make the last element in the array the length of the string. So in this example the string is 932 characters long and the fields start at positions 1, 59, 86, ... 850 for ten total fields. Then you can loop through the array and parse out the data like For i = LBound(arrStart) To UBound(arrStart) - 1 Sheet1.Range("a1").Offset(0, i).Value = _ Mid(y, arrStart(i), arrStart(i + 1) - arrStart (i)) Next i to put each field in a separate column. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "SUDHENDRA" wrote in message ... 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 1BOSTON 1 - 2002 CALENDAR YEAR SALES - SHOWING PRODUCE WHO SOLD INTO ACTON MARKET AREA 12B202 0PURCHASER TOWN/ BOOSTING DEALER CHRISTMAS HOTCAKE POST OFFICE SLR DEALER NAME DEALER MARKET AREA COMMUNITY NO LOCATIONS 12B202 Acton MERCY-KRANK In Acton MA 57 704 5 13 41 116 23 622 10 34 TOTAL IN MARKET 57 704 5 13 41 116 23 622 10 34 0 12B073 Clark & White Inc Newton MA 1 12 1 2 12B386 Sentry HEART-FORD S Medford MA 1 12 2 3 3 81 1 1 5 12B491 North Shore D-T Inc Peabody MA 1 1 1BOSTON 2 - 2002 CALENDAR YEAR SALES - SHOWING PRODUCE WHO SOLD INTO ACTON MARKET AREA 12B202 0PURCHASER TOWN/ BOOSTING DEALER CHRISTMAS HOTCAKE POST OFFICE SLR DEALER NAME DEALER MARKET AREA COMMUNITY NO LOCATIONS 12D487 Gervais MERCY-KRANK Lowell MA 2 500 2 999999 UNKNOWN 1 250 1 TOTAL POST OFFICE PURCHASES 21000 1 3 41000 4 1NEW YORK 377 - 2002 CALENDAR YEAR SALES - SHOWING PRODUCE WHO SOLD INTO AL-SCH-TRY MARKET AREA 14B981 0PURCHASER TOWN/ BOOSTING DEALER CHRISTMAS HOTCAKE POST OFFICE sLR DEALER NAME DEALER MARKET AREA COMMUNITY NO LOCATIONS 14B402 Nemith CHOLO Corp Latham NY 60 150 10 13 83 37 219 1 7 48 14B539 Fuccillo HUMBLE-MET Schenectady NY 58 145 2 6 7 73 13 77 3 16 14B561 Lazare DETPLUB I Albany NY 76 190 16 50 142 92 544 18 113 TOTAL IN MARKET 194 485 2 32 70 298 142 840 1 28 177 1NEW YORK 378 - 2002 CALENDAR YEAR SALES - SHOWING PRODUCE WHO SOLD INTO AL-SCH-TRY MARKET AREA 14B981 0PURCHASER TOWN/ BOOSING DEALER CHRISTMAS HOTCAKE POST OFFICE SLR DEALER NAME DEALER MARKET AREA COMMUNITY NO LOCATIONS 14B402 Nemith CHOLO Corp Latham NY 60 150 10 13 83 37 219 1 7 48 14B539 Fuccillo HUMBLE-MET Schenectady NY 58 145 2 6 7 73 13 77 3 16 14B561 Lazare DETPLUB I Albany NY 76 190 16 50 142 92 544 18 113 TOTAL IN MARKET 194 485 2 32 70 298 142 840 1 28 177 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 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) | |||
| Help Importing Text File! | New Users to Excel | |||
| Importing text file | Excel Discussion (Misc queries) | |||
Linear Mode
