How to save sent items in shared mailbox sent items folder in Office 365 (Exchange…
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.
Table of contents
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.
This Post Has 0 Comments