Any Ideas How I can Make this Formating Program Run Faster?

[mechanical13]

I'm using LabView 8.0.1 to extract columns and rows from MS Excel to format to a specific format which can be read into a software program we use in-house.

The software program requires a specific header which declares variables, etc and also fomats the extracted data in an arcane way.

Due to an error in the software program we use, the file format that has to be read in is not uniform and has the first line containing  two point sets (see attached file for an idea of what I am talking about). Any other format will not be read by the program.

This introduced some problems, and finally, I ended up with an highly inefficient program. It runs, but is relatively slow.

The programs, the input excel file and output file are given for example. 

A couple of notes on the files:

1) The file type used as input is just an excel file which has been saved in space-delimited format.

2)  To speed up the process,I  permitted the formating of only 2 variable types (x, y, z, variable 1, variable 2). I haven't gotten to adding more variables. Ideally, I would like to count the number of columns on the excel file, read the headers and then write the output file automatically with all the variable names.

3) I used sequenced loops in the vi, which is probably not the best way to do this. I am guessing that I can interweave all the numbers into one large array before writing --- but have no idea how I can accomplish that.

The output file extension is actually .fvp. I've renamed it in order to upload the file.

Any help/suggests would be greatly appreciated!!

~Janak

Files are attached.

[Joe_H]

I have not ran your code yet but I do have a couple of suggestions that may help.

1. You are opening the same file at three different times. Just open it one time and you can get all of the infor that you need by reading strings and turning them into numbers. this will speed things up alot.
2. I'm still not clear on your intentions with the while loop because the only thing that you are doing is passing the # of iterations out by reading the file until it gets to the end. Why not just get the file size and let that be your input to the for loop.
3. Clean up the wires of your code so that others do not have to clean it up for just so that they can help you figure out what is going on.

[Dennis_Knutson]

I agree about the wiring. The formatting that is done with the concantanate string function is just too messy to understand.

A point about the Read From Spreadsheet File. In 8.0, the function was only capable of outputting numbers. This all changed in 8.2 but the diagram for the Write to Spreadsheet has instructions on how you can modify the VI to output strings. I would suggest you do that as well.

And you are correct about the sequence structure. It should be eliminated.

[johnsold]

In addition to Joe's comments it appears that just by wiring up the error clusters you could eliminate the sequence structures.

It looks as though you are trying to set the file position to the end of the file. If so, just set offset = 0 and the from (0:start) to "end". Create a constant and set the value to "end". The Read from Spreadsheet File.vi has an output "mark after read (chars)" which is probably the same number as you get from your while loop. Check it. If they are the same, then no need to recalculate something already reported. Also, check the help files to make sure all the files and references are closed. Some VIs, like the Read from Spreadsheet File.vi probably open the file, read it, and close it while others do not close the files.

I did not work through your array manipulations but I suspect that array subset alone is sufficient with appropriate inputs.

Depending on the file size it may be more efficient to build a single string in the for loop and do one write outside the loop. File operations are slow. Use a shift register and concatenate strings. If the files are really large (>10s of MB), read the white paper on handling large datasets which can be found by searching the Forum archives.

Lynn

[Matthew_Kelton]

I didn't realize I was having spaghetti for dinner tonight.

Why on earth do you open this file three seperate times?  This is way over-complicated for what you want to do.  I am having extreme difficulty following your code because of your wiring.  Scan to String can be your friend.

You're opening and closing your output file n+1 times as well.  This is completely unnecessary.

You should open your input file one time, pull the data out that you need, then create your output file, open it once and write the data out.

All that being said, the code ran pretty fast for me with your example file in LV 8.2.1 (I don't have 8.0 installed).

[RavensFan]

Use the Format Into String Function instead of the large concatenation.  Most of your inputs are constants, so they could be put into the Format string of that function.  It looks like you have only 2 truly variable string inputs, so they would show up as %s in the format string.

So your format string would be something like this (\codes turned on)  (VI is LV 8.5)

Message Edited by Ravens Fan on 09-28-2007 05:05 PM

[mechanical13]

The code is slow relative to how large a file I need to process.

Typically, my input files contain approximately 50,000 - 100,000 lines.

In the current code, it takes about 5-10 minutes to run, which is too long a time to wait.

I understand that I shouldn't be reading the files in three times, but don't exactly have a clue how to inter-weave numbers, especially it has to be in a very specific form.

I was able to get a VGA program (MS Excel) to do this interleaving in about 30 seconds (the headers were messed up though the Excel macro isn't too user error proof, so I am really keen to get it working with LabView ) so, I believe such fast crunching of  approximately 70,000 rows should be achievable with Labview.

My idea is to have a fast, user-error-proof tool that will translate between formats.

Cheers,

Janak

[Matthew_Kelton]

Don't say I never did anything for you.  Attached is a reworked VI.  I will not claim it to be the best way, but it is significantly simpler than the code you posted.  Go through it and try to understand what is being done.  I almost left it uncommented for a learning exercise, but thought that may be cruel.

[mechanical13]

Matt, Thanks a lot. I haven't figured out how its done, but the program you wrote is approximately 500 times faster than mine.

I appreciate your help. Will be sure to learn from it so that I will be able to code better in the future.

I hope you have a great weekend.

[Matthew_Kelton]

My guess is that most of your time was in your opening and closing of the output file in the for loop.  You'll notice I only open and close the input and output files once.