cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mmorley
Resolver I
Resolver I

Help needed for reading text file and adding each line into array for processing

Hi,

 

I've been looking at various threads regarding importing csv files, with all recommending to use another paid plugin of which this is not an option and trying to create my own work around

 

I have got flow to copy the file from csv to txt

Read the contents of the txt file

 

If i then use the compose option on the file contents, this brings in the data in a single line with the line feeds converted to \r\n

If I create an array variable and then append the file contents this comes through as though its in a list (that's how it is shown) but when use this on appy to each the entry is a single line

 

How do I get the compose/append array to show the lines as individual entries so i can then cycle through these and break these down further and add to various tables depending on certain entries.

 

thanks

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

Hi @mmorley,

 

The connector is security, and it is 50 times for a month if the license is free. If your CSV file is not much, such as 1 file per day, it is an ideal connector.

 

If you would like to do this all in Flow so not relying on other connectors, you could refer to this blog, it will help you convert the CSV data into a object:

https://sergeluca.wordpress.com/2018/10/28/microsoft-flow-advanced-tutorial-creating-a-csv-converter...

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi @v-litu-msft 

 

I may have found the solution so just performing some tests

 

the info supplied on this thread helped me out

https://powerusers.microsoft.com/t5/Building-Flows/Txt-file-to-Excel-table/td-p/475549

 

Thanks

 

View solution in original post

6 REPLIES 6
v-litu-msft
Community Support
Community Support

Hi @mmorley,

 

You could use the Parse CSV action of Encodian connector to do it, then use json() function to convert it into an array:

Annotation 2020-05-08 105055.png

 Run result:

Annotation 2020-05-08 105545.png

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

thanks,

 

but how secure is that connector?

what do they class as actions - number of requests made to the connector a month? number of items in a file?

would also need authorisation from our employers if we can use this, seeing if we go over a limit there are charges

 

ideally would like to do this all in house so not relying on other connectors than the standard in flow/premium in flow

Hi @mmorley,

 

The connector is security, and it is 50 times for a month if the license is free. If your CSV file is not much, such as 1 file per day, it is an ideal connector.

 

If you would like to do this all in Flow so not relying on other connectors, you could refer to this blog, it will help you convert the CSV data into a object:

https://sergeluca.wordpress.com/2018/10/28/microsoft-flow-advanced-tutorial-creating-a-csv-converter...

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

hi @v-litu-msft,

 

Thank you very much for this, I will look at the solution provided to create it in flow myself

 

The problem is we maybe looking at 4 short csv files initially, but as things progress this may become more and could go above the 50 per month of which I would need to put in a full business case of why we need rely on a 3rd party connector if we could do ourselves within flow.

 

@v-litu-msft been trying to create the flow for csv which you linked to and can't even get the basic working without it constantly throwing an error 😞 or not doing what i want it to do

 

 

I copy the csv file to a txt file so is displayed correctly as otherwise flow sees it as a stream and won't allow anything else to work

I've created a JSON Object with the initial value set to {} to stop errors

I've added a compose that has the outputs from 'get file contents' and this lists all the entries

But I can't get this information into a table or array for process, nor can i process this using an 'apply to each' loop and setting a variable to the result just so can see if working

if i use the option to create csv file and put the 'from' to outputs from the data operation that get the file contents it returns an error saying it must be of type array and not string

 

if i try to split the contents using the expression split(Outputs('CSV_Data'),'\r\n') or ',' this fails and get the full list as a single entry

 

 

 

 

 

 

Hi @v-litu-msft 

 

I may have found the solution so just performing some tests

 

the info supplied on this thread helped me out

https://powerusers.microsoft.com/t5/Building-Flows/Txt-file-to-Excel-table/td-p/475549

 

Thanks

 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (1,935)