We need to export a mailbox to PST file. The problem is that we are…
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