• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Rearrange & Convert X-ray Spectroscopy Data into a new ASCII format

NCase

Member
Hello and Happy New Year To Everyone

I have a x-ray 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.

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.

Happy Holidays,

Best,

Francis
 

Attachments

NCase

Member
Happy New Year,

To clarify my data processing request.

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

Best,

Francis
 

vletm

Excel Ninja
NCase
Maybe someone has waited clear rules for some parts...
eg Are rows 1..63 always same except those 6,7,22,23?
eg Clear rules for those four rows?
>
Press [ Do It ] from AVG-sheet...
 

Attachments

NCase

Member
Hi,

Thank you for your help. Yes in the MRS file the only things that change in the avg files are the file path and the sample name which are in the original .avg file.

Best,

Francis
 

NCase

Member
Hi

I am getting a 400 error when I try to run the macro but it does open the avg file and stops processing the data.

Best,

Francis
 

vletm

Excel Ninja
NCase
Which row gives error? I don't get!
Is it same avg-file?
... I didn't set so many error handling!
Could You answer to my #4 questions?
Screen Shot 2019-01-03 at 14.16.11.png
 

NCase

Member
Hi,

Sorry for confusion. The sample file names can be very different. This format is fixed for a specific type of analysis but the file names for different project can vary alot.

The I am getting is in the attached image. I cannot see what line is effected.

Best,

Francis
 

Attachments

vletm

Excel Ninja
NCase
Which row gives error?
If I cannot see/know it, it's challenge to modify!
..
and other questions needs answers.
... or I could left those 6,7,22,23-rows as empty ... hmm?
... is data always same length?
 

NCase

Member
The 1004 error comes up on this line

xcut = WorksheetFunction.Find(",", chk_a)

The MRS file has a format that must be kept the same so the spectroscopy software can batch open and process it.

The data is always the same length.

Best,

Francis
 

NCase

Member
The sample name is very important to keep connected with the new mrs file format. The folder path is important but not as critical.
 

NCase

Member
Vletm,

Thank you for your help so far.
So the key information in the .avg file is the Sample name, path and the raw data. I need a method to turn the .avg into the mrs format in a batch way.

For the MRS file format, the "File path" should be placed in row 6 and 22 and the "File Name" in row 7 and 23
The path and sample name can be obtained from row 2 of the .avg file

I need to be able batch process hundreds of files. Hence why I need the sample name and ideally the folder path in the mrs file.

So how do I get the macro to process a folder of files and create a folder of mrs files with the file name and ideally path in each generated mrs file name.

I am including a large batch of files to play with.

Best,

Francis
 

Attachments

vletm

Excel Ninja
I've asked about those 6,7,22,23 -rows informations few time,
without helping answers!
I even send photo ... but nothing ... only that 'very important'.

Did that 'my output' look like correct mrs-file?
( except those four lines which You won't answer? )

I don't need those answers,
You need those answers!
... that You could get something!


I could make from avg-file new sheet as mrs-file with Excel,
but who knows what would be in real mrs-file or how to export it?
If export someway ... is it useful ... I cannot compare/verify that.

Play ... hmm?
If 'my file' works with one file
then it would work also with any number of files.
 

NCase

Member
Hi

The data is transformed perfectly (rows 91 to 343 in .avg file)
Row 2 is the file path and sample name and changes for each sample test

Row 2 is ;Dump of DataSpace 'C:\DataDump Data Extraction\ProjectFolder\01-01-2018_AABB_116208.VGD'

I want to take the folder path in Row 2 of the avg file and put into row 6 and 23 of the MRS file

Row 6 is file_path=C:\DataDump Data Extraction\ProjectFolder\001_01-01-2018_AASS_116208.MRS

Row 23 is desc2=C:\DataDump Data Extraction\ProjectFolder\

I want to take the sample name in row 2 of the avg file and put into row 7 and 22 of the MRS file

Row 7 is file_name=001_01-01-2018_AASS_116208.MRS

Row 22 is desc=001_01-01-2018_AASS_116208.MRS
 

NCase

Member
Hi,

The last file is transforming the single avg file into the MRS format. The next challenge is to batch process the avg files and save the processed file as .mrs file so it can read by the data processing software.

Best,

Francis
 

vletm

Excel Ninja
Did You rewatch that photo?
Do those has some connection with my 'blue notes'?
maybe You ... previous text ... would work with that file which I've tested.
... of course, I could left those as in my previous version or fixed!

also
Reread #15 Reply's blue text.

There is still only one challenge!
... answers!
 

NCase

Member
Hi

I did reply about blue text in photo.

"The sample file names can be very different. This format is fixed for a specific type of analysis but the file names for different project can vary alot."

So the file names will not have the same structure all the time
 

vletm

Excel Ninja
NCase
#1Q was 'Did You ..?'
#2Q was 'Do those ..?'

Have You express that You'll have over 100 files?
>> You have 'copy&pasted' few times 'same phrases' ... about ONE file!
If You can give rule for ONE file then ... what to do with those others?
.. .. .. .. .. .. hmm?
> NEXT >
1) save xlsb-file to own folder
2) create AVG-folder
3) copy all AVG-files there
4) create MRS-folder
Screen Shot 2019-01-04 at 20.22.31.png
5) Open xlsb-file
6) press [ AVG to MRS ]-button

>> if need changes ... then ... answers.
 

Attachments

Top