cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SteveODea
Frequent Visitor

Import text file into Sharepoint List

Hi, I'm fairly new to Power Automate and need some help getting out of the starting blocks with this particular problem.

 

Essentially, I have a fixed width text file which I receive from a 3rd party on a daily basis which I need to import into a SharePoint list for which I have already defined the Columns (Col1, Col2, etc)

 

The text file is (oddly) named AB.CE and has no headers and the data contained within needs to be split based on the following:

 

Col 1 - Characters 1-8

Col 2 - Characters 9-10

Col 3 - Characters 11-15

Col 4 - Characters 16-21

Col 5 - Characters 22-55

Col 6 - Characters 56-72

Col 7 - Characters 73-81

 

Can anyone give me a steer as to how to get power automate to read the data so that I can feed this into a Create Items Action?

 

My data file looks like this:

 

Untitled.jpg

 

1 ACCEPTED SOLUTION

Accepted Solutions
Jronash
Responsive Resident
Responsive Resident

You'll have to use some of the expressions to accomplish this.  I don't have enough info to show you exactly how to accomplish it, but I can point you in the direction of the tools you will need to use.

 

First, you'll need to take your text input and convert it into an array with each line as it's own item.  You can accomplish this with the split() expression.  The split() expression takes two parameters - the first is the text string, and the second is the separator.  I'm guessing that in your data the separator will just be the new line code.  So the expression will look something like this:

split(output('Your_Data'), '\n')

 

Now you have an array with each line separated, but you need to split each line into columns.  The best way to do this is to use a Select action.

substring.png

 

The Select action processes each row in your array, and here we are using it to create a new array with columns.  On the left are the column header names, and on the right is an expression that selects the part of the data that we want.  I'm using substring(), which takes three parameters.  The input string, the start index, and the length.  Within a Select block, you can use the expression item() to refer to the current row of the array.  So going from what you posted, the expression to extract the first column would be:

substring(item(),0,8)

For the second column, it would be:

substring(item(),9,2)

And so on.

 

Hopefully that's enough to get you started.

 

View solution in original post

2 REPLIES 2
Jronash
Responsive Resident
Responsive Resident

You'll have to use some of the expressions to accomplish this.  I don't have enough info to show you exactly how to accomplish it, but I can point you in the direction of the tools you will need to use.

 

First, you'll need to take your text input and convert it into an array with each line as it's own item.  You can accomplish this with the split() expression.  The split() expression takes two parameters - the first is the text string, and the second is the separator.  I'm guessing that in your data the separator will just be the new line code.  So the expression will look something like this:

split(output('Your_Data'), '\n')

 

Now you have an array with each line separated, but you need to split each line into columns.  The best way to do this is to use a Select action.

substring.png

 

The Select action processes each row in your array, and here we are using it to create a new array with columns.  On the left are the column header names, and on the right is an expression that selects the part of the data that we want.  I'm using substring(), which takes three parameters.  The input string, the start index, and the length.  Within a Select block, you can use the expression item() to refer to the current row of the array.  So going from what you posted, the expression to extract the first column would be:

substring(item(),0,8)

For the second column, it would be:

substring(item(),9,2)

And so on.

 

Hopefully that's enough to get you started.

 

View solution in original post

Many thanks @Jronash  this just what I need to get going

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (30,468)