cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
n8greene
Advocate II
Advocate II

Create CSV Table - How to add quote marks around a value to handle leading zeros

Hi- My flow creates a CSV file that the user will open in Excel. One of the columns is zip code, which sometimes has a leading zero, for example 04101 When the user opens the CSV in Excel, it trims the leading zero off the zip code and displays 4101. The best solution I can think of is wrapping the value in quote marks on the CSV but I can't figure out how to do that. I tried: Concat('"',item()?['PracticeZip'],'"') but it returned """04101""" Obviously, the value I want printed on the CSV is "04101" with the quotes but without quotes around the quotes. Any suggestions on how to get this to work? Thanks
10 REPLIES 10
v-yuazh-msft
Community Support
Community Support

Hi @n8greene,

 

You could take a try with expression below:

concat('"',item()?['PracticeZip'],'"')

I have made a test on my side to create a flow as below:

Capture.PNG

 

The expression in the Compose 2 as below:

concat('"',outputs('Compose'),'"')

The flow would run successfully as below:

Capture.PNG

 

Best regards,

Alice

Hi Alice-

Thanks for the response, but the solution you provided does not work when pulling values from a SharePoint list and then placing them into a CSV.

 

To restate the problem, I want the csv export to look like this (for example):

Smith,John,16 Main St.,Portland,ME,"04101",Anesthesiologist, etc.

(I need the zip code  in quotes so preceeding zeros will not be stripped off when opened in excel. )

The solution you provided casues this result:

Smith,John,16 Main St.,Portland,ME,"""04101""",Anesthesiologist, etc. (this does not render right in excel)

 

I've pasted my test flow below to demonstrate. The value in the sharepoint list is simply 04106.

Test  Flow:

2019-01-14 09_09_05-Edit your flow _ Microsoft Flow.png

Result:

Pic2.png

 

Pic3.png

Hi @n8greene,

 

Would you please add a () to the value as below:

Capture.PNG

Capture.PNG

 

Or you could add a space before the value as below:

Capture.PNG

Capture.PNG

 

Best regards,

Alice

 

Anonymous
Not applicable

I have this issue too, has anyone found a solution for this?

MarkStokes
Skilled Sharer
Skilled Sharer

I feel like this issue might not be a Flow issue, but rather an Excel issue. When you open the CSV file in excel (without surrounding the zip code with quotes) can you set the column data type to be Text so it just evaluates the content it finds literally?
Anonymous
Not applicable

Hello,

 

the Flow I'm trying to build gets data from SharePoint and uploads a CSV to SFTP where the file will be processed by one of our Finance systems. This systems expects every value in the CSV to be surrouned by double quotes so

 

"1","Name","Address"

 

I open the CSV in Notepad, rather than Excel to see the format.

 

My Flow looks like this, and in the Concat I've tried setting a value to:

concat('"',item()?['Amount'],'"')

csvtable.JPG

This results in """1""","""Name""" rather than "1","Name"

 

I found this thread looking for an answer to the same problem. The idea above to use brackets instead gave me an idea:

 

Use a compose to define a string that you know will never appear in your data. It can be as long or short as you like, but it must be completely unique. The longer you make it the less likely it'll appear in your data. In my example I chose ^&

 

WillPage_0-1603405493767.png

I tried both wrapping the value in a concat() function and also just by clicking the dynamic content in the Value box.

Next, replace the string with a quote throughout the whole CSV output:

WillPage_1-1603405602591.png

 

In a test run, it seems to work for both methods of doing concat() and also just clicking the dynamic content:

WillPage_2-1603405642507.png

 

Paulie78
Super User
Super User

This is how Excel behaves when importing CSV files, but there is an easy solution. I posted the exact solution on this thread a few days ago:

 

https://powerusers.microsoft.com/t5/Building-Flows/Convert-CSV-Column-data-type-to-string/m-p/723680...

 

Basically you insert a tab character before the value and then Excel will treat it as a text column. No need to insert quotes etc.

 

Just create a variable called charTab and copy and paste a tab into it. Then put that before your Zip code column and when you do the Excel import it will not be interpreted as a number.

jerryveldhuis
Regular Visitor

While looking for a solution to this same problem I found this post.

Here is the solution I found:

 

The solution was found from this helpful solution:

    https://superuser.com/questions/318420/formatting-a-comma-delimited-csv-to-force-excel-to-interpret-...

 

concat('="', item()['code'],'"')
 
This creates a csv value like this:
      code
      "=""010.3000"""
 

This caused excel (and apparently google spreadsheet) to force the value in the cell to literally:

     ="010.3000"

 

GLMSDev
Helper II
Helper II

not sure if this helps. but replace function helped.  

 

replace(...,'"""','"')

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

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

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (2,946)