How do I format CSV to proper format to be uploaded into Smartsheet using Data Shuttle?

[ljkeefe]

I have vendor data exported into CSV that I'd like to upload into Smartsheet using Data Shuttle. However, the vendor data is exported in this format which the importing tool can't read. :

Is there a way to program Power Automate to automatically grab the CSV from the source, then format it like this, so that it can import into Smartsheet? 

I am not extremely familiar with Power Automate so I have no code to share because I don't know where to even begin. I've created some very simple flows but that's the extent of my skills.

Any help is appreciated.

Thanks!

[grantjenkins · Accepted solution]

This is how I would build the flow to get what you're after.

Below is the CSV file that I want to transform.

Below is the full flow. I'll go into each of the actions.

Get file content using path retrieves the content from the CSV I want to transform. I'm not sure how you are getting this content into your flow.

Compose converts the content to a string using the following expression.

base64ToString(outputs('Get_file_content_using_path')?['body']?['$content'])

Filter array splits the data by carriage return and new line then removes any empty rows (normal get an empty row at the end after splitting the data). The expressions used are:

//From
split(outputs('Compose'), decodeUriComponent('%0D%0A'))

//Condition
item()

Select uses the body of the Filter array and the following expressions. Note that the column names should be called col0, col1, col2, etc. as shown.

split(item(), ',')?[0]
split(item(), ',')?[1]
split(item(), ',')?[2]
split(item(), ',')?[3]
split(item(), ',')?[4]
split(item(), ',')?[5]
split(item(), ',')?[6]
split(item(), ',')?[7]
split(item(), ',')?[8]
split(item(), ',')?[9]
split(item(), ',')?[10]
split(item(), ',')?[11]
split(item(), ',')?[12]

Create CSV table uses the following expressions.

//From
range(1, 12)

//Month (note this is index 1 to get the month)
body('Select')[1][concat('col', item())]

//Year (note this is index 0 to get the year)
body('Select')[0][concat('col', item())]

//Residential
body('Select')[2][concat('col', item())]

//Multifamily
body('Select')[3][concat('col', item())]

//Commercial
body('Select')[4][concat('col', item())]

//Grand total
body('Select')[5][concat('col', item())]

Create file uses the output from Create CSV table to create a new CSV file with the transformed data. Note that I've just added an expression to build up a dynamic date part for the file name - you can do whatever you want here.

formatDateTime(utcNow(), 'yyyyMMdd-hhmmss')

After running the flow I will get a new CSV file that looks like the example below.

[ljkeefe]

@grantjenkins Thank you for the response. I tried the solution and got this error. Any idea why I would get this? I reviewed the inputs and they all look correct.

This is the Month input 

[grantjenkins]

Are you able to show the rest of your flow to try and workout where the issue is?

[ljkeefe]

@grantjenkins I tried my best to snapshot the flow onto a Word doc. I hope this helps.

[grantjenkins]

Everything looks ok. Are you able to run your flow and check what you get as output from each of the actions to try and see if you can identify the root cause. So look at the output from the Compose then Filter array then Select, etc. to see if the data looks to be ok, and where it might be coming back incorrect.

[ljkeefe]

@grantjenkins Thank you for taking a look. I thought it looked correct as well. Yes, I've run the flow and it gets stuck Create CSV (as shown above) and it gives me the array error. I will try again. Thank you!