LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

How can i read txt file then take parts of it to be put into excel

Hey all

 

I need to take a .txt file which is a log of sequences from a device and then be able to take out the specific sequences to be grouped together and analyzed either in excel of labveiw itself. Each line of the txt file is its own sequence with time stamp and specific command. it could be great if i could do the anlysis in real time as the the data is being written to the .txt file. I really am not sure where to begin as my first attempts with the file i/o vi's did not work. 

 

Thanks,

Triff

0 Kudos
Message 1 of 15
(2,612 Views)

Post your vi and the text file that you need to read and we could try to help.



------------------------------------------------------------------------------------------------------
Kudos are (always) welcome for the good post. 🙂
0 Kudos
Message 2 of 15
(2,610 Views)

 

 

Here is just something i kinda threw together real quick to just try to read the file then write it back to another txt file so i had something to work off.  I would like it the be written into excel though or be graphed with labview  and a sample of some of the code what i want to do is to really track time of events and be able to give some common statistics on them stdev, min, max sort of stuff but i need to be able to seperate them first.

 

-Triff

Download All
0 Kudos
Message 3 of 15
(2,606 Views)

Can anyone take another look at my topic i'm really stuck and am not sure where to head.

0 Kudos
Message 4 of 15
(2,577 Views)

To be honest, I really don't understand what it is that you're trying to do. What exactly are you trying to extract from the file? If the device is continously writing to the file, and has the file open, you won't be able to open it from LabVIEW unless the device closes the file. Even if you did, you wouldn't necessarily see the most recent content since the contents to be written may not be flushed out to disk at the moment you request the file write.

 

To write to Excel you need to use ActiveX. There's a couple of examples that ship with LabVIEW on writing a table to Excel. You could also go for the Report Generation Toolkit, depending on your level of need. There are also plenty of examples in the Excel thread.  NOTE: DO NOT POST QUESTIONS IN EXCEL THREAD.  Let me repeat that: DO NOT POST QUESTIONS IN THE EXCEL THREAD. Let's just say I'm being pro-active in my warning since some people seem to be incapable of reading all the posts in that thread that say DO NOT POST QUESTIONS IN THE EXCEL THREAD.

0 Kudos
Message 5 of 15
(2,570 Views)

The real problem i'm hgaving is trying to filter through the data after it is extracted from the .txt file i know how to write things back to excel its just being able to take each line have labview look at it and pull out what i ask so for example if i want two lines like this 

11: 1:41.155;  12;  Dspch:  SS4 Cmd ->    (FineFocus) Camera1:  Snapshot:  ZSTACK Start.

11: 1:41.237;  12;  Cmdr2: SS4 Cmd ->    (FineFocus) Camera1:  Snapshot:  ZSTACK End. 

and i can pull out every line like this by looking for the snapshopt+start command folled by snapshot+end command and subtract their time stamps to get the time it took for the process. and then be able to put all that into the excell file to plot the process times and look for outliers.  I don't really know how to sort through all this data.  And if it can't be real time so be it i can just read the txt file after the device has stopped processing and is no longer using or updating the file. I'm sorry if my information on my question is confusing.

 

Thanks,

Triff

0 Kudos
Message 6 of 15
(2,567 Views)

This might be way more than what you are asking for but...

 

Regular Expressions. They are like the most useful thing you ever learned about computer science.

 

Labview has a couple functions that do regular expressions (match regular expression, for one), depending upon the amount of power you require in processing the string. I recommend reading a good regular expressions tutorial.

 

If each line looks like what you posted. I'll even give you a hand.

 

Let's try matching each element in the string so:

 

11: 1:41.155;  12;  Dspch:  SS4 Cmd ->    (FineFocus) Camera1:  Snapshot:  ZSTACK Start.

 

\d{2} - match exactly two numbers...

 

or you cna do

 

\d+ - match one or more numbers

 

You can then make this a value using "Decimal String to Number"...

 

: 1:41.155;  12;  Dspch:  SS4 Cmd ->    (FineFocus) Camera1:  Snapshot:  ZSTACK Start.

 

\d*:?\d{1,2}\.\d+ - let's break this down

 

\d* means match match 0 or more number, that nabs the one, maybe it doesn't always exist. I'm accounting for this. You can do \d+ for 1 or more matches of a digit or \d{1,2} if we know it will always be either one or two digits.

:? means match 0 or 1 ':', which I dunno, may not be printed if the hour isn't there... you can drop the ? if it's always there.

\d{1,2} means it's either one or two digits. I imagine both digits are always printed (leading 0s are kept) so then you could do \d{2}

\. period is a wildcard special character so we have to escape it.

\d+ means get one or more digits.

so now we have 1:41.155. If we wanted to seperate hours and minutes we could have done multiple regular expressions to do so.

 

; 12;  Dspch:  SS4 Cmd ->    (FineFocus) Camera1:  Snapshot:  ZSTACK Start.

 

I'll leabe this to you...

 

Dspch:  SS4 Cmd ->    (FineFocus) Camera1:  Snapshot:  ZSTACK Start.

 

(Dspch|Cmdr2)

 

This matches the string Dspch or Cmdr2. If there are more possible values here, you can add them with another pipe... (Dspch|Cmdr2|ThirdOption). You can also nab this with \w+.

 

etc.

 

---

 

Sorting through all this data can be tricky. Let's say, for instance, you made a cluster for each line which has lots of the matches in it, as in each cluster has each kind of match in it, and there is one cluster for each line. You could now search in the data for the infromation you want by plugging that array of clusters into a for loop and matching values in each cluster against what you want to search for.

 

I did a simple example for search for a string in one of the fields in the attached. But you could make it so you could search and match over all of the fields or any specific field! Look at the code.

 

Good luck!

Message 7 of 15
(2,553 Views)

Thanks a lot majoris i think that this is the path i needed to be directed on, i am using labview 9 however and can't open  your code since i am using an older version than you.

 

Also yes all data is in the same format as the two lines i posted i also attached a txt file earlier in the thread with a bit more code.

 

i have the match regular expression vi but is there any other regular expression vi's that i could use and i understand the way to read the numbers with the coding you provided but is there a vi i can type that sort of coding into to select the time stamp? maybe you have already adressed this in the program you sent.  I feel like once i get this sorted out im home from just going to have an enormous case structure for the different commands.  My other worry is that the regular expression splits the string into three sub strings as you know which means that when i find the right command the timestamp will be in a different string do you see any easy ways to get around this since the timestamp is what im interested in for the commands to extract the response time.

0 Kudos
Message 8 of 15
(2,549 Views)

If we can get each line into an array, we can just cycle over the array looking for our timestamp for instance.

 

If we can't directly do that, but there are like endlines at the end of each row of data, we can first loop over the data looking for end lines. Then we can take the match from each endline to the next i.e.

 

.*\n

 

or something similar, and put that match into the next element into an array (using build array, or preferably by index/replacing into a preallocated array).

 

Anyways once you have an array, you can search on all these fields doing something like this:

 

 

regex example.png

 

I recommend putting all that stuff in the while loop into a VI, something like parse_line.vi. It can parse up and convert the row as needed into more meaningful blocks. And I'm sure you'll need that functionality in lots of places in your code. Lemme know if you have more specific questions.

0 Kudos
Message 9 of 15
(2,537 Views)

I appreciate the help I am not getting what i need however since i need to search for the command then go back for the timestamp what is happening is the before match is starting all the way at the begining of the data.  I was thinking about making this string into a 2d array and then using something like

for i=1:2000

if A(i,1)="Cmdr"

A(i)=B(j)

j=j+1

else  end

 

so that it will take out everyrow containing that word and place it in it's own array then i could just do it again moving from left to right with each command i want and then be able to pull out the timestamp's from each line that contains all the data i want the only thing is this will take pretty long since i need to be able to scan across all the colums of the array to find the words which needs to be done however many colums the data is placed into and i'm not sure if this language will translate into labview as i am still fairly new to it.

0 Kudos
Message 10 of 15
(2,524 Views)