Selective read of txt file to csv

[Aqua-]

Hi,

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

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

The thing is that some rows in the .txt file are not placed in the order i would like them to be in + some rows include data that I dont want to take to the .csv file.

I attached the .txt file of the raw data here.

An example for a row that is out of place is the "NUMBER_OF_SETS" row which appear after the columns' headers, where i want it to appear before them, and right after the "NUMBER_OF_FIELDS" row.

An example for rows that i dont want to take to the .csv file is the "BEGIN_DATA_FORMAT", "END_DATA_FORMAT", "BEGIN_DATA",  and "END_DATA" rows.

The format i would like to get is all the meta data showing first, with the number of sets placed right after the number of fields, then a blank row and then the columns' headers and the data without the "BEGIN_DATA", etc. rows.

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 sub 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.

Ideally, I could use the "BEGIN_DATA_FORMAT", "END_DATA_FORMAT", etc. rows to create an indicator for where to make a blank row and sort the data.

The reason I dont want to use specific row numbers is quite obvious - my next measurment tool might include/exclude extra metadata rows, which will render the code unusable.

Thanks for helping

[crossrulz]

You can use a FOR loop to iterate through all of the items and do whatever with the data using a case structure based on the first item in a row.  I would have a cluster for the header data to only include the information you want and then an array for the data.  You can then build up the file in whatever order you want.

[MrMattPayne]

I mean, depending how variable the files will be and what kind of control you want (what fields you want and don't want), your answer will change, but here's something quick and dirty based on the following criteria:

• All the metadata info comes before the data and starts with "ORIGINIATOR"
• Skip blank rows
• Column header will be between "BEGIN_DATA_FORMAT" and "END_DATA_FORMAT", but don't include those rows
• Data will be between "BEGIN_DATA" and "END_DATA", but don't include those rows

See how that works. I just get the different parts based on the criteria that define them, then combine them all with a Build Array with the "Concatenate Inputs" option checked.

Also your loop had no timing, and would max out your CPU. You always want some kind of a timing element to prevent that, so I added a 100ms wait.

[Aqua-]

Thanks for both of you @crossrulz and @MrMattPayne.

I liked the way you did it @MMP, i think this is what i was looking for with the "search 1D array" which allows me to take subsets of arrays instead of appending row by row like what i started trying 🤙. Allow me not to declare a solution as more help may beneeded in the future.

[Aqua-]

The machine im working with outputs a .txt file for each measurment, i.e. every button press.

Say i wanted to make some data analysis such as mean values, averages, delta of specific values in each row, etc. I guess i can do all that stright on my 2D array before saving it to the file.

1) Is it prefered to make these kind of calculations stright in my LV code? I think, for the convenience of my client, that it will better for him to automate such calculation on the excel through my VI, meaning, when I hover over the cell that represent the average of 3 cells in the row, I'll see the corresponding excel function that is used.

e.g. when hovering over cell AT28 in the picture, I can see the calculation that gave me the result.

This will be ideal for my client as he doesnt know any LV coding, so it will be easier for him to make changes in any case, and make it less of a black box for him.

Now say I want to make some graphs out of the data, and save it to the file.

2) Can I save it on a .csv file? My experience (+ a test on my coputer) says it cant.

If I want to have graphs, do I need to save the file as xlsx file? 

As far as i know sheets are not supported within a .csv file.

3) If my client want for some reason to save all measurment of the same day (say 5 outputs) to 1 .xlsx file with each saved in a different sheet, is that possible?

4) A more technical thing - I saw that for converting data into MS Excel the Report Generation Toolkit is needed. I saw 2 links for this toolkit - one is a paid module:

https://www.ni.com/en-il/shop/software/products/labview-report-generation-toolkit.html 

and the other a free-relatively small size add-on: https://www.ni.com/en-il/support/downloads/software-products/download.labview-report-generation-tool...

Is the free link really all that is needed, and if so, can I use the free one for my client without  legal issues?

5) I also saw that LabVIEW's help suggest using Active X to manage Excel through LV. Which one is prefered? what are the differences between the two?

As I may or may not already mentioned - this is a new territory for me, as I never had to export output to xls and never had to worry about licenses before, since my work place had the complete package (the university).

Thanks a lot for any help.

Partial answers will be appriciated as well, as each question is a problem in itself so dont be shy 😆

[Yamaeda]

A CSV is a pure text file, and as such cannot contain macros, graphs, tabs or the like. So, if a CSV is what you want to produce you'll need to do the calculations in LV and generate a result column yourself. I don't know if you can 'trick' it by simply writing the formula as a column, it should be easy enough to test.

If memory serves, Report Generation Toolkit is now included in LV (assuming you're using a newer one). And it should give you some more options. You can also generate a document Template (outside LV) which already has macros and stuff and just enter the data.