We have a "Create CSV Table" action from an array, but no actions to load, read, transform CSV data which many enterprises processes still requires.
Actually, why not using the SAME powerful "Edit SQL data with Power Query" and create a new tool called "Edit CSV with Power Query"? I'm going to create a new idea...
Great idea, but why limit to "CSV"? Just have it be a "text extract" action with a specifiable delimiter, maybe even make it a 1-10 character string, just in case of weird legacy formats. Also, use whitespace as delimiter, and now you get "all the words". Also allow just grabbing the whole line with no delimiter.
You can use Parse CSV action from Plumsail Documents connector. It allows you to convert CSV into an array and variables for each column. Please read this article demonstrating how it works.
@akharns You can do this by setting up a custom Azure function. (You get a million free runs per month.) You can then either set that as a connector or just send a direct HTTP request.
I installed the npm module "csv2json" to the function and then used this as the index.js (where "csvString" is the CSV file set as a string... I'll attach an image.)
module.exports = async function (context, req) {
context.log('JavaScript HTTP trigger function processed a request.');
if (req.body.csvString) {
const csv2json = require('./csv2json.js');
const csv = (req.body.csvString);
const json = csv2json(csv, {parseNumbers: true});
console.log(json);
// status: 200, /* Defaults to 200 */
context.res = {
body: json
};
}
else {
context.res = {
status: 400,
body: "Please pass data into the body."
};
}
context.done();
};
installed npm module with cmd tool here
Overall FlowHTTP Request to FunctionAppParse Data to JSON Object
You can skip the "Transform Data From Parser" step. In my case one of the fields was in the "City, State" format, so I needed to remove that comma in order for the Azure function to work correctly.
From there you can use the "Filter Array" built-in action to filter your data and "length(outputs(your_filter_action))" to return the number of rows that match that criteria.
But, yeah, this should be just built-in to Flow. SO MANY things output CSV files.