Double quoting everything

[rctneil]

Hello,

I have an excel spreadsheet which i am converting to csv, I want everything in each cell to be double quoted. Is there a quick and easy way to double quote the contents of each cell whilst in excel so when i save as a csv it's all pre double quoted for me?

 

[Sin_Chase]

Assuming your data contains no commas, why not just do a find and replace on , and replace with "," ?

You will need to chuck additional quotation marks on the start and end of each line but that would be easy to automate.

 

[rctneil]

hmm, yeh, I would first like to see if there is anyway I could insert a comma at the start and end of every cell in excel before exporting as a csv

 

[Sin_Chase]

hmm, yeh, I would first like to see if there is anyway I could insert a comma at the start and end of every cell in excel before exporting as a csv

Export a blank column from either side of the data set. Or create the same columns but with a unique identifier in each, lets say xyz123 for start and 123xyz for the end

Then do a find and replace on:
, > ","
xyz123, > "
,123xyz > "

 

[rctneil]

?, I'm confused

 

[Sin_Chase]

Well if you had the following spreadsheet

 --A--   --B--   --C--  
1  1       1       1     
2  1       1       1   
3  1       1       1  
4  1       1       1

It would export to

1,1,1
1,1,1
1,1,1


You would turn it into

 --A--   --B--   --C--  --D--   --E--
1  x       1       1      1       y   
2  x       1       1      1       y   
3  x       1       1      1       y   
4  x       1       1      1       y

Where x and y are unique identifiers.

it would export to

x,1,1,1,y
x,1,1,1,y
x,1,1,1,y
x,1,1,1,y

You then find and replace , into ","
x, into "
and ,y into " and are left with:

"1","1","1"
"1","1","1"
"1","1","1"

 

[SiriusB]

OK a new and even better method than the macro:

Open a text editor and paste in this script. And save it as a .ps1 file.

enquote-csv.ps1

param(
[Parameter(Mandatory = $true, Position = 0)]
[string]$path,
[Parameter(Mandatory = $false, Position = 1)]
[string]$output
)

if($path -AND (Test-Path $path))
{

    if(!$output)
    {
        $output = $path
    }
    
    $csv = Get-Content $path
    #column headers shouldn't be quoted so written as-is to output file
    Set-Content -Path $output -Value $csv[0]
    
    #skip first line of csv file
    for($i = 1; $i -lt $csv.length; $i++)
    {
        $fields = $csv[$i].Split(',')
        for($j = 0; $j -lt $fields.length; $j++)
        {
            $fields[$j] = '"' + $fields[$j] + '"'
        }
        
        Add-Content -Path $output -Value ([string]::Join(',',$fields))
    }
}

Save your Excel document as a .csv.

Open PowerShell and run the above script as follows:

This line of code will replace the file.csv with enquoted fields

C:\scripts\enquote-csv.ps1 'C:\path to\file.csv'

This line of code will enquote the fields and save the output to the path specified

C:\scripts\enquote-csv.ps1 'C:\path to\file.csv' 'C:\path to\new file.csv'

Voila!


Macro Method
Just in case you would rather do it the slightly harder way!

Create a new macro in excel and paste in the following [remove any existing code in the editing window]:

Sub AddQuote()
Dim myCell As Range
    For Each myCell In Selection
        If myCell.Value <> "" Then
            myCell.Value = Chr(34) & myCell.Value & Chr(34)
        End If
    Next myCell
End Sub

Save your workbook as .xslm

Select all the cells you wish to be quoted then run your macro.

Save the document as a .csv. Open the CSV in a regular text editor [notepad, notepad++, et al.]

Do a search and replace. Search for """ and replace with "

Save the changes.

Excel ignores quotes around a text string, so when saving as CSV it uses """ to display the quote. Excel normally
gets rid of the surrounding quotes, but other applications may be confused by them all.

This method gets rid of the need for creating two extra columns and doing a search and replace on multiple strings.

EDIT:

Rather than using a text editor for the string replacement, you could also use PowerShell to do it for you:

(get-content 'C:\path to\file.csv') | % { $_ -replace '"""', '"' } | set-content 'C:\path to\file.csv'

You can save the above into a script if you don't want to type it each time! Also, if you wish to keep the original CSV, change the path after set-content to something else.