skip to Main Content

Excel CSV triple quotes when saving file

You filled an Excel file and saved it to a CSV file. The Excel saved CSV file is showing triple double quotes. After saving the file, you double-check it by opening the CSV file with a text editor. What do you see? Instead of a double quote, it’s showing the double quotes three times.

Open the CSV file with a text editor

Download the CSV file TripleQuotes.csv and place it in C:\Temp\ folder. Do you have the text editor Notepad++ installed on the system? That’s great. If you don’t, download the application and install it.

Open the CSV file with the text editor. You can also right-click the TripleQuotes.csv file, and the context menu will show up. Click Edit with Notepad++.

Edit CSV file with Notepad++

How it looks before editing

Let’s have a look at the CSV file. It’s showing “””OU=IT,OU=Users,OU=Company,DC=exoip,DC=local“””.

Excel CSV triple quotes when saving file before

Import the CSV in PowerShell with the Import-CSV cmdlet. We can see the double quotes. That’s not good as it should not be showing in the output.

PS C:\> Import-Csv C:\Temp\TripleQuotes.csv -Delimiter ";" | Format-Table

FirstName Lastname City   Company OU
--------- -------- ----   ------- --
Max       Fraser   London EXOIP   "OU=IT,OU=Users,OU=Company,DC=exoip,DC=local"
Piers     Bower    London EXOIP   "OU=IT,OU=Users,OU=Company,DC=exoip,DC=local"
Kylie     Davidson London EXOIP   "OU=IT,OU=Users,OU=Company,DC=exoip,DC=local"
Richard   Grant    London EXOIP   "OU=IT,OU=Users,OU=Company,DC=exoip,DC=local"
Boris     Campbell London EXOIP   "OU=IT,OU=Users,OU=Company,DC=exoip,DC=local"
Nicholas  Murray   London EXOIP   "OU=IT,OU=Users,OU=Company,DC=exoip,DC=local"
Leonard   Clark    London EXOIP   "OU=IT,OU=Users,OU=Company,DC=exoip,DC=local"
Ruth      Dickens  London EXOIP   "OU=IT,OU=Users,OU=Company,DC=exoip,DC=local"
Jonathan  Fisher   London EXOIP   "OU=IT,OU=Users,OU=Company,DC=exoip,DC=local"
Grace     Rees     London EXOIP   "OU=IT,OU=Users,OU=Company,DC=exoip,DC=local"

In the next step, we will change it to OU=IT,OU=Users,OU=Company,DC=exoip,DC=local.

Edit CSV triple quotes with a text editor

You can use the Replace feature with Notepad++. Click Search in the menu bar and click Replace… If you want, you can press Ctrl+H,  which will do the same.

Excel CSV triple quotes when saving file replace

Find  “””  and replace with  ” . Click Replace All.

Excel CSV triple quotes when saving file replace all

How it looks after editing

The Replace All feature worked great. The quotes are showing as  ” .

Excel CSV triple quotes when saving file after

Import the CSV in PowerShell. No more double quotes are showing.

PS C:\> Import-Csv C:\Temp\TripleQuotes.csv -Delimiter ";" | Format-Table

FirstName Lastname City   Company OU
--------- -------- ----   ------- --
Max       Fraser   London EXOIP   OU=IT,OU=Users,OU=Company,DC=exoip,DC=local
Piers     Bower    London EXOIP   OU=IT,OU=Users,OU=Company,DC=exoip,DC=local
Kylie     Davidson London EXOIP   OU=IT,OU=Users,OU=Company,DC=exoip,DC=local
Richard   Grant    London EXOIP   OU=IT,OU=Users,OU=Company,DC=exoip,DC=local
Boris     Campbell London EXOIP   OU=IT,OU=Users,OU=Company,DC=exoip,DC=local
Nicholas  Murray   London EXOIP   OU=IT,OU=Users,OU=Company,DC=exoip,DC=local
Leonard   Clark    London EXOIP   OU=IT,OU=Users,OU=Company,DC=exoip,DC=local
Ruth      Dickens  London EXOIP   OU=IT,OU=Users,OU=Company,DC=exoip,DC=local
Jonathan  Fisher   London EXOIP   OU=IT,OU=Users,OU=Company,DC=exoip,DC=local
Grace     Rees     London EXOIP   OU=IT,OU=Users,OU=Company,DC=exoip,DC=local

Read more: Import CSV delimiter PowerShell »

Edit CSV triple quotes with PowerShell

You can replace the triple double quotes to double quotes with PowerShell. Run PowerShell as administrator and run the following cmdlet.

PS C:\> (Get-Content C:\Temp\TripleQuotes.csv) | ForEach-Object {$_ -replace '"""','"'} | Out-File C:\Temp\TripleQuotes.csv -Encoding UTF8

Great, the triple double quotes are replaced with double quotes. Did this help you to remove the triple quotes?

Keep reading: Add email address to list of names in Excel »

Conclusion

In this article, you learned about Excel is saving the CSV file with triple double quotes. Use a text editor or PowerShell to replace the triple double quotes to double quotes. As of last, verify the CSV file import in PowerShell with the Import-Csv cmdlet.

Did you enjoy this article? If so, you may like the article Import CSV delimiter PowerShell. Don’t forget to follow us and share this article.

ALI TAJRAN

ALI TAJRAN

ALI TAJRAN is a passionate IT Architect, IT Consultant, and Microsoft Certified Trainer. He started Information Technology at a very young age, and his goal is to teach and inspire others. Read more »

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top