Skip to main content
Community site session details

Community site session details

Session Id : 17Cl3Yxu7HVX9pnUvgWB7L
Power Automate - Building Flows
Unanswered

Access CSV file on FTP, Amend Data and resave on the same FTP server.

Like (0) ShareShare
ReportReport
Posted on 11 Jun 2022 16:53:46 by

Hi, I have a CSV file that I can save/place on an FTP Server, eventually this will be accessed by a Magento 2 website and used to update stock levels.

 

The file is pretty simple, just two columns, SKU and Quantity (Quantity in Stock), but here's my issue.  Magento 2 doesn't know what to do with stock levels that are not numbers, and some of my suppliers send through the info with words in the stock level feed like "none" or "not available", so I need Power Automate to look at this column and change anything thats not a number into a number, this could be done with lots of different "Find and Replace" type actions, if this is possible, that I could build individually.

 

Once the changes have been made then I just need it saved and Magento will read it from there.

 

I am a complete newbie and was just wondering if Power Automate was capable of doing this?  Is it easy enough to build a flow for this?  Any help would be greatly appreciated.

 

Thanks,

 

Neil.

  • eliotcole Profile Picture
    4,266 Moderator on 11 Jun 2022 at 18:04:30
    Re: Access CSV file on FTP, Amend Data and resave on the same FTP server.

    Hi, @NAMCKNIGHT, to answer your questions directly:

    1. Yes, this can be done with Power Automate.
    2. Yes, you can start to try, by taking the file and trying some stuff with it in Power Automate.

     

    😉

     

    If you wanted some more specific help with a flow, I would say a good place to start would be:

    1. Try some stuff ... fiddle with it (now that you know it can) and try anything! It's just logic, and you search for what you want to do. 🙂
    2. Then show us what you have tried so far by pasting screen shots of the flow in to your question and include a copy or example of some of the CSV data. 🤓 (obfuscate where necessary)

     

    For example, are the numbers (when there are no words) in the CSV within quotes, or are they just the number? Not all CSV files are made the same. 👍

     

    A CSV could look like any of the examples in the below spoiler (but often the first):

    Spoiler (Highlight to read)
    SKU,"Quantity (Quantity in Stock)"
    dfdf79873hjhh,1
    dfs0987f3hr,2
    sfosidfhsd90808,"none"
    sfdsf987987,"nothing"
    "SKU","Quantity (Quantity in Stock)"
    "dfdf79873hjhh","1"
    "dfs0987f3hr","2"
    "sfosidfhsd90808","none"
    "sfdsf987987","nothing"
    SKU,"Quantity (Quantity in Stock)"
    dfdf79873hjhh,"1"
    dfs0987f3hr,"2"
    sfosidfhsd90808,"none"
    sfdsf987987,"nothing"


    If it's the first example, then the below VERY SPECIFIC Select action will work.

    That is until an SKU gets a comma in it, or more columns start to be returned. But I would not recommend you trying this without a little more time under your belt ... or by browsing the forum a little bit. I think I've answered the question you're asking (there or there abouts) recently elsewhere. 🙂

    From
    skip(
     split(
     YOUR_CSV_INPUT, 
     variables('carriageVAR')
     ), 
     1
    )
    Map
    KEY VALUE
    SKU
    split(
     item(), 
     ','
    )[1]
    QuantityInStock
    if(
     equals(
     string(
     xpath(
     xml(
     json(
     concat(
     '{"value":', 
     split(item(), ',')[1], 
     '}'
     )
     )
     ), 
     'number(*/text())'
     )
     ), 
     'NaN'
     ), 
     0, 
     if(
     contains(split(item(), ',')[1], '.'), 
     float(split(item(), ',')[1]), 
     int(
     split(
     item(), 
     ','
     )[1]
     )
     )
    )

    You need to have defined a string variable called carriageVAR that simply has a new line in it (press ENTER once), and those expressions must be entered into the expression editor.

     

    As you may realise, a lot of this is quite advanced, and even then it can break. It's possible to make it sway to anything, but it gets more complex.

    SKU,"Quantity (Quantity in Stock)" dfdf79873hjhh,1 dfs0987f3hr,2 sfosidfhsd90808,"none" sfdsf987987,"nothing" "SKU","Quantity (Quantity in Stock)" "dfdf79873hjhh","1" "dfs0987f3hr","2" "sfosidfhsd90808","none" "sfdsf987987","nothing" SKU,"Quantity (Quantity in Stock)" dfdf79873hjhh,"1" dfs0987f3hr,"2" sfosidfhsd90808,"none" sfdsf987987,"nothing" If it's the first example, then the below VERY SPECIFIC Select action will work. That is until an SKU gets a comma in it, or more columns start to be returned. But I would not recommend you trying this without a little more time under your belt ... or by browsing the forum a little bit. I think I've answered the question you're asking (there or there abouts) recently elsewhere. From skip( split( YOUR_CSV_INPUT, variables('carriageVAR') ), 1 ) Map KEY VALUE SKU split( item(), ',' )[1] QuantityInStock if( equals( string( xpath( xml( json( concat( '{"value":', split(item(), ',')[1], '}' ) ) ), 'number(*/text())' ) ), 'NaN' ), 0, if( contains(split(item(), ',')[1], '.'), float(split(item(), ',')[1]), int( split( item(), ',' )[1] ) ) ) You need to have defined a string variable called carriageVAR that simply has a new line in it (press ENTER once), and those expressions must be entered into the expression editor.   As you may realise, a lot of this is quite advanced, and even then it can break. It's possible to make it sway to anything, but it gets more complex.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for…

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 497 Super User 2025 Season 2

#2
David_MA Profile Picture

David_MA 436 Super User 2025 Season 2

#3
Riyaz_riz11 Profile Picture

Riyaz_riz11 244 Super User 2025 Season 2

Loading complete