merge all datas of a sequence in a single excel file

Question

Hello

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.

Admin · Accepted Answer

This question has been marked as assumed answered.

ryoboyle · Answer

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.

valentinrusu · Answer

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:

merge all datas of a sequence in a single excel file

Top Replies