Batch Ascii File Conversion

Hello

I have spectroscopy instrument that exports raw data in an ascii format with an ".avg" file extension.

However the data is arranged in a layout that is not useful for further data processing by our custom software. So I need to batch convert these files into another ascii format with a ".MRS" file extension.

I have made a excel spreadsheet in how I want to transform the .avg file to the .MRS file format. I have included one ".avg" and the transformed ".MRS" version of the processed data that I did manually.

For the "avg" file format. The "Folder Path" and "Sample Name" is in Row 2.
Starting at Row 91, the "Raw data" is arranged in rows of 4 until Row 343

For the "MRS" file format. The "Folder Path" is placed in Row 6 and 23 and the "Sample Name" is placed in Row 7 and 22
The "Raw Data" is placed in Row 64 to 1073

Does anyone have any strategies on how to achieve this goal with an excel macro? I will have 100's of files to process so automating this step is key.

Best,

Francis

Hi,

Correct.

For the "avg" file format. The "Folder Path" and "Sample Name" is in Row 2.
Starting at Row 91, the "Raw data" is arranged in rows of 4 until Row 343
For the "MRS" file format. The data path is placed in Row 6 and 23 and the sample name is placed in Row 7 and 22
The "Raw Data" is placed in Row 64 to 1073

Best,

Francis

Change Test sub to suit. Test MRS file created before removing Exit Sub line.

Kenneth,

You are a genius. Slight problem with data transfer. The List@ 0,4,8 etc is adding that number to the raw data.

Raw data in AVG file
LIST@ 0= 73540.670222, 74007.235144, 74122.378528, 74475.766728
LIST@ 4= 74479.583892, 75007.524054, 74607.980959, 75413.395748
LIST@ 8= 75092.628512, 75329.586111, 75714.395595, 76134.041392
LIST@ 12= 76484.775445, 76472.107619, 76525.123873, 76519.322793
LIST@ 16= 76681.134664, 76890.350097, 76959.793379, 76808.650980
LIST@ 20= 76348.558021, 76685.406776, 76237.392962, 76456.819805
LIST@ 24= 75901.796606, 76079.244729, 77143.369740, 76928.917529
LIST@ 28= 75893.761048, 76379.506810, 76360.600788, 77737.340767

Raw Data in AVG versus Raw Data in MRS
73540.7 73541
74007.2 74007
74122.4 74122
74475.8 74476
74479.6 474480
75007.5 75008
74608 74608
75413.4 75413
75092.6 875093
75329.6 75330
75714.4 75714
76134 76134
76484.8 1276485
76472.1 76472
76525.1 76525
76519.3 76519
76681.1 1676681
76890.4 76890
76959.8 76960
76808.7 76809
76348.6 2076349
76685.4 76685
76237.4 76237
76456.8 76457
75901.8 2475902
76079.2 76079
77143.4 77143
76928.9 76929
75893.8 2875894

Kenneth,

Thank you and its working perfectly. Instead of hard coding the file path.Is it possible to change code so I can broswe to certain folder.

Is it possible to dump the created mrs files into a folder named mrs

Best,

Francis