automatic lookup of data from workbooks / datasheets

[Gabi1]

Hey
finally back to some LV programming, this time with an interresting challenge.

i have several large datasets to extract. the number of datasets is such, that i need a semi automated solution.
each dataset includes few thousand files.
some dataset are text files, and some are excel workbooks, including several sheets.
the idea is this: look-up in the file a value of interest, and define its "position". Then, for all the files in this dataset the program will automatically extract this value. the way the prog should determine if a value is in the right field, is by its description with preceding string.
Everytime there is no match, i should be able to define a new look-up position.
At the end, i expect the program to pick up a file, and search the value related to several possible formats.

Any idea to give me a lead how to takle this?

note: posted also on LAVA

[MikeS81]

Hi Gabi1,
is your question still available or did you get some responses from LAVA? Do you already have the format where you want to save the positions?

Mike

[Gabi1]

Thanks Mike for your interest.

no i did not yet got any answer that would enlight me

but i am now understanding a little more the interface LV and Excel. at first i wanted a direct interface to an excel sheet, where i could "click" with the mouse to choose the field of interest. it wont be feasable.

my approach will be to read a full datasheet into a string table. the first problem is that some cells in the excel sheet have errors, and then the translation variant to string table returns an error. so i need a way to scan the variant data for 'bad' cells.

the second step, will be to choose a few specific cells from that table. i think i should manage not too difficultly using event structures. in general there will be a marker at a specific cell (string indicating the meaning of the value in the next cell), and a value cell.

third step will be to extract a list of such excel tables, and read out the specific cells of interest. the tricky part will be, in case the marker cell is not equal to the choosed value. then the excel file shoudl again be fully extracted to a table, and again i should be able to choose the marker cell and value cell.

so the app will have a library of possible cells to look for a specific marker, in the following excel file to read. if it fits, extract value. if search returns null, then again i (the user), should be able to pick from the table what is the marker cell and value cell.

you see the necessity to switch from excel format to string, the searching and comparison, makes the whole process quite convoluted, rather than a direct operation with excel interface in a container for example.

i hope this is clear. i acknowledge any input and idea 🙂