Forum Discussion

keonijared's avatar
keonijared
Copper Contributor
Apr 07, 2022
Solved

Help for ERM software migration using Excel

Hello everyone.    I have a vendor report spit out from Sage100 that sorted our master vendor list vertically; as in, one vendors info is in a row with columns laid out left>right as ID, name/addre...
BI & Data Analysis
excel
office 365
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Apr 07, 2022

    keonijared 

    This is a first attempt to 'put my money where my mouth is'.  It is not elegant, but I was struggling with the irregularities  in the data.

    LoadDataλ 
= LAMBDA(vendor, idx,
     LET(
        inputCol, INDEX({1, 2, 5, 2, 2, 2, 2, 2, 2, 3, 4}, idx),
        inputEntry, INDEX({0, 0, 0, 1, 2, 3, 4, 5, 6, 0, 0}, idx),
        item, IF(
            inputEntry,
            SplitAddrλ(INDEX(rawData, 2 + 4 * (vendor - 1), 2), inputEntry),
            INDEX(rawData, 1 + 4 * (vendor - 1), inputCol)
        ),
        item
     )
  );

rawData = RawData!$A$5:$E$16;

SplitAddrλ 
= LAMBDA(ref, k,
    INDEX(TEXTSPLIT(ref, , CHAR({44, 10}), FALSE, " "), k)
  );

     

mathetes's avatar
mathetes
Gold Contributor
Apr 07, 2022

keonijared 

 

Interesting. Let's first see if there's a simple way to do this. Do you have any choices on how you receive that data from Sage100?

 

I ask because I've seen something similar happen when I get a report from American Express on our monthly charges: since I'm bringing it into Excel, and they give a choice of downloading their data as Excel or as CSV (comma separated value), I naturally picked "Excel"...but somebody at their end thinks it's helpful to make the Excel download "pretty," so they've added colors and formatting, so that the addresses appear as what you're showing, multiple lines!!! It's NOT exported as an Excel table, which is just stupid, frankly, on their part.

 

But the CSV export from Amex is perfectly useable, and is in the kind of format you are wanting to have.

 

So my first question: can you get the data from Sage100 as CSV or some other format that is closer to what you need in the first place?

keonijared's avatar
keonijared
Copper Contributor
Apr 07, 2022
Excellent question, and yes- it took us HOURS to pull this data from Sage100; any other export format did not include the data that the ERM software company was requesting. This was literally the only way Sage included the minimum fields of data needed.

To top this off, that list started out as just shy of 10,000 vendor entries, and our team had to delete old/unused vendors manually, one by one, to finally curate the file as it is now. If we attempted to re-run reports, we would have to start from scratch, basically, putting us possible weeks over deadline.

I absolutely abhor Sage's UI.

Resources