Writing to Excel file is very slow. How can I speed this VI up?

[ajh]

I am reading through a text file line by line and sorting data into the appropriate column in an excel worksheet. The text file may contain several thousand lines. The vi runs alot slower than I had hoped. It seems the report tool kit excel vi's are always slow. Any suggestions?

[smercurio_fc]

I don't have the Office toolkit, but one thing that's definetely slowing you down in this case is your time delay. Yeah, it's only 0.02 seconds, but with 39,457 lines, that adds up to quite a bit of waiting. Temporarily disable it to see how much of an effect that is compared to temporarily disabling the writing to Excel.

[ajh]

It was running slow without the time delay.

[smercurio_fc]

Well, since I don't have the Office toolkit I can't test the VI as is, so I took your VI and used just straight ActiveX calls as in the "Write Table to XL" example. The fundamental problem is that you are doing a lot of individual operations (39,457 x 18) into Excel using ActiveX. This is going to take a long time. I would suggest creating the 2D table you want to write in LabVIEW and then using the "Range" object to simply write the entire table to Excel at once. This will be a lot faster, but it will use more memory since you will need to create the table in LabVIEW.

[GerdW]

Hi ajh,

I also don't have the Office toolkit (installed), but I suspect it's the "Excel easy text" function to take a long time...

Could you redesign your program? At the moment you are scanning the file line by line. For each line you have a call to "Excel Easy..." to fill just one cell. Maybe you can scan the file first, build a 2D array of string from your data and the push the whole 2D array to an Excel sheet (thus calling Excel only once)?

[amcolosk]

I do use the report generation toolkit, and it is very slow for writing values.  It seems to improve the time if it writed data in groups.  As has been suggested, I would recomend grouping the values into either an 1d array / cluster and write it for every group of data or a 2d array and do it all at once.    By the layout of your code, implementing a cluster to import the data by a group should be easy since you already have the case structure set up.

[ajh]

Thanks for the advise. It worked.

I read the file and wrote to a 2D array, then placed all the data into Excel at one time. I went from over 1.5 hours the old way to approx. 1 minute using your suggestions.

[Kirit]

Can i get the screenshot of the tst_file_sort.vi.

I am unable to open the file in 7.1 version, i dont know the reason. It would be very helpful if i get the screenshot view of that particular file.

Thank you

[Mathan]

Hai kirit,

I have attached what u wanted.

Mathan

[Kirit]

Mathan,

Thank you for the .PNG 's. Thats fine but it is for sorting out data in the same file(document). If i want to sort (a particular data) from n number of excel sheets then this will not work. What are the possible modifications to be made in the same( tst_file_sort.VI) to change that to search for a particuler data in N muber of excel sheets.

I can clarify if the requirement is not clear.

Thank you

Rgds

Kirit