merge all datas of a sequence in a single excel file


I  would like to merge data form several chromatogramm (sequence) in a single excel file to have

date / time / and area n, retention time etc

I have reproceesed the whole sequence and each data of analysis is stored in a single excel file encapsulated in a single folder

Is there an option somehwere ?  Does someone own a macro to do that ?

Thanks a lot for your help.

  • Hello,


    Would you mind providing more information on what software product and revision of that software product it is that you are using? You can usually find this information by going to the 'Help' dropdown menu and selecting the option for 'About'.

  • Hello


    Software  is Chemstation B.0402 SP1 (208)


    Thanks for your interest


    [edited for content]

  • Hello,


    In ChemStation rev B.04.02 SP1, you would need to use batch reporting to generate a summary Excel file for your entire sequence.


    To enter Batch Review, you will need to go to 'Batch' > 'Load Batch...'. The software will then prompt you to load a batch (.B) file, as seen below:



    Batch files are saved automatically in your result set folder and will have the same name as the sequence template that was used to collect that result set (but will have a .B extension, not a .S). Once select the correct batch file for your sequence, click 'OK' and you will then need to select a method and the data files you want to process/report (below):


    Once you press 'OK' in the above window, you will then be able to update the Batch Options by going to 'Batch' > 'Options...':



    You can update what information you want to show up in the Batch Report from the 'Report Table' tab in the Options window. For example, if you want to report the area for each of your compounds, you can insert or append a column as shown below:



    In the 'Report Options' tab you can then specify that you want to print the report to a file and specify the file type as .XLS:



    NOTE: In the processing tab you will probably want to change the option for pausing between processing of runs from 10 seconds to 1 second to speed up the processing time.


    You can then save the batch (I recommend saving the batch file as a new name after making your changes) and then process the batch (Batch>Start) and print the report (Batch>Output Batch Report) andthe Excel batch report will then be created in the result set directory (where your data files are saved).


    Hopefully this is helpful. Please let us know if you have any questions.

  • Hi cecile.daniel, if the answer provided was correct, please come back and mark it as such.


    I would like to add that the way the data comes out in a batch report is a bit unusual, in part because it was designed to be used for LIMS and other programs that look for information in fixed places in these files.


    If you output to a CSV (comma separated values) file type, you will actually get two files [Report]00.CSV and [Report]01.CSV, where [Report] should be replaced by the Prefix field in the Options for Batch review window posted above by ryoboyle. To view these files correctly in Excel, it is best not to double-click on the files directly but rather open Excel separately and go through the import wizard instead, which should be triggered when you go to File > Open and pick one of the files. Otherwise, the file will be opened with the data listed in a long string, along with the commas that should be separating the elements into separate cells. For example (using our Demo data):


    If opening the files instead in Excel by clicking File > Open, you will be presented with the Text Import Wizard, in which you select Delimited in order to separate out the data into cells:


    Click Next. In this case, since CSV is intended to be separated by commas, choose comma as the delimiter. You should see a preview of the data below in the Data preview section:


    Clicking Finish should yield legible data:





    On the other hand, if you choose XLS (Excel) as the File Type, there's no import wizard necessary. You will get a file with 3 tabs:


    The labels for the data are located on the Labels sheet. Select the Title cells corresponding to all column of data (the total number will depend on the number of compounds calibrated on the method used for batch reporting and on the number of peak results chosen in Report Options):

                   (click and drag from the Title cell corresponding with Column1, E3 in the screenshot above, to the Title cell corresponding with the last Column, E18 in the screenshot above and copy this selection of title headings)


    The Data sheet shows the actual data. Go to that sheet and find Column1. Right click on it, choose Paste Special and then check the Transpose box, then OK:




    Now the data is properly labeled:

  • This question has been marked as assumed answered.

  • Hi,

    This was very instructive, thank you. We did a run of all samples in the sequence as "samples," without "calibration" samples. The problem is that the batch process excel file we generated does not really contain any meaningful data (no RT, peak area etc) even though it was specifically set in the Batch -> Options -> Report Table to show RT, area and %amount. Is there a workaround through this? Would really appreciate the help! Our software is rev. C.01.07 [27]. Thanks!

  • Hello! I would say make sure that the method you are using for data processing is both set to integrate peaks (to give you peak areas) and calibrated (to give amounts).

  • Hi there, thanks for your reply. Will you be able to point me out to the right direction on how to double check that the method I'm using for data processing is both set to integrate peaks and calibrated? The only thing I really want to do is batch process my one sequence file with both retention times and peak areas. We just did the run of all samples in the sequence as "samples" without "calibration" samples. When I batch processed that, the excel file I generated didn't really contain any information. 

  • Unfortunately, it's been a few years since I've had any of these versions on my system, so I can't give you step-by-step instructions, but I believe one of my colleagues should be able to help. The gist is that if you're doing batch processing, you'll see that you're prompted for a method to use for that processing (second screenshot from above). That method should have both appropriate integration parameters and have a calibration table in order to get both areas and amounts in your report. , or others, can you help point in the right direction? 

Was this helpful?