Workaround for XLSM issues

Question

My company receives a daily CDR report from Comcast and I'm trying to get this data into our database in an automated fashion. Unfortunately, Comcast has advised there is a 0% chance they send us a CSV or XLSX or anything that is NOT an XLSM. For some context, Comcast's CDR report is several sheets but I need only the data from Sheet1 (called All CDR) and there are NO macros in sheet1.

So, I have been working all week on a workaround. During testing I was able to get this code to work:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Macro;
Database=\\\fileserver\Comcast.xlsm;IMEX=1;HDR=YES','select * from [All CDR$]')

NOTE: This only reads the data, I will turn it into an INSERT statement once working.

But I found out that this code ONLY works if the file has been opened and saved. I.e. I had to open the file, enable content, then Excel asks "Do you want to make this file a trusted document?" which I click Yes, then save and exit. Then the code works.

So how can I work around this awful issue courtesy of Comcast? I have Power Automate at my disposal as well as SQL Server and other standard business tools. I have tried using power automate to read the excel file but this has failed as Sharepoint would not recognize the XLSM files, only XLSX. I wanted to use power automate to open the Excel file, save it and close it but I do not think this is possible. Thanks in advance

If you know the file has no macros try just renaming it to .XLSX. I tried your query on a .XLSM file that I created then renamed to .XLSX and the provider opened it just fine. — squillman, Jan 20 '22 at 22:13
Maybe open as `Excel 12.0;` not `Excel 12.0 Macro;`. Or barring that unblock the file using `Unblock-File` in Powershell — Charlieface, Jan 21 '22 at 02:40
@squillman - there is a macro in the file so renaming it to XLSX or CSV "corrupts" the file so to say. thank you though! — Bruno, Jan 21 '22 at 13:06
@Charlieface - unblocking the file did not allow my script (same as the OP) to work...thank you though! I tried unblocking files directly and the entire folder but no luck. — Bruno, Jan 21 '22 at 13:15

score 1 · Answer 1 · answered Jan 20 '22 at 22:20

If you can't find a nicer way then you can do this using an Office Script. It may or may not work as you need it but you can work through that.

If you go into Excel Online (any workbook) under the user that will run the script in PowerAutomate, you should have an option in the ribbon called Automate.

Find it and create a new script called ... Get All Data from Worksheet

Paste this code in ...

function main(workbook: ExcelScript.Workbook, worksheetName: string)
{
  let worksheet = workbook.getWorksheet(worksheetName);
  let usedRange = worksheet.getUsedRange(true);

  return usedRange.getValues();
}

Now, you can call that from PowerAutomate using the Run script action under Excel Online (Business) ...

Action

Result

JSON Response

[
  [
    "Header 1",
    "Header 2",
    "Header 3",
    "Header 4",
    "Header 5",
    "Header 6",
    "Header 7"
  ],
  [
    "21",
    "22",
    "23",
    "24",
    "25",
    "26",
    "27"
  ],
  [
    "31",
    "32",
    "33",
    "34",
    "35",
    "36",
    "37"
  ],
  [
    "41",
    "42",
    "43",
    "44",
    "45",
    "46",
    "47"
  ],
  [
    "51",
    "52",
    "53",
    "54",
    "55",
    "56",
    "57"
  ],
  [
    "61",
    "62",
    "63",
    "64",
    "65",
    "66",
    "67"
  ],
  [
    "71",
    "72",
    "73",
    "74",
    "75",
    "76",
    "77"
  ],
  [
    "81",
    "82",
    "83",
    "84",
    "85",
    "86",
    "87"
  ],
  [
    "91",
    "92",
    "93",
    "94",
    "95",
    "96",
    "97"
  ],
  [
    "101",
    "102",
    "103",
    "104",
    "105",
    "106",
    "107"
  ],
  [
    "111",
    "112",
    "113",
    "114",
    "115",
    "116",
    "117"
  ]
]

From there, you can do what you need with the data.

Workaround for XLSM issues

1 Answers1

Action

Result

JSON Response