How to read a specific data from txt file and save into excel

[vijay0507]

Hi all,

im new to Labview, recently I have started a project work on Labview, where I have extract some measurement file (.txt files) from the Measurement system.Now I want to take a specific data from the .txt file ( for example it contains start date, time interval and serial number of the device, end time) now I want to read only start time, serial number and end time and time difference between start and end time) which I have to write into an excel sheet. Could anyone help me with the problem. 

looking forward to hear from you soon

regards 

vkg

[GerdW]

Hi vijay,

---

@vijay0507 wrote:

recently I have started a project work on Labview, where I have extract some measurement file (.txt files) from the Measurement system.Now I want to take a specific data from the .txt file

Could anyone help me with the problem. 

---

No, nobody can help you with your task - unless you provide more information!

We don't know:

• your "measurement system"
• the formatting of the data in your "measurement text file"
• the specific formatting of the timestamps inside that text file
• the formatting of the resulting "Excel sheet"

What have you tried so far?

Where are you stuck?

[vijay0507]

Hi GerdW,

Thank you so much for you response.

Actually, I have a .txt file with measuring result from an instrument + some metadata about the measurment.

I am trying to automate the export of the data from .txt to .csv file through LabVIEW.

The thing is that I need specific data from .txt file (Serial number, start date, start time, end time, time difference

I attached the .txt file of the raw data here. Could you please check it. 

The format i would like to get it in .csv file is "Serial number of the device", "Start Date", "Start time", "End Time" and "Time Difference of the measurement"

In python i know how to exclude/run on the data and break it with loops and "if"s, but working with labview is different and i suspect there is a built in function to do it for me more or less. any suggestions?

I attach here my VI, however i just played with it so far - i used a few  arrays to pick my rows and order them in the way i want, just to present it here. I do not think this is the way to go.

Could you please check it let me know me your valuable suggestion.

regards

VGK

[Bob_Schor]

Hello, Vijay.  Thank you for including data for us to see.

The Good News is that your data file is a Tab-delimited file (a .csv, but with Tabs instead of Commas, which is what LabVIEW's "Read Delimited Spreadsheet", itself, uses as the default).

So if you open your Test text file and read it, one line at a time, as a file of String, you will get (for the first line, for example) an Array with "[<TimeStamp>]:", "[Logger]:", "[Text Log]:", and "Created", a line with 4 entries.

But not all the lines have 4 entries!  Also, when you read "one line at a time", you need to keep track of the starting and stopping place in the File Reads.  Fortunately, LabVIEW provides help for this -- do you know about Shift Registers, which work within While and For loops to save an output value (like "mark after read") and "shift" it back to the input side for the next use as "start of read offset", so you can easily get the value of each line as an array of Strings.

Now you need to decide what you want to do with these Strings.  Do you want to write them all back to your output file?  Do you want to make changes?  Are there "spacer" (or "other headers") that you want to put in the Output file?

As we say, "You Pays Your Money and You (and LabVIEW) Makes Your Choice" -- or LabVIEW should help you do just what you tell it you want to do, so look at your data, and decide how you want to output it.

Note if you read it as separate 1D Array of Strings, you can similarly write it as separate 1D Array of Strings (using, of course, Write Delimited Spreadsheet).

Bob Schor

[vijay0507]

Dear Bob,

Thank you so much for your kind suggestion. I have consider your suggestion and built a program to extract specific data from .txt file. Until extracting information I have sucessfully done. Now I am trying to save that specific information into :csv file, but unfortunately, not getting the data into desired format.

Currently I am getting data in a single column as follows

start time, serial nr., end time

10:52

53200561

11:01

09:59

53200563

10:08

Actually I am looking forward to get the data in .csv file below format in 3 columns

Start Time     Serial Nr.      End Time

10:52            53200561       11:01

09:59            53200563       10:08

Could you please let me know your suggestion getting data in different column. For your reference, I am attaching my vi  and desired result in .csv file. Looking forward to hear from you soon

Thank

vgk

[Bob_Schor · Accepted solution]

Hello, vgk.

I can "talk you through" the process of getting your data from a "single column" into a "header line" and multiple "3-column data lines".  However, you have a much more serious problem to work on, namely the horrible "style" of your LabVIEW code.  I'll offer some starting tips, but will make an observation of how to tell "Good LabVIEW code" from "horrible LabVIEW code" (and, I regret to say, you "FINAL_TEST.vi" falls in the latter bucket).

Good LabVIEW code is well laid-out, with (almost) all wires running straight, with few bends, and never running backwards.  All VIs and functions that use the Error Line are placed on the Error Line (Experts can relax this requirement).  Sequences are (almost) never used, and Stacked Sequences are definitely never used.

Care is taken to lay out the code in "logical order", first things first (and on the left).  "Trivial details" are "hidden" in sub-VIs with enough information (by means of a VI Icon or other (text) identifier so the viewer understands the flow of data and how the data are transformed as it flows (again, from left to right).

If you want to try to "fix" your code, I'd suggest printing it out (so you can scribble on the paper) and "follow the flow of data".  The first thing that happens is you do a Write Delimited Spreadsheet.  Get rid of the decoration and unnecessary "Write Header", and start using the Error Line!  Do NOT branch the output -- subsequent writes will all use the same "wire".

Now figure out "what happens next".  It should "sit" on the same Error Line you just created!  Keep going, following the data, and get a (probably pretty long) sequential list of VIs.  Remember to "follow the data flow", and get rid of Sequences and Frames (they do have a use, but not here).

You'll probably end up with something that "won't fit on a screen".  Is any of this suitable for "burying in a subVI", that is, does it do some simple "nameable" task with the data coming in, including "getting rid of it" (by writing it to disk)?  There's a "quick and dirty" way to turn some code into a sub-VI, but I urge you to actually start with "New VI", place down the Inputs and Outputs on the Front Panel (don't forget Error In and Error Out, wired to the lower corners of the default 4-2-2-4 Connector Pattern) and copy the code you want into the Block Diagram.

But how to organize the data you have into the File you want?

The first element is a comma-separated array of Strings (used for the Header).  Pass it through the function "Spreadsheet String to Array" (on the String Palette) and it should become an array of the three column headers.  If you write this array to "Write Delimited Spreadsheet", you'll get a Delimited Spreadsheet with the Headers in the right place.  [This may seem obvious, but wait, there's more ...]

You now have 6 strings which you want to become the first two rows of three elements.  Can you gather them into a 1D "Array of Strings" having 6 elements?  Now look at the Array functions -- you have a 1 x 6 Array, and you want to turn this into a 2 x 3 Array.  This is called a "Reshape" operation.

Now you just write this 2D array (two rows of 3 columns) to the same Delimited Spreadsheet.  And you have what you want.

Bob Schor