Search/Extract Data w/in Text File

Hello again!  I am searching within a text file for the last row within the 
file. After I locate the row I need to separate the data and place into 
multiple cells on my worksheet.  The code I have is as follows:

Dim TextPath As String
Dim DelimSrchChar As String
Dim METtime As String

Dim DataBuffer As Variant
Dim NCData As Variant

Dim DelimPos As Integer
'===============================================

DelimSrchChar = ","

TextPath = "C:\Temp\Formatted_NC_SN1008.txt"

Open TextPath For Input As #1       'open text file for SN1008

    Do While Not EOF(1)             'go while not end of text file
        
        Line Input #1, NCData
            
        If EOF(1) Then
            DelimPos = InStr(NCData, DelimSrchChar)
            
            MsgBox DelimPos
            
            Application.Cells(1, 2) = NCData
                
        End If
   
    Loop
    
Close #1

End Sub

NCData looks like this:  000:03:42:53,3.40426,20.,18.9623.  I can get the 
data, but I am having trouble finding a method for parsing the data and 
placing the parsed data into multiple cells.

The problem:  I can use the InStr function to get the data to the left of 
the first delimiter as position 13 (i.e. 000:03:42:53) , but how can I get 
the data between delimiter positions 13 and 21 (i.e. 3.40426) and between 
delimiter positions 21 and 26 (i.e. 20.XX), and to the right of delimiter 
position 26 (i.e. 18.9623)?  The data sizes can change so they are not 
necessarily fixed.

Thanks you.

D.Parker
0
DParker1 (41)
6/21/2005 5:37:01 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
596 Views

Similar Articles

[PageSpeed] 30

Let's assume that you are parsing on the coma.  First read the data into a 
single column as text.  Then use the text to columns feature to do the 
parsing for you.

In the worksheet this is Data > Text to Columns..
In VBA its something like:

Selection.TextToColumns
-- 
Gary's Student


"D.Parker" wrote:

> Hello again!  I am searching within a text file for the last row within the 
> file. After I locate the row I need to separate the data and place into 
> multiple cells on my worksheet.  The code I have is as follows:
> 
> Dim TextPath As String
> Dim DelimSrchChar As String
> Dim METtime As String
> 
> Dim DataBuffer As Variant
> Dim NCData As Variant
> 
> Dim DelimPos As Integer
> '===============================================
> 
> DelimSrchChar = ","
> 
> TextPath = "C:\Temp\Formatted_NC_SN1008.txt"
> 
> Open TextPath For Input As #1       'open text file for SN1008
> 
>     Do While Not EOF(1)             'go while not end of text file
>         
>         Line Input #1, NCData
>             
>         If EOF(1) Then
>             DelimPos = InStr(NCData, DelimSrchChar)
>             
>             MsgBox DelimPos
>             
>             Application.Cells(1, 2) = NCData
>                 
>         End If
>    
>     Loop
>     
> Close #1
> 
> End Sub
> 
> NCData looks like this:  000:03:42:53,3.40426,20.,18.9623.  I can get the 
> data, but I am having trouble finding a method for parsing the data and 
> placing the parsed data into multiple cells.
> 
> The problem:  I can use the InStr function to get the data to the left of 
> the first delimiter as position 13 (i.e. 000:03:42:53) , but how can I get 
> the data between delimiter positions 13 and 21 (i.e. 3.40426) and between 
> delimiter positions 21 and 26 (i.e. 20.XX), and to the right of delimiter 
> position 26 (i.e. 18.9623)?  The data sizes can change so they are not 
> necessarily fixed.
> 
> Thanks you.
> 
> D.Parker
0
GarysStudent (1572)
6/21/2005 5:58:05 PM
Thank you for the response.  My delimiter is the ",", so I would be parsing 
on the comma, but are you suggesting to write to a cell and then write to the 
columns?  I would like to avoid the first action and go directly to placing 
the parsed text data in various cells.  I think its possible with this 
method, I would have to write the data to the cell and rewrite over that cell 
when the TextToColumns action is performed I guess.

D.Parker

"Gary's Student" wrote:

> Let's assume that you are parsing on the coma.  First read the data into a 
> single column as text.  Then use the text to columns feature to do the 
> parsing for you.
> 
> In the worksheet this is Data > Text to Columns..
> In VBA its something like:
> 
> Selection.TextToColumns
> -- 
> Gary's Student
> 
> 
> "D.Parker" wrote:
> 
> > Hello again!  I am searching within a text file for the last row within the 
> > file. After I locate the row I need to separate the data and place into 
> > multiple cells on my worksheet.  The code I have is as follows:
> > 
> > Dim TextPath As String
> > Dim DelimSrchChar As String
> > Dim METtime As String
> > 
> > Dim DataBuffer As Variant
> > Dim NCData As Variant
> > 
> > Dim DelimPos As Integer
> > '===============================================
> > 
> > DelimSrchChar = ","
> > 
> > TextPath = "C:\Temp\Formatted_NC_SN1008.txt"
> > 
> > Open TextPath For Input As #1       'open text file for SN1008
> > 
> >     Do While Not EOF(1)             'go while not end of text file
> >         
> >         Line Input #1, NCData
> >             
> >         If EOF(1) Then
> >             DelimPos = InStr(NCData, DelimSrchChar)
> >             
> >             MsgBox DelimPos
> >             
> >             Application.Cells(1, 2) = NCData
> >                 
> >         End If
> >    
> >     Loop
> >     
> > Close #1
> > 
> > End Sub
> > 
> > NCData looks like this:  000:03:42:53,3.40426,20.,18.9623.  I can get the 
> > data, but I am having trouble finding a method for parsing the data and 
> > placing the parsed data into multiple cells.
> > 
> > The problem:  I can use the InStr function to get the data to the left of 
> > the first delimiter as position 13 (i.e. 000:03:42:53) , but how can I get 
> > the data between delimiter positions 13 and 21 (i.e. 3.40426) and between 
> > delimiter positions 21 and 26 (i.e. 20.XX), and to the right of delimiter 
> > position 26 (i.e. 18.9623)?  The data sizes can change so they are not 
> > necessarily fixed.
> > 
> > Thanks you.
> > 
> > D.Parker
0
DParker1 (41)
6/21/2005 6:17:40 PM
You can avoid over-writing you input data if you wish.  

Try the process out manually to get the feel of it.  Then use the recorder.  
It will help define the many parameters that Selection.TextToColumns requires.

I hope this is of some value to you.
-- 
Gary's Student


"D.Parker" wrote:

> Thank you for the response.  My delimiter is the ",", so I would be parsing 
> on the comma, but are you suggesting to write to a cell and then write to the 
> columns?  I would like to avoid the first action and go directly to placing 
> the parsed text data in various cells.  I think its possible with this 
> method, I would have to write the data to the cell and rewrite over that cell 
> when the TextToColumns action is performed I guess.
> 
> D.Parker
> 
> "Gary's Student" wrote:
> 
> > Let's assume that you are parsing on the coma.  First read the data into a 
> > single column as text.  Then use the text to columns feature to do the 
> > parsing for you.
> > 
> > In the worksheet this is Data > Text to Columns..
> > In VBA its something like:
> > 
> > Selection.TextToColumns
> > -- 
> > Gary's Student
> > 
> > 
> > "D.Parker" wrote:
> > 
> > > Hello again!  I am searching within a text file for the last row within the 
> > > file. After I locate the row I need to separate the data and place into 
> > > multiple cells on my worksheet.  The code I have is as follows:
> > > 
> > > Dim TextPath As String
> > > Dim DelimSrchChar As String
> > > Dim METtime As String
> > > 
> > > Dim DataBuffer As Variant
> > > Dim NCData As Variant
> > > 
> > > Dim DelimPos As Integer
> > > '===============================================
> > > 
> > > DelimSrchChar = ","
> > > 
> > > TextPath = "C:\Temp\Formatted_NC_SN1008.txt"
> > > 
> > > Open TextPath For Input As #1       'open text file for SN1008
> > > 
> > >     Do While Not EOF(1)             'go while not end of text file
> > >         
> > >         Line Input #1, NCData
> > >             
> > >         If EOF(1) Then
> > >             DelimPos = InStr(NCData, DelimSrchChar)
> > >             
> > >             MsgBox DelimPos
> > >             
> > >             Application.Cells(1, 2) = NCData
> > >                 
> > >         End If
> > >    
> > >     Loop
> > >     
> > > Close #1
> > > 
> > > End Sub
> > > 
> > > NCData looks like this:  000:03:42:53,3.40426,20.,18.9623.  I can get the 
> > > data, but I am having trouble finding a method for parsing the data and 
> > > placing the parsed data into multiple cells.
> > > 
> > > The problem:  I can use the InStr function to get the data to the left of 
> > > the first delimiter as position 13 (i.e. 000:03:42:53) , but how can I get 
> > > the data between delimiter positions 13 and 21 (i.e. 3.40426) and between 
> > > delimiter positions 21 and 26 (i.e. 20.XX), and to the right of delimiter 
> > > position 26 (i.e. 18.9623)?  The data sizes can change so they are not 
> > > necessarily fixed.
> > > 
> > > Thanks you.
> > > 
> > > D.Parker
0
GarysStudent (1572)
6/21/2005 6:26:03 PM
It was of very great value to me!  Thank you very much, the macro recorder 
gave me a better feel.

Thanks again.

D.Parker

"Gary's Student" wrote:

> You can avoid over-writing you input data if you wish.  
> 
> Try the process out manually to get the feel of it.  Then use the recorder.  
> It will help define the many parameters that Selection.TextToColumns requires.
> 
> I hope this is of some value to you.
> -- 
> Gary's Student
> 
> 
> "D.Parker" wrote:
> 
> > Thank you for the response.  My delimiter is the ",", so I would be parsing 
> > on the comma, but are you suggesting to write to a cell and then write to the 
> > columns?  I would like to avoid the first action and go directly to placing 
> > the parsed text data in various cells.  I think its possible with this 
> > method, I would have to write the data to the cell and rewrite over that cell 
> > when the TextToColumns action is performed I guess.
> > 
> > D.Parker
> > 
> > "Gary's Student" wrote:
> > 
> > > Let's assume that you are parsing on the coma.  First read the data into a 
> > > single column as text.  Then use the text to columns feature to do the 
> > > parsing for you.
> > > 
> > > In the worksheet this is Data > Text to Columns..
> > > In VBA its something like:
> > > 
> > > Selection.TextToColumns
> > > -- 
> > > Gary's Student
> > > 
> > > 
> > > "D.Parker" wrote:
> > > 
> > > > Hello again!  I am searching within a text file for the last row within the 
> > > > file. After I locate the row I need to separate the data and place into 
> > > > multiple cells on my worksheet.  The code I have is as follows:
> > > > 
> > > > Dim TextPath As String
> > > > Dim DelimSrchChar As String
> > > > Dim METtime As String
> > > > 
> > > > Dim DataBuffer As Variant
> > > > Dim NCData As Variant
> > > > 
> > > > Dim DelimPos As Integer
> > > > '===============================================
> > > > 
> > > > DelimSrchChar = ","
> > > > 
> > > > TextPath = "C:\Temp\Formatted_NC_SN1008.txt"
> > > > 
> > > > Open TextPath For Input As #1       'open text file for SN1008
> > > > 
> > > >     Do While Not EOF(1)             'go while not end of text file
> > > >         
> > > >         Line Input #1, NCData
> > > >             
> > > >         If EOF(1) Then
> > > >             DelimPos = InStr(NCData, DelimSrchChar)
> > > >             
> > > >             MsgBox DelimPos
> > > >             
> > > >             Application.Cells(1, 2) = NCData
> > > >                 
> > > >         End If
> > > >    
> > > >     Loop
> > > >     
> > > > Close #1
> > > > 
> > > > End Sub
> > > > 
> > > > NCData looks like this:  000:03:42:53,3.40426,20.,18.9623.  I can get the 
> > > > data, but I am having trouble finding a method for parsing the data and 
> > > > placing the parsed data into multiple cells.
> > > > 
> > > > The problem:  I can use the InStr function to get the data to the left of 
> > > > the first delimiter as position 13 (i.e. 000:03:42:53) , but how can I get 
> > > > the data between delimiter positions 13 and 21 (i.e. 3.40426) and between 
> > > > delimiter positions 21 and 26 (i.e. 20.XX), and to the right of delimiter 
> > > > position 26 (i.e. 18.9623)?  The data sizes can change so they are not 
> > > > necessarily fixed.
> > > > 
> > > > Thanks you.
> > > > 
> > > > D.Parker
0
DParker1 (41)
6/21/2005 6:33:04 PM
Reply:

Similar Artilces:

Problem with data using IF and Nested IF statements possibly???
Afternoon All I am attempting to analyse data from multiple worksheets from numerous people the incoming data all has one thing in common column A this is a certain frequency a job is done. The problem is that there are many ways of entering the data ie 12 months or 52 weeks or 365 days all essentially meaning the same thing. My idea is to collate the data in col A and then using the Helper column as the standard frequencies ie if cell A1 = 12 months, closes frequency in helper is 52 weeks therefore value in C1 = 52 weeks. By using an IF statement I can change the value of one frequen...

transfer data from one worksheet to another
Can anyone please help I have a worksheet that contains 12 columns and a variable number of rows. the 4 main columns Col A = Identity Number Col B = Surname Col C = First name Col D = Department etc There is a separate worksheet for each department.There are 6 departments and I need to transfer the identity number,names and details held in the remaining columns to a separate worksheet depending upon what department the person belongs to. The data on the master list is not sorted in any particular order. Is there a way to copy this data to each sheet without having to sort and then copy/paste t...

Combining Text and numbers from two cells
Hi all please help i have a cell with a number such as 6000 (a1) and another cell with a text aaa- and i would like to combine them in another cell to make aaa-6000, could somebody please help me with a formula? thanks Andrew -- koba ------------------------------------------------------------------------ koba's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28639 View this thread: http://www.excelforum.com/showthread.php?threadid=492631 Hi! Try this: A1 = 6000 B1 = aaa =B1&"-"&A1 Biff "koba" <koba.1zwtbm_1134361504.8695...

Calendar from Excel Data
I currently keep a fairly complex calendar as an Excel spreadsheet. I would like to find a way to automatically generate a normal-looking month-by- month calendar from this. I really don't need the month-by-month calendar to show much of the complexity, just the events and times and possibly locations in the correct dates on the calendar. I did a little looking on the web and saw many calendar systems, but it wasn't clear to me which might be able to import my data from Excel. --David ...

allowing M05 to convert your file twice!! BAAAD!
In an attempt to resolve some of my problems, I reinstalled MS Money. Now, I already converted my .mny file, however since it installed, it wanted to convert my file without even looking at it (to see it already was). I thought it would; but, it converted it anyway and all of the transactions were backwards (debits = credits and vice versa). That may be why people have wrong entries! ...

Free/Busy data
When I try to set upa meeting and try to save and close it outlook gives me an error saying " Unable to update public free/busy data". It also gives me this error when I exit out of outlook. Also it seems like all my contacts are gone from the list. Any suggestion? ...

Search last 4 digits in an (account number)
I store bank accunt and credit card numbers in various Contact Notes. For example, a credit card # as xxxxxxxxxxxx1234. Outlook contact search would not find this contact if I enter the search for "1234". Is there a way to do this in Outlook 2007? TIA PS - this search works fine in Outlook Mobile. outlook searches from the beginning - so it will only work if you use xxxxxxxxxxxx 1234. Other search tools (like google desktop) may find it. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: htt...

Look for: Search In => Choose folders=> Search subfolders not working
When I search for an old email using Outlook's Look for: box and specify which folders to search in via the "Choose folders=>Search subfolders" checking a folder does not actually cause any subfolders to be checked. Unless there is some other setting and/or option hidden away somewhere, the "Search subfolders" check box is not working. I must manually check all the desired subfolders. What am I doing wrong? -- Regards, Peter Sale Santa Monica, CA USA To email me, just pull 'my-leg.' ...

Importing CSV Files into Microsoft Money
Is there an easy way to import CSV or Excel Files into MS Money 2003? My bank screws up the Payee when I electronically download from their website, so I'm looking for a way to fix this then import into MS Money 2003. Any ideas? Thanks! In Money 2004, you can have Money use your user-entered payee names instead of overwriting them with the downloaded names in the statement (in Tools-Options-Online Services-Replace downloaded payee...). I enter the transaction how I prefer it, and when Money downloads the appropriate transaction, my name is the one that remains after the matching....

Importing pst files into Outlook
I needed to reformat the hard disk of my computer. To save the data in Outlook I exported the most important folders into a CD-R. When I reinstalled all the software and tried to import the pst files fron the CD-R I get 2 messages: "Properties for this information service must be defined prior to use" and then "File access is denied. You do not have the permission required to access the file E:\XX.pst" Is anyway to recuperate the date on pst files??? (calendar, input, contacts,etc.) What am I doing wrong? Thanks for your help NP On 07 May 2004 15:10, "NP" ...

Text in Report to be Justified
Hello I am using Access 2003. I have a field L1 with data type Memo. In the report the alignment option available is Left, Center or Right. How to have the text alignment in field L1 to be justified so that the text in my report are justified. Thanks in advance. Biju/Kuwait What type of control are you using on your report? I also have an option of "Distribute" which will do what you are asking. Are you using a text box? -- Hope that helps! RBear3 .. "Biju George" <bijuleena71@hotmail.com> wrote in message news:u4%23pnlyiHHA.4112@TK2MSFTNGP04.phx.gbl... ...

Why do these symbols #### appear after correcting data in a cell?
After correcting or updating informartion entered into a cell, then going to next cell, all of these appear in the previous cell instead of the information entered & saved? Jim That usually happens when the column is not wide enough to hold the information in that cell. You will get this when the cell content is a number. Post back if you need more. HTH Otto "Jim" <Jim@discussions.microsoft.com> wrote in message news:26D6FB89-3BEA-4117-86EB-4F23A0077532@microsoft.com... > After correcting or updating informartion entered into a cell, then going > to &g...

Extracting Data from Word 2007 forms
I created a form for our agents to reduce the amount of paperwork they have to carry with them. I decided to use a form in Word 2007. Now they're wanting to be able to extract data from these forms to upload to a database. I am having trouble finding a good way to extract this data in office 2007. My question is what is the best way to go about doing this? I've seen discussions on how to do it from office 2003 but can't seem to use the same procedure in '07. Do I need to create a Macro, VBA code, or is there some simpler way to do it that I've overlooke...

How do I save a narrated (sound embedded) PPT07 file in SCORM form
I want to allow my company's LMS (Learning Management System) to store, push, and report viewership of narrated (Isound embedded) PPT 2007 files. The LMS has two APIs for content: SCORM 1.2 or AICC. Has anyone found a way to store narrated PPTs (or take intermediate steps) to create SCORM-format output so an LMS can manipulate it? ...

Are there any good tools out there for importing data?
I am looking for a tool that will give me the DBA abilities I need to work with MSCRM. I could do some of this with Access, SQL, etc., but they say you can't even update records via other tools (not utilizing the APIs). Some of the basics that I need to do are: 1. Import contacts and link them to existing accounts (on import) 2. Import contacts and accounts together (and link them) 3. Do mass updates based on a query. For example, query all records that have "georgia" as the state and change to "GA". This is just one example of the many DBA activities. Does anyone ...

Search & replace in formulae
Is it possible to do a search and replace which includes the cell formula contents ? I have a workbook that has cells which link to the contents of a cell in another workbook and I'd like to know if the naming has to remain constant or if I can replace a part of the name periodically. ie each year. thanks "Iain Rhodes" <iain@pricejam.com> wrote in message news:8ec201c49682$858c5d20$a501280a@phx.gbl... > Is it possible to do a search and replace which includes > the cell formula contents ? I have a workbook that has > cells which link to the contents of a cel...

CSV File, change separator sign
I can not change separator sign creating CSV file in office2007 running on XP PRO. I want to have , as separator defalt is ; I have changed in control panel nationals setting but it still does not give me csv fil whit , as separator it keeps give me ; as separator. Can anyone tell me how to solve this. I've run into this myself, and eventually found it fastest just to use NotePad or EditPad. 1. Export your .CSV doc 2. Open it in NotePad 3. CTRL-H to activate Search/Replace feature Search: ; Replace: , 4. Save the .CSV file. -- "Actually, I *am* a rocket scientist." -- JB...

Searching for Newsgroups
I subscribe to a Usenet service for accessing various news groups. After I download all the newsgroups from that server I can not find a way to search for the newsgroups I have interest in. When I put something like "RV" in the search box on the top right corner, it searches all of my Emails rather than just the list of groups that had "RV" in the title. Help tells me that there is a " Display newsgroups containing" box but for the life of me I can't find it. Any thoughts out there? I am running Entourage 12.2.3 Dunc Click the bar in th...

Normalizing data
I have an excel file which has over 5000 lines and will continue to add new data. I would like to convert this into an Access database. The problem is I'm not sure which field is my primary key as these fields have redundant data lines. I'm guessing that normalization is required but not quite sure how to go about it. Here are some sample data lines: Dealers Start Date Org Amt CUSIP Security Class BS 2/13/2008 22 07388YAE2 BSCMS 07-PW16 A4 Citi 2/13/2008 10 07388YAE2 BSCMS...

Search engine optimization
Hello all! My question today regarding this issue is: I have noticed some sites have the following on their sites "Source". meta name= "robots" content="Index, follow" meta name= "revisit-after" content="15 days" or (30 days) What does this function accomplish as far as search engines go? and will it really imporve your results ( rankings) Like for instance "robots" "index, all" what is the difference between this and saying "robots" "index, follow" I'm a bit confused about this. Can someone ...

combining data
I have a large amount of data formatted in the same way in three to four different worksheets. Each sheet holds about 1000+ rows of data. Is there a way to combine the data from all four sheets into one? Copy and Paste? Or do you need some code for this as you will be doing it regularly? -- Michael Hopwood (Phobos) "frank" <mrfrank73@aol.com> wrote in message news:018401c3a94e$75ce1850$a401280a@phx.gbl... > I have a large amount of data formatted in the same way in > three to four different worksheets. Each sheet holds > about 1000+ rows of data. Is there a...

Outlook 2000 w/XP
We just got new email service. I have successfully configured every computer in the office with exception to one XP machine; I cannot find option to change email properties. Two other XP machines I navigate "tools-Email accounts". On this particular machine (with outlook 2000 ver 9.0.0.2711) I cannot find similar options....please help. Sincerely, Joe Mangano You may have a Tools > Services command, if so select this and then add a new service of type Internet Email. "JoeMan" <jmangano@naturestonefloors.com> wrote in message news:010f01c3d921$f3baf550$a10...

Filtering data by custom fields in pivot tables from Visual Report
I have recently upgraded to MS Project 2007 and like the visual reporting feature, except I really need to filter and group the resulting pivot table in Excel by custom fields. For example, for task usage, I cannot filter my pivot table to show only the tasks that have costs > $0, thus my table ends up showing too many lines and a lot of 0's. I would also like to group by department/ function which I put in a custom text field. How do I do this? I saw related posts about saving the file as an Access database and then creating the pivot table by connecting to the datab...

Can I search identical fields from multiple tables?
I have a database to log details about staff in my school. I have a tables for staff details, courses, absence, educational visits etc. In each table I have a date field to show when the member of staff is not present. I want to produce a query to search all tables by entering a date - e.g. to bring back all results for staff who are not in school on a certiain day because they are either ill, on a course or on an educational visit. The query will work by entering a date once - it will then search all the other tables and bring back the results. Is this possible? Do I restructure? ...

What does outlook search when you search Contacts ?
When you do a search in oulook for contacts, what is the criteria that outlook searches? Or I guess how does outlook search? Why is it not consistant? The reason I am asking is that we have our customers contacts in public folders. In our customers we have 6 contacts for XYZ Foods. If you search in outlook for just "XYZ" it only comes back with 4 of the six. If you do a search for "XYZ Foods" it returns with all 6. And if you just entered "Foods" no contacts were found. And we actually have a few companies in our contacts who's names are a two part name ending...