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

[NCase]

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

 

[NCase]

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

 

[NCase]

Anyone have any suggestions regarding this excel data processing challenge

 

[vletm]

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...

 

[NCase]

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]

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]

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?

 

[NCase]

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

 

[NCase]

I can now see error run time error 1004 when i run macro through vba editor

 

[vletm]

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]

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]

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

 

[vletm]

NCase
Modified version.

If something is very important
then You should answer to questions
otherwise that would handle as normal.

 

[NCase]

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

 

[vletm]

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.

 

[vletm]

NCase
This version also saves that selected single avg-file to mrs-file.
Note: 6,7,22,23 -rows informations are ... something!

 

[NCase]

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]

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]

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]

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]

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

5) Open xlsb-file
6) press [ AVG to MRS ]-button

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

 

[Kenneth Hobson]

Cross-posted: https://www.excelforum.com/excel-programming-vba-macros/1258632-batch-ascii-file-conversion.html