1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'VBA Macros' started by NCase, Dec 31, 2018.

  1. NCase

    NCase Member

    Messages:
    39
    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

    Attached Files:

  2. NCase

    NCase Member

    Messages:
    39
    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
  3. NCase

    NCase Member

    Messages:
    39
    Anyone have any suggestions regarding this excel data processing challenge :)
  4. vletm

    vletm Excel Ninja

    Messages:
    4,633
    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...

    Attached Files:

  5. NCase

    NCase Member

    Messages:
    39
    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
  6. NCase

    NCase Member

    Messages:
    39
    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
  7. vletm

    vletm Excel Ninja

    Messages:
    4,633
    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
  8. NCase

    NCase Member

    Messages:
    39
    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

    Attached Files:

  9. NCase

    NCase Member

    Messages:
    39
    I can now see error run time error 1004 when i run macro through vba editor
  10. vletm

    vletm Excel Ninja

    Messages:
    4,633
    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?
  11. NCase

    NCase Member

    Messages:
    39
    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
  12. NCase

    NCase Member

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

    vletm Excel Ninja

    Messages:
    4,633
    NCase
    Modified version.

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

    Attached Files:

    NCase likes this.
  14. NCase

    NCase Member

    Messages:
    39
    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

    Attached Files:

  15. vletm

    vletm Excel Ninja

    Messages:
    4,633
    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.
  16. vletm

    vletm Excel Ninja

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

    Attached Files:

  17. NCase

    NCase Member

    Messages:
    39
    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
  18. NCase

    NCase Member

    Messages:
    39
    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
  19. vletm

    vletm Excel Ninja

    Messages:
    4,633
    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!
  20. NCase

    NCase Member

    Messages:
    39
    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
  21. vletm

    vletm Excel Ninja

    Messages:
    4,633
    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.

    Attached Files:

  22. Kenneth Hobson

    Kenneth Hobson Active Member

    Messages:
    238

Share This Page