cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fanatic
Helper I
Helper I

Help with convert excel based csv to xlsx

Dear,

 

hopefully someone have an idea or workaround how I can convert a csv into a usefull format that I can use the excel connector "Create Table" and "List rows in present table".  Currently , I'm struggle with it because I can only use default connectors.

 

Thank you for any idea's 

2 ACCEPTED SOLUTIONS

Accepted Solutions

It' much easier as I thought.

 

1. Copy the csv to "Onedrive" make the file text-based

2). Split csv by carriage return , returns the available lines in my csv

3). Loop, carriage return result,

4). Split csv again based on the delimitier

5. Now you can write back the array to a pre-defined excel template with, "Add row to table"

 

Unbenannt.PNG 

View solution in original post

Fanatic
Helper I
Helper I

How much detail you need ?

1. I receive my csv file as email attachment from an automatic system report, which is my trigger. At first a save the attachment to my onedrive , because the csv is not readable from email. When you save it to onedrive and reopen it its a normal txt file. In additional I can only use standard connector
In my case I need an extra step, because the csv file is inside a Zip Archive.  After the csv is saved to my onedrive , I save the File Id inside a variable

Fanatic_0-1658424516702.png

 

2).  I use get file from onedrive with m,y save id to get the csv as txt file. Then I use a filter array, with split csv file by newline to split each csv row into one line. Because a CSV file ends always with /n  .  And item() is not equal is checking empty lines. So I exclude each empty line.

Please note that the function "split" is limit to your licence . In my basic E3 licence my csv file can't be bigger as 5000 rows. Everything else is not catched by the split funtion.

 

Split(body(<csv file>), variable('Newline'))

 

Afterwards I use a select action to format my data for each column, to remove unexpected character.

For each column selection  , I use split the row again by my delimiter

split(item(),'<delimiter>')[0] = Column A

split(item(),'<delimiter>')[1] = Column B

split(item(),'<delimiter>')[2] = Column C

 

After that you can write your data into sharepoint or else. I have only one problem in my systems, when any data column contains a "enter character" or " /n" . then the csv can't be split correctly.  I'm still searching an idea for that

Fanatic_1-1658424737666.png

 

View solution in original post

4 REPLIES 4
v-bacao-msft
Community Support
Community Support

 

Hi @Fanatic ,

 

As far as I know, we need to rely on the Premium connector to convert csv to xlsx. Like Cloudmersive Document Conversion connector and Plumsail Documents connector.

 

Best Regards,

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

It' much easier as I thought.

 

1. Copy the csv to "Onedrive" make the file text-based

2). Split csv by carriage return , returns the available lines in my csv

3). Loop, carriage return result,

4). Split csv again based on the delimitier

5. Now you can write back the array to a pre-defined excel template with, "Add row to table"

 

Unbenannt.PNG 

ObiShyamKenobi
Frequent Visitor

Hi @Fanatic 
If you still have this flow, can you provide more details for each of the steps please?

 

Thanks.

Fanatic
Helper I
Helper I

How much detail you need ?

1. I receive my csv file as email attachment from an automatic system report, which is my trigger. At first a save the attachment to my onedrive , because the csv is not readable from email. When you save it to onedrive and reopen it its a normal txt file. In additional I can only use standard connector
In my case I need an extra step, because the csv file is inside a Zip Archive.  After the csv is saved to my onedrive , I save the File Id inside a variable

Fanatic_0-1658424516702.png

 

2).  I use get file from onedrive with m,y save id to get the csv as txt file. Then I use a filter array, with split csv file by newline to split each csv row into one line. Because a CSV file ends always with /n  .  And item() is not equal is checking empty lines. So I exclude each empty line.

Please note that the function "split" is limit to your licence . In my basic E3 licence my csv file can't be bigger as 5000 rows. Everything else is not catched by the split funtion.

 

Split(body(<csv file>), variable('Newline'))

 

Afterwards I use a select action to format my data for each column, to remove unexpected character.

For each column selection  , I use split the row again by my delimiter

split(item(),'<delimiter>')[0] = Column A

split(item(),'<delimiter>')[1] = Column B

split(item(),'<delimiter>')[2] = Column C

 

After that you can write your data into sharepoint or else. I have only one problem in my systems, when any data column contains a "enter character" or " /n" . then the csv can't be split correctly.  I'm still searching an idea for that

Fanatic_1-1658424737666.png

 

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events happening this month - don't miss out!

 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (2,857)