Chemstation - Exporting results into excel format?

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

18 posts Page 1 of 2
Hey there just wondering if it is possible to export raw data into excel?

I have a list of 60 compounds (pesticides) and i would like to somehow copy the concentrations into excel so i can calculate recoveries etc rather than having to type them in one by one by hand from the raw data.

I'm sure there would be some sort of command or technique for doing this but i can't find anything in the help section.

Cheers

Can't you set this up in the Reports function in the Chemstation software? There should be a check box in the report section when editing an entire method that allows you to create a XLS report for each sample.

You should be able to batch process the raw data in the data analysis tab of the offline chemstation and set it up to output an excel file which you could then manipulate.

You can export to .xls or .dif, I've done that. It's not the best (at least on versions up to A.09) but it can be made to work. Then I'd copy and paste from there.

If you're in LC-chemstation, and you use batch, it's easy.
Go to batch,
click on the batch options buttton ("batch" with a close-cross in it), or select batch-options from the menus.
Choose the Report Options tab
Tick the Excel box, and the report, when processed, will be created in the form of an Excel workbook.

For some reason, the first worksheet in the workbook contains virtually nothing, the second contains the column-headings for the third, and the third can contain whatever data you want to export. You will need to choose your columns in the batch report options.

What I've done is to try and simplify this process as much as I can by writing a set of macros in excel that will loop through a chosen batch (sequence) folder, open all the .D directories, read the chosen data and compounds, and write it all to the open workbook. It will also continually add data sets to the end of the previous one in case your data is split up across multiple instruments. Once this is complete, I've got to click a few buttons to do a full data analysis on a set of data (including control charting my ISTD areas).

You share the folders where your data is stored and all this can be accomplished from my desk.

It probably sounds lazy but when you run a high throughput fermentation screen doing 250K samples a year, you'll find any little way you can to save a few minutes...........

I have an Excel export macro that will do this process as well. It allows you some ability to customize what is exported, and in what format. You just need to have Excel running a particular spreadsheet. It works in both run and reprocess modes.

Agilent has some other options involving macros, but I have found them to be less useful.

I would be interested in getting a copy of the macro from LCGCBrew.

And, I know of an add-on called/by chem2xl that may do something similar. Have seen it installed but did not investigate.

Also, Labtronics sells programs to capture data from reports. It is very generic and needs to be customized for each application, but if you are a computer-literate user, or know someone who is, it might be a good option.

Warning: be careful with the Batch Report spreadsheets. Batch Report uses its own calibration scheme, and it may not be the same as what you use.

Finally, the newest ChemStation versions have an option to export an XML file to a specific location. If you can read the information from there, your job may be easier.
Merlin K. L. Bicking, Ph.D.
ACCTA, Inc.

Good point! I'd be grateful for any comments from someone from Agilent about batch report differences in calibration and integration.

I've also noticed occasional differences, but my usual procedure is to set up my calibration before I go to batch mode; this way I know exactly where my calibration came from. I also subsequently look at cali points and QC points in the batch results, and judge whether I can trust the whole dataset by whether these points are OK. Hope that's a reasonable approach...

One important point about batch mode is that it opens files strictly according to the signal details. If these are set up to disregard the first few minutes of a run, anything that happens in that time won't affect integration. This means you can get different integration, and different calibration, compared to when the software had access to the whole chromatogram. Neither is necessarily wrong, they're just different...

lmh:

I teach some of the ChemStation courses for Agilent, and I always caution students about using Batch Review. It is a great tool for reviewing data, although the new B series has a more convenient method.

My understanding is that Batch Review simply averages all calibration runs and uses that value for calculations. Have not bothered to check this; if there are differences, that's all I need to know.

Of course, if you do not do any recalibration during your run (all your standards are simply analyzed as samples), then everything should be fine.
Merlin K. L. Bicking, Ph.D.

ACCTA, Inc.

Batch reporting is good for a quick review, thats about it.......

I can validate the concerns raised here and say that I know for a fact that batch reporting does use a averaged calibration scheme.

mbicking, you've got an email on the way to you with some of that macro information.

Speaking of which, I've written a ton of different macros for Agilent, Gerstel, CTC etc. I'd be happy to share them or write something quickly to suit your needs, just drop me an email.

Josh

Thanks for explanation; I wouldn't have met this difference. Frankly I've never really been happy about trusting calibrations that change during a sequence. Simplistically, I want my calibration curve at the end of a sequence to look like the curve at the start (and any other point where known samples are introduced). If it doesn't, I prefer to treat all the results in between as burdened with that level of unreliability, rather than try to botch some sort of bracketing scheme. Bracketing always relies on the idea that sensitivity changed in some simplistic way between two sets of standards, but in practise it rarely does.

Hi all,
first of all sorry for my bad english... maybe i have a possible solution for the problem.
I'm developing a web application that can export data from ChemStation CSV report to a excel file.
It works in a similar way to the macro explained by LCGCBrew but it is written in PHP. Here is a demo of the program (section "Analisi").
http://portal.netsons.org/chemtools/
At the moment i have not examples chromatograms to use for a full demonstration, but the application can recursively read the data from a sequence parameter directory that the server can read (for example from a shared folder) and export the value (you can choose which value to export) in a excel file.
If someone is interested and have some chromatograms (with report in CSV format) that can be showed in public, this chromatograms should be used as examples in the demo.
In this manner i can provide you a full demonstration of how the application works
Interested people can contact me at the mail address showed in the page footer of the site linked
Cheers

8) Update
Now you can try out a demonstration sending your chromatograms in ZIP format.
This is the address for the demo:
http://portal.netsons.org/chemtools/analisi/upload.php

Instructions:
- Integrate (manually or not) the peaks in all your chromatograms
- Generate the CSV report from chemstation for all your chromatograms
(Under report mask flag destination File and format CSV)
- ZIP the sequence parameter directory containing all your acquisition (and the CSV files created in the previous step)
- Upload the ZIP using the provided link, select the data output format (Excel) and choose wich field to export
- Finish

Limitations
The demo is hosted by a third party free host and the size of file to upload is limited. Theoretical value is 64 MB but after few seconds (about 60 i think) the server could terminate the connection and kill your upload.
For this reason the size of the ZIP file must be limited to about 3-4 MB (i tested a file of about 5 MB and it works fine, but it depends on the speed of your internet connection). All my program needs is the CSV files, so, in order to reduce the size of the ZIP file, you can eliminate all the others files. in this case:
- Make a copy of you sequence parameter directory containing all the acquisitions
- Delete from each acquisition directory all files apart the CSV files
- ZIP the cleaned sequence parameter directory and upload
It is very easy, takes only a minute. after months of research and destroy :) , we found the easiest way to fix this problem. it is a combination indeed. install a virtual pdf document writer, go to file, print options and select pdf writer as default. go to your batch report window and press print, the pdf writer will ask you ,how to save . save the file and open with a pdf viewer. and in viewer select "select all" , than paste this to an txt file. open excell , open your saved txt file, a window will come to ask you how you want to open the file, select limited (i use a turkish excell so i don't know the exact words, a 2nd window will come and select "space " only. you will see that your batch report are in perfect shape
This message is for LCGCBrew

Can you send me your Excel export macro for GC Chemstation?

Thanks.

royekan
The "funny" thing it is straight forward to generate an Excel report in ChemStation E2 for GC-MS, but seems still be a problem in the C -series? (I have never been able to get a clear answer from a Agilent representative. Why can Agilent not have the save report capability in C as in E? A major reason to use Chromeleon.

The PDF workaround is outlined above.Some comments to the PDF-> Excel work. Many users are not able to follow such workflow, they find it to complicated and instead rewrites the data manually. With Office 2013 it might become more straightforward since Excel is supposed to open a PDF and tabulate data in cells. (I see this as a major efficiency gain in the lab.) For those that don't want to wait for Office 2013 can find PDF-> Excel converters on the market, including Acrobat Professional that can be used to copy tables from PDF to Excel.
18 posts Page 1 of 2

Who is online

In total there is 1 user online :: 0 registered, 0 hidden and 1 guest (based on users active over the past 5 minutes)
Most users ever online was 1117 on Mon Jan 31, 2022 2:50 pm

Users browsing this forum: No registered users and 1 guest

Latest Blog Posts from Separation Science

Separation Science offers free learning from the experts covering methods, applications, webinars, eSeminars, videos, tutorials for users of liquid chromatography, gas chromatography, mass spectrometry, sample preparation and related analytical techniques.

Subscribe to our eNewsletter with daily, weekly or monthly updates: Food & Beverage, Environmental, (Bio)Pharmaceutical, Bioclinical, Liquid Chromatography, Gas Chromatography and Mass Spectrometry.

Liquid Chromatography

Gas Chromatography

Mass Spectrometry