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++.

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“””.

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.

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

How it looks after editing

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

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.