Skip to main content
Power Automate
    • Connectors
    • Templates
    • Digital process automation
    • Robotic process automation
    • Business process automation
    • Process advisor
    • AI Builder
  • Pricing
  • Partners
    • Blog
    • Documentation
    • Roadmap
    • Self-paced learning
    • Webinar
    • Business process and workflow automation topics
    • Overview
    • Issues
    • Give feedback
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • User groups
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Microsoft Power Automate Community
    • Welcome to the Community!
    • News & Announcements
    • Get Help with Power Automate
    • General Power Automate Discussion
    • Using Connectors
    • Building Flows
    • Using Flows
    • Power Automate Desktop
    • Process Advisor
    • AI Builder
    • Power Automate Mobile App
    • Translation Quality Feedback
    • Connector Development
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Galleries
    • Community Connections & How-To Videos
    • Webinars and Video Gallery
    • Power Automate Cookbook
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Community Engagement
    • Community AMA
    • Community Blog
    • Power Automate Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power Automate Community
    • Galleries
    • Power Automate Cookbook
    • Import Tab Delimited File

    Import Tab Delimited File

    05-29-2020 08:16 AM - last edited 06-29-2020 13:31 PM

    juresti
    Continued Contributor
    3399 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    juresti
    juresti Continued Contributor
    Continued Contributor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    Import Tab Delimited File

    ‎05-29-2020 08:16 AM

     

    Title: Import Tab Delimited File

     

    Description: This flow allows you to import tab delimited files into a destination table.

     

    Detailed Instructions: Import the package attached below into your own environment.

    You need to change the action where I get the file content from, such as sharepoint, onedrive, or other storage locations.

     

    Please read the comments within the flow steps, they should explain using this flow.

     

    If you have number types remove the quotes (around the variables) at this step where the items are appended.

    Notice all my variables have quotes and are all string type. If one were a number type it would not have quotes.

    All column names should remain with quotes. You will also rename your columns here and add or remove.

    Just keep the format - comma after each column name : data item except the last one.

    Also make note of how each column is accessed here with the formula variables('varname')[0], then variables('varname')[1] ... and so on where [0] is the place of the column in your data in order starting from 0.

    zero is one, 1 is two, 2 is three, and so on.

    21.PNG

     

    You will need to modify the JSON schema to match your column names and types, you should be able to see where the column names and types are within the properties brackets.

    There is no need to rebuild the entire schema, simply rename the columns and change their type if needed.

    Also add and remove columns, as this JSON will allow you to select the columns dynamically for writing your data.

     

    The current column names are col0, col1, col2, etc... and I set them all type string.

    The names and types should match your appended json from image above.

    I don't have memorized all the types but there is string, integer, float? maybe? and other and will need to be looked up.

    22.PNG

     

    This step will control how many records you are going to process.

    01.PNG
    The flow is set up to process 100 rows and should not be changed or due to the nesting of loops it will go over the limit.

    You should change the number of loops though. In the pic it is set it to 5 loops. That's 5 x 100 rows = 500 rows.

    So if you have 8,000 rows you would have 80 loops.

     

    Questions: If you have any issues running it, most likely I can figure it out for you.

     

    Anything else we should know: You can easily change the trigger type to be scheduled, manual, or when a certain event occurs.

    The tab delimited file file must be text based as in saved as tab delimited text format.

     

    If your system or excel allows you to export or save as text tab delimited, this should be the correct file type.

     

     

    tab_delimited_import_06-29-20.zip
    Labels:
    • Labels:
    • Solutions
    Message 1 of 13
    3,399 Views
    0 Kudos
    Reply
    • All posts
    • Previous Topic
    • Next Topic
    • « Previous
      • 1
      • 2
    • Next »
    PatrickInCO
    PatrickInCO
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎06-28-2020 11:06 AM

    Hi - This flow only works if my text file has one row. I think I have an problem with the carriage return. The error message I get is below. It is adding a \r into the JSON. 

    The 'content' property of actions of type 'ParseJson' must be valid JSON. The provided value '{
    "rows":{
    "items":[{"FY Wk":"2020W51","QTR":"2020Q4","QTR Age":"0","Group ID":"1RED","Part Num":"1174500","IPN":"ROSE","Location":"CHI","Capacity":"2000","SWAP Commit":"100","ABC":"100","BRD":"100\r"}{"FY Wk":"2020W51","QTR":"2020Q4","QTR Age":"0","Group ID":"1BLUE","Part Num":"1654500","IPN":"TULIP","Location":"DEN","Capacity":"2000","SWAP Commit":"100","ABC":"100","BRD":"100\r"}{"FY Wk":"2020W51","QTR":"2020Q4","QTR Age":"0","Group ID":"1GREEN","Part Num":"1234500","IPN":"ROSE","Location":"TOL","Capacity":"2000","SWAP Commit":"100","ABC":"100","BRD":"100"}
    ]
    }}' cannot be parsed: 'After parsing a value an unexpected character was encountered: {. Path 'rows.items[0]', line 3, position 192.'.

     

    PatrickInCO_0-1593367520368.png

     

    Any ideas on how to fix?

    Message 2 of 13
    2,967 Views
    0 Kudos
    Reply
    juresti
    juresti Continued Contributor
    Continued Contributor
    In response to PatrickInCO
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎06-29-2020 07:42 AM

    Hello @PatrickInCO 

     

    I don't believe the "\r" is causing the issue.

     

    I have run a test and the "\r" is being processed correctly as a return.

     

    I'm thinking there really is a character causing the issue. Anyhow, you would need to find line 3 and count up to character 192.

     

    Don't do that though. I'm uploading a new version today that I've been working on.

     

    It will remove the \r before it is written and will also handle processing more than the limit rows of 5000.

     

    My run with "\r"

     

    30.PNG31.PNG

     

    Message 3 of 13
    2,949 Views
    0 Kudos
    Reply
    juresti
    juresti Continued Contributor
    Continued Contributor
    In response to PatrickInCO
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎06-29-2020 01:40 PM

     

    @PatrickInCO  

     

    I looked at your data closer and the items do not have a comma separating them.

    Something is not correct in the original file? 

    32.PNG

    After the tab file is processed and placed in json array they are separated by commas then.

    It is possible though that version of the automation did not work correctly because my first files did not have "\r" in them so I could not have the problem. This version though handles the "\r" which could be the issue.

     

    I've uploaded a new version of the automation.

     

    It has changes mainly to handle larger data sets.

     

     

     

    Message 4 of 13
    2,933 Views
    0 Kudos
    Reply
    PatrickInCO
    PatrickInCO
    New Member
    In response to juresti
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎07-05-2020 01:06 PM

    Thanks for the response... been busy so haven't had a chance to try the new version. In my raw data I have a CR/LF according to Notepad++. Not sure where the comma comes from? 

     
     
     

    Annotation 2020-07-04 164723.png

     

     

     

    Message 5 of 13
    2,851 Views
    0 Kudos
    Reply
    Ariphmn
    Ariphmn
    Regular Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎09-10-2020 12:46 PM

    This solution MIGHT be exactly what I need in order to finally finish a project that's taken me about 2 weeks... BUT when I try to import  the solution to Power Automate, I get an error:

     

    The solution file is invalid. The compressed file must contain the following files at its root: solution.xml, customizations.xml, and [Content_Types].xml. Customization files exported from previous versions of Microsoft Dynamics 365 are not supported.

     

    Is the solution zip file from a previous version? I've done some google searches and am still having issues. 

     

    Also, thank you so much for posting solutions. I've come across other posts of yours that have been helpful. 

    Message 6 of 13
    2,453 Views
    0 Kudos
    Reply
    juresti
    juresti Continued Contributor
    Continued Contributor
    In response to Ariphmn
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎09-10-2020 12:52 PM

    @Ariphmn 

     

    I'll check the file.

     

    Maybe I need to export a new one, there may have been server changes.

    Message 7 of 13
    2,449 Views
    0 Kudos
    Reply
    petew625
    petew625
    New Member
    In response to Ariphmn
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎09-18-2020 07:45 AM
     
    I'm getting the same type of error:-
    "The solution file is invalid. The compressed file must contain the following files at its root: solution.xml, customizations.xml, and [Content_Types].xml. Customization files exported from previous versions of Microsoft Dynamics 365 are not supported."
     
    is there any chance of an update ?
    Message 8 of 13
    2,390 Views
    0 Kudos
    Reply
    juresti
    juresti Continued Contributor
    Continued Contributor
    In response to petew625
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎09-18-2020 10:27 AM

     

    @petew625 

    @Ariphmn 

    I was able to import it without any issues.

     

    Where are you trying to upload it to? It should import right into Power Automate.

     

    01.PNG02.PNG03.PNG04.PNG

    Message 9 of 13
    2,384 Views
    0 Kudos
    Reply
    petew625
    petew625
    New Member
    In response to juresti
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎09-18-2020 11:35 AM

    Success.

     

    Thanks for coming back.

     

    Apologies but I followed the instructions here which were not appropriate.

    https://docs.microsoft.com/en-us/power-automate/import-flow-solution

     

    My mistake

     

    pete

    Message 10 of 13
    2,379 Views
    0 Kudos
    Reply
    • « Previous
      • 1
      • 2
    • Next »

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Automate
    • Power Virtual Agents

    • Sign up free
    • Sign in

    Browse

    • Templates
    • Connectors
    • Partners

    Downloads

    • Mobile
    • Gateway

    Learn

    • Documentation
    • Learn
    • Support
    • Community
    • Give feedback
    • Blog
    • Pricing

    • © 2022 Microsoft
    • Contact us
    • Trademarks
    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Terms & conditions