Data processing with excel - Chromatography Forum

Data processing with excel

Discussions about chromatography data systems, LIMS, controllers, computer issues and related topics.

3 posts Page 1 of 1
I am a grad student in need of a little bit of efficiency help with my data processing. I am using an Agilent 7890 GC with OpenLab CDS and I have .xls reports automatically generated for each GC I run; however, it still takes time to go into the file, open the report, get to the right tab, and copy the peak data from the .xls report and then paste it into an excel sheet that I have to process my data. Is there any way to expedite this and have excel import the data for me? Or can I have OpenLab put all of my data into one .xls? Also, is there any way to have OpenLab name the .xls file something other than just Report01?

Thank you
This works for D.02 Chemstation and earlier...

Use the custom report/database software to create a report.xls sheet. Now take that sheet to another PC along with the associated method.xls sheet that Chemstation Custom Reports put in the directory when you QC the data and add it to the custom database.

If you open the report.xls and check for links, it will have a link "detail.xls" if you refresh this link to the method.xls sheet in the data file subdirectory, then you have it.

You can customize the report all you like, just don't mess with the linked cells which are off to the right. You can move rows around by cutting and pasting but you have to move the whole row. If you add or subtract a compound to/from the method then it breaks your report. In custom reports you can adapt a report by deleting all the rows but one and invoking "copy down". That will reorder and insert all the links properly. Experiment and you will see what can be done.

Don't copy your customized report back to your chemstation. Chemstation uses a very early version of excel and you will break the report.

I copy the data directory and report subdirectories over my network to a workstation and then generate reports by updating the link with each successive sample. With a little ingenuity, you can link in customer data and use a macro to dump each file into one or more master spreadsheets.

Edit: This thread probably needs to be moved to the Data Issues Thread
Yay, data parsing.

Simplest thing would be to go into your .xls data file, select the peak data field you want, hit Copy, then Paste Link to your excel sheet that you use to process the data (ProcessSheet). After you have all of the links set up (and assuming that the .xls data files are consistent in their formatting) you can go to your ProcessSheet and go to Data and Edit Links to browse to the next .xls data file and the linked fields will all update. You will have to do this for each .xls data file but it should be simpler.

If you have MS Access you can create a module that allows you to browse to a days worth of data and select the data folders and click a button and run VBA code to do all of this for you and compile it into a table but that's a bit more complicated.
3 posts Page 1 of 1

Who is online

In total there are 7 users online :: 0 registered, 0 hidden and 7 guests (based on users active over the past 5 minutes)
Most users ever online was 234 on Tue Feb 06, 2018 7:33 am

Users browsing this forum: No registered users and 7 guests