Sharing my Power Automate template: RE:Queue CSV to SharePoint Excel Table

Alex Wong

Description:

This flow shows how to take a csv file generated by RE:Queue and convert and save into SharePoint as Excel file with the data in the excel being formatted as table for easily consumption by other flow.

Required connections:

• Excel Online (Business)
• SFTP - SSH
• SharePoint
• Microsoft Teams
  • this is optional, the flow include the use of MS Teams chatbot to send a chat message if the flow processing has error

Suggested skill level required to use this template: Intermediate

Configuration details:

• ONE TIME SETUP: Setup sFTP connector (our RE NXT is hosted on Azure, so instruction is Azure hosting based)
  • Go to your RE NXT > Database options, here under FTP access account information you will find: User name, Password, Server, and Port info to use on the setup of the SFTP - SSH connector in Power Automate.
  • You can leave SSH private key, SSH private key passphrase, SSH host key finger-print, and Root folder path blank.
  • Check the checkbox for Disable SSH host key validation
• ONE TIME SETUP: Need a blank excel file setup at a SharePoint location first
  • Open excel, and delete any other worksheet your excel setting may automatically create and leave ONLY Sheet1
  • Save the excel file at a SharePoint location accessible by you
• ONE TIME SETUP: Need the Excel Script saved to your MS user account
  • While you have the excel file open from above step, click on the Automate menu at the top and then click on New Script (see image 1 below)
  • A Code Editor would appear on the right-panel of Excel, which you can highlight to delete the default code that's there, give it a name (I just name it CSV2Excel) and copy and paste below code into the code editor body area and click Save script (see image 2 below)

image 1

image 2

function main(workbook: ExcelScript.Workbook, csv: string) {

/* Convert the CSV data into a 2D array. */

// Trim the trailing new line.

csv = csv.trim();

// Split each line into a row.

let rows = csv.split("\\r\\n");

let data: string[][] = [];

rows.forEach((value) => {

/*

* For each row, match the comma-separated sections.

* For more information on how to use regular expressions to parse CSV files,

* see this Stack Overflow post: https://stackoverflow.com/a/48806378/9227753

*/

let row = value.match(/(?:,|\\n|^)("(?:(?:"")*[^"]*)*"|[^",\\n]*|(?:\\n|$))/g);

// Remove the preceding comma.

row.forEach((cell, index) => {

row[index] = cell.indexOf(",") === 0 ? cell.substr(1) : cell;

if (row[index].startsWith("\\"")) {

row[index] = row[index].substr(1,row[index].length-2);

}

});

data.push(row);

});

// Put the data in the worksheet.

let sheet = workbook.getWorksheet("Sheet1");

let range = sheet.getRangeByIndexes(0, 0, data.length, data[0].length);

range.setValues(data);

// Create Table

let newTable = sheet.addTable(range, true);

// Autofit rows and columns

newTable.getRange().getFormat().autofitColumns();

newTable.getRange().getFormat().autofitRows();

// Returns # of records in body

return data.length;

}

Category: Automation, Data Management

Instructions:

• After import the flow attached, and link the connector correctly, do the following to the flow:
  • Update the Hosted File location where you export RE:Queue files are
  • Update the blank excel file on SharePoint in action Get Excel Template
  • Update the Create Excel File action to where you want to save the new excel with the csv data
  • Update Run Script to Copy CSV to Excel pointing to the file Id of the Create Excel file action step and select the CSV2Excel Script

CommunityDemo-REQueueCSVtoSharePointExcel_20230515124231.zip

Alex Wong

This is the look of the excel file after the flow is done:

Heather McLean

@Alex Wong - Would you like to share at the User Group on Tuesday?

Alex Wong

@Heather McLean
sure

Lindsey Copeland

@Alex Wong Hey Alex!

I got all this set up, and Power Automate shows no errors in my flow. However, I think because my environment is not Azure hosted, I haven't quite figured out how to point directly to our SFTP and the appropriate folder I need to pull files from.

Any recommendations? Options? Other articles that may help make this work for us? I appreciate any help you can give Making this work would be a game-changer for us!\\

Alex Wong

@Lindsey Copeland
can you screenshot what the FTP access account information tile in the Database options? make sure to cross out the username and password. or you can paste just the server url

Lindsey Copeland

@Alex Wong

Alex Wong

@Lindsey Copeland
yup, definitely blackbaud hosting, not azure.

what you can do is to download and use a FTP client, such as FileZilla, after logging in, you will be able to “drill down” to the folder path where you want Power Automate to monitor. Then you just have to copy the path from FileZilla

JR Barden

@Alex Wong
Can't get the Import button to become active. Under related resources, BB sFTP-New/SFTP - SSH Connection, option is to select during import but when I click on that, I just have the option to Create New and when I click on that I just get a list of connections. What do I pick there?

Alex Wong

@JR Barden
If you never established a SFTP connection to your Blackbaud environment's SFTP site, then you will have to first do that and then can select it in the import process.

the connection to use is SFTP - SSH

JR Barden

@Alex Wong Well shoot! I got it to work once, but then it stopped and won't let me open the files in the FTP folder. So the flow can't move them from the FTP site to sharepoint. :-(

Alex Wong

@JR Barden
the screenshot you show is a “remote” file explorer that you open when click on Hosted File, you will NOT be able to open ANY file using any program when in the “remote” file explorer. So the error message is not related to the flow.

If you want to manually move file from remote file explorer to sharepoint, you will need to create onedrive shortcut to the sharepoint location where it becomes available on your “local file explorer” first. Then from there in the remote file explorer, you can “move” or copy the csv file to the network mapped C:\\ of your local drive.

However, what I explained above is simply trying to get file manually. Has nothing to do with the flow that automates the csv file to sharepoint. So i'm not sure what you mean by “got it to work once”.

Candy Reichert

@Alex Wong
Thank you for sharing this flow. I have it installed and it seems to be working with a couple issues.

1. It is not exporting any more lines into the excel than the original run even though the FTP file includes more lines.
2. The flow show as successful on the flow history, however, it still says there is an error. This is what I see on the Triger error:
   
   {

"error": {

"code": 504,

"source": "flow-apim-msmanaged-na-northcentralus-01.azure-apim.net",

Can you help me figure this out?

Thank you, in advance for all your help.

Alex Wong

@Candy Reichert
going to need to to look at a run history and post the action that failed and the same action in edit mode.

Note that there's no “append”. the flow (if i remember correctly as I don't use this anymore) pick up the entire csv file that was “modified” (new file saved overwriten by RE:Queue) and will add to a NEW excel file (if you didn't modify the flow too muich). never meant to be an “update” to the excel

Karen Mitchell

@Alex Wong Hi Alex - do you have a video tutorial I can look at? We're just using Queue and need to automatically move files from the Tenant File Share into our local servers and not sure where to start. Thanks.

Alex Wong

@Karen Mitchell
I did not make a video tutorial.

I don't remember if I ever demo this in a Power Automate User Group though. @Erik Leaver @Heather McLean maybe able to help locate it if I did.