Importing reports that vary

Vanessa_21460
Vanessa_21460 Altair Community Member
edited February 2020 in Community Q&A

Hello All,

I have a process in which we get reports that are in Excel 97-2003 file format after 65536 rows of data the data is input on sheet two of the same file so on and so forth. Depending on the day I can have a file with 3 sheets, the next day it can have 30 sheets. Sheet 1 has a header sheets 2 through XX have the data that needs to be appended to the original sheet with no new headers. I can build a macro but wanting to see if Monarch complete might have a unique way to do this already built in. Anyone have any recommendations on possible solutions?

I would love to have this on the server but am open to any suggestions.

 

------------------------------
Vanessa Tanner
Manager Optimization Services
Gap Inc Store Labs
San Francisco NM
(505) 462-0455
------------------------------
Tagged:

Answers

  • Baba_Majekodunmi_703
    Baba_Majekodunmi_703
    Altair Employee
    edited February 2020
    Hi @Vanessa Tanner!

    My first question is about the actual data source itself. I suspect that the underlying data source is a database of some sort. If this is the case, it would be preferred and ideal to connect directly to the data source via Monarch Complete and Automator.

    If you can't connect to the database then you'll have to work with the different excel output files. The Append helper in Monarch complete will automate the work for combining all of the data across the various sheets, but this will need to be done manually for each spreadsheet. Good news is that it will be a fast manual process,  the challenge is how many of these spreadsheets do you get daily?

    Does this help?

    ------------------------------
    Baba Majekodunmi
    Solutions Consultant
    Altair Engineering Inc.
    Manassas VA
    978-275-9325
    ------------------------------
    "
  • Steve Caiels_21881
    Steve Caiels_21881
    Altair Employee
    edited February 2020

    Hi Vanessa,

    In the spirit of this being a forum that others may reference in the future, I echo Baba's suggestion of investigating the upstream process.

    On the rare occasions when the data is not in a friendly format for Monarch, it's always worth checking! If connecting to the underlying database is not possible, maybe a change in export format to xlsx, accdb or csv is possible?

    I believe we have some new features in development that *may* help with the multiple sheet issue, but I'm afraid I don't have details at present.

    Regards,

    Steve.

    ------------------------------
    Steve Caiels
    Professional Services
    Altair
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 02-25-2020 11:44 AM
    From: Vanessa Tanner
    Subject: Importing reports that vary

    Hello All,

    I have a process in which we get reports that are in Excel 97-2003 file format after 65536 rows of data the data is input on sheet two of the same file so on and so forth. Depending on the day I can have a file with 3 sheets, the next day it can have 30 sheets. Sheet 1 has a header sheets 2 through XX have the data that needs to be appended to the original sheet with no new headers. I can build a macro but wanting to see if Monarch complete might have a unique way to do this already built in. Anyone have any recommendations on possible solutions?

    I would love to have this on the server but am open to any suggestions.

     

    ------------------------------
    Vanessa Tanner
    Manager Optimization Services
    Gap Inc Store Labs
    San Francisco NM
    (505) 462-0455
    ------------------------------
    "
  • Baba_Majekodunmi_703
    Baba_Majekodunmi_703
    Altair Employee
    edited March 2020
    Hi @Vanessa Tanner,

    Were you able to find a solution that worked for this?

    ------------------------------
    Baba Majekodunmi
    Solutions Consultant
    Altair Engineering Inc.
    Manassas VA
    978-275-9325
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 02-25-2020 11:44 AM
    From: Vanessa Tanner
    Subject: Importing reports that vary

    Hello All,

    I have a process in which we get reports that are in Excel 97-2003 file format after 65536 rows of data the data is input on sheet two of the same file so on and so forth. Depending on the day I can have a file with 3 sheets, the next day it can have 30 sheets. Sheet 1 has a header sheets 2 through XX have the data that needs to be appended to the original sheet with no new headers. I can build a macro but wanting to see if Monarch complete might have a unique way to do this already built in. Anyone have any recommendations on possible solutions?

    I would love to have this on the server but am open to any suggestions.

     

    ------------------------------
    Vanessa Tanner
    Manager Optimization Services
    Gap Inc Store Labs
    San Francisco NM
    (505) 462-0455
    ------------------------------
    "
  • Vanessa_21460
    Vanessa_21460 Altair Community Member
    edited March 2020
    Baba Majekodunmi_21731 said:

    Hi @Vanessa Tanner,

    Were you able to find a solution that worked for this?

    ------------------------------
    Baba Majekodunmi
    Solutions Consultant
    Altair Engineering Inc.
    Manassas VA
    978-275-9325
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 02-25-2020 11:44 AM
    From: Vanessa Tanner
    Subject: Importing reports that vary

    Hello All,

    I have a process in which we get reports that are in Excel 97-2003 file format after 65536 rows of data the data is input on sheet two of the same file so on and so forth. Depending on the day I can have a file with 3 sheets, the next day it can have 30 sheets. Sheet 1 has a header sheets 2 through XX have the data that needs to be appended to the original sheet with no new headers. I can build a macro but wanting to see if Monarch complete might have a unique way to do this already built in. Anyone have any recommendations on possible solutions?

    I would love to have this on the server but am open to any suggestions.

     

    ------------------------------
    Vanessa Tanner
    Manager Optimization Services
    Gap Inc Store Labs
    San Francisco NM
    (505) 462-0455
    ------------------------------
    "
    Hello Baba,

    Unfortunately I am having to take this outside of Monarch Complete to do. I am utilizing a macro to combine the data and using a database to manipulate the data. Hopefully Monarch will roll out some new features soon so I can re-visit the process. I did evaluate opportunities upstream before looking at Monarch as a solution unfortunately they are unable to modify they way they are sending us the data without some substantial fee's being incurred by us. As always thank you for trying to help us solution better ways to do business.

    ------------------------------
    Vanessa Tanner
    Manager Optimization Services
    Gap Inc Store Labs
    San Francisco NM
    (505) 462-0455
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 03-12-2020 10:58 AM
    From: Baba Majekodunmi
    Subject: Importing reports that vary

    Hi @Vanessa Tanner,

    Were you able to find a solution that worked for this?

    ------------------------------
    Baba Majekodunmi
    Solutions Consultant
    Altair Engineering Inc.
    Manassas VA
    978-275-9325
    ------------------------------

    Original Message:
    Sent: 02-25-2020 11:44 AM
    From: Vanessa Tanner
    Subject: Importing reports that vary

    Hello All,

    I have a process in which we get reports that are in Excel 97-2003 file format after 65536 rows of data the data is input on sheet two of the same file so on and so forth. Depending on the day I can have a file with 3 sheets, the next day it can have 30 sheets. Sheet 1 has a header sheets 2 through XX have the data that needs to be appended to the original sheet with no new headers. I can build a macro but wanting to see if Monarch complete might have a unique way to do this already built in. Anyone have any recommendations on possible solutions?

    I would love to have this on the server but am open to any suggestions.

     

    ------------------------------
    Vanessa Tanner
    Manager Optimization Services
    Gap Inc Store Labs
    San Francisco NM
    (505) 462-0455
    ------------------------------
    "

Categories