| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with data importing from txt file to excel programmatically
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
|
|||
|
|||
|
Help with data importing from txt file to excel programmatically
Here You have an example.
But remember, I really need to have your txt file to test on it and more information how Ypu want to extract data from this file. I don't think your copied and pasted data have the same structur as is in txt file. What is a sign to recognize column (tab, 4 spaces, ";", "-")? Sub MetroDatail() 'ExtractDetail "1BOSTON" ExtractDetail "1NEWYORK" End Sub Sub ExtractDetail(sFindText As String) Dim strTemp As String, sinput_path As String Dim REGIONFILE As String Dim i As Long 'number of row Dim InFile As Long 'number of file sinput_path = "C:\" REGIONFILE = "test.TXT" InFile = FreeFile() Open sinput_path & REGIONFILE For Input Access Read As InFile Do While Not EOF(InFile) Line Input #InFile, strTemp If (InStr(1, strTemp, sFindText) 0) Then i = i + 1 ThisWorkbook.Worksheets(3).Range("A" & i) = strTemp End If Loop Close #InFile End Sub -----Original 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 . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with data importing from txt file to excel programmatically
THANKS
-----Original Message----- Here You have an example. But remember, I really need to have your txt file to test on it and more information how Ypu want to extract data from this file. I don't think your copied and pasted data have the same structur as is in txt file. What is a sign to recognize column (tab, 4 spaces, ";", "-")? Sub MetroDatail() 'ExtractDetail "1BOSTON" ExtractDetail "1NEWYORK" End Sub Sub ExtractDetail(sFindText As String) Dim strTemp As String, sinput_path As String Dim REGIONFILE As String Dim i As Long 'number of row Dim InFile As Long 'number of file sinput_path = "C:\" REGIONFILE = "test.TXT" InFile = FreeFile() Open sinput_path & REGIONFILE For Input Access Read As InFile Do While Not EOF(InFile) Line Input #InFile, strTemp If (InStr(1, strTemp, sFindText) 0) Then i = i + 1 ThisWorkbook.Worksheets(3).Range("A" & i) = strTemp End If Loop Close #InFile End Sub -----Original 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 data from a DataCom file into an Excel workbook | Excel Discussion (Misc queries) | |||
| Importing data from an excel file to another with figures and lett | Excel Discussion (Misc queries) | |||
| importing text file data into excel | Excel Discussion (Misc queries) | |||
| copying data from an unopened file programmatically | Excel Discussion (Misc queries) | |||
| Importing data from an Excel file on a web server using ADO/VBA | Excel Programming | |||
Linear Mode
