Getting quotes around my fields when exporting to CSV

Welcome to Apple Discussions

Are you asking about the Apple spreadsheet program Numbers or Excel? When I export a Numbers spreadsheet to .csv & then open it in TextEdit I do get the quotes. If you're asking about Excel, a better place to ask would be the Microsoft Office forums.

I have a feeling the problem lies in the structure of the Excel files, especially since they are .xlsx files. After opening them in Numbers, try copying & pasting (using Paste & Match Style might be best) the data into a new Numbers spreadsheet & exporting the new file. That may get rid of the embedded Excel structure.

Hi Peggy

Are you really getting quotes around values like 1 or 2 or 3 ?

I never got them.

Here I get the double quotes around values embedding a comma.

,1,2,"2,5","la, pluie",lapin,55,"5,6",87,,

To get the double quote enclosing every value, I use this script:

--

--[SCRIPTclipboard2CSVfile]
(*
Enregistrer le script en tant que Script, Application ou Progiciel :clipboard2CSVfile.xxx
déplacer le fichier créé dans le dossier
<VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:
Copiez la table à exporter dans le Presse-papiers.
menu Scripts > clipboard2CSVfile
Le script créera un fichier CSV dans lequel toutes les valeurs sont encadrées par des guillemets.
--=====
L'aide du Finder explique:
L'Utilitaire AppleScript permet d'activer le Menu des scripts :
Ouvrez l'Utilitaire AppleScript situé dans le dossier Applications/AppleScript.
Cochez la case "Afficher le menu des scripts dans la barre de menus".
+++++++
Save the script as a Script, an Application or an Application Bundle:clipboard2CSVfile.xxx
Move the newly created file into the folder:
<startup Volume>:Users:<yourAccount>:Library:Scripts:
Coopy the table to export into the clipboard
menu Scripts > clipboard2CSVfile
The script will create a CSV file in which every cell value is enclosed between double quotes.
--=====
The Finder's Help explains:
To make the Script menu appear:
Open the AppleScript utility located in Applications/AppleScript.
Select the "Show Script Menu in menu bar" checkbox.
+++++++
Yvan KOENIG (Vallauris FRANCE)
7 juillet 2009
*)
on run
 set fName to (do shell script "date " & quote & "+_%Y%m%d-%H%M%S" & quote) & "." & "csv"
 
 set p2d to path to desktop
 
 tell application "System Events" to make new file at end of p2d with properties {name:fName}
 set enTexte to quote & my remplace(the clipboard as text, tab, quote & "," & quote) & quote
 write enTexte to file ((p2d as text) & fName)
end run
--=====
(*
replaces every occurences of d1 by d2 in the text t
*)
on remplace(t, d1, d2)
 local l
 set AppleScript's text item delimiters to d1
 set l to text items of t
 set AppleScript's text item delimiters to d2
 set t to l as text
 set AppleScript's text item delimiters to ""
 return t
end remplace
--=====
--[/SCRIPT]

Yvan KOENIG (from FRANCE mardi 7 juillet 2009 19:19:50)

My guess is that you didn't copy a table but a sheet.

Here is an enhanced version taking care of this odd case which I forgot to treat 😟

--

--[SCRIPTclipboard2CSVfile]
(*
Enregistrer le script en tant que Script, Application ou Progiciel :clipboard2CSVfile.xxx
déplacer le fichier créé dans le dossier
<VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:
Copiez la table à exporter dans le Presse-papiers.
menu Scripts > clipboard2CSVfile
Le script créera un fichier CSV dans lequel toutes les valeurs sont encadrées par des guillemets.
--=====
L'aide du Finder explique:
L'Utilitaire AppleScript permet d'activer le Menu des scripts :
Ouvrez l'Utilitaire AppleScript situé dans le dossier Applications/AppleScript.
Cochez la case "Afficher le menu des scripts dans la barre de menus".
+++++++
Save the script as a Script, an Application or an Application Bundle:clipboard2CSVfile.xxx
Move the newly created file into the folder:
<startup Volume>:Users:<yourAccount>:Library:Scripts:
Coopy the table to export into the clipboard
menu Scripts > clipboard2CSVfile
The script will create a CSV file in which every cell value is enclosed between double quotes.
--=====
The Finder's Help explains:
To make the Script menu appear:
Open the AppleScript utility located in Applications/AppleScript.
Select the "Show Script Menu in menu bar" checkbox.
+++++++
Yvan KOENIG (Vallauris FRANCE)
7 juillet 2009
*)
on run
 try
 set enTexte to the clipboard as text
 set fName to (do shell script "date " & quote & "+_%Y%m%d-%H%M%S" & quote) & "." & "csv"
 
 set p2d to path to desktop
 
 tell application "System Events" to make new file at end of p2d with properties {name:fName}
 
 set enTexte to quote & my remplace(enTexte, tab, quote & "," & quote) & quote
 write enTexte to file ((p2d as text) & fName)
 on error
 if my parleAnglais() then
 error "The clipboard doesn‘t contain text data. Maybe you selected a Numbers sheet !"
 else
 error "Le presse-papiers ne contient pas de données texte. Vous avez peut-être copié une feuille de Numbers !"
 end if
 end try
end run
--=====
(*
replaces every occurences of d1 by d2 in the text t
*)
on remplace(t, d1, d2)
 local l
 set AppleScript's text item delimiters to d1
 set l to text items of t
 set AppleScript's text item delimiters to d2
 set t to l as text
 set AppleScript's text item delimiters to ""
 return t
end remplace
--=====
on parleAnglais()
 local z
 try
 tell application "Numbers" to set z to localized string "Cancel"
 on error
 set z to "Cancel"
 end try
 return (z is not "Annuler")
end parleAnglais
--=====
--[/SCRIPT]

Yvan KOENIG (from FRANCE mardi 7 juillet 2009 20:45:10)

You didn't copy a table but a sheet !

With Numbers '08, copying a sheet returns an error which is filtered by the new script.
With Numbers '09, copying a sheet doesn't generate an error so several tables are gathered in a single CSV file. This is not a correct result.
I will try to find a workaround.

Yvan KOENIG (from FRANCE mardi 7 juillet 2009 21:26:33)

I am selecting the table from my sheet. Not the sheet itself. When I run the script with the table selected in Numbers in the clipboard it returns a blank .csv file.

It seems that we aren't running the same application 😉

When I run the same script with a table selected from Excel it returns a file that is over 4 times the size of the original ( this is probably due to the nature of the .csv format )

The fact that the file is larger is normal.
When the standard export to CSV stores a single comma, the script stores three characters: a comma and two double quotes.

but the last field doesn't have a closing quote and the first field doesn't have an opening quote.

It really failed to replace returns by quote +return + quote.

It's now corrected.

--

--[SCRIPTclipboard2CSVfile]
(*
Enregistrer le script en tant que Script, Application ou Progiciel :clipboard2CSVfile.xxx
déplacer le fichier créé dans le dossier
<VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:
Copiez la table à exporter dans le Presse-papiers.
menu Scripts > clipboard2CSVfile
Le script créera un fichier CSV dans lequel toutes les valeurs sont encadrées par des guillemets.
--=====
L'aide du Finder explique:
L'Utilitaire AppleScript permet d'activer le Menu des scripts :
Ouvrez l'Utilitaire AppleScript situé dans le dossier Applications/AppleScript.
Cochez la case "Afficher le menu des scripts dans la barre de menus".
+++++++
Save the script as a Script, an Application or an Application Bundle:clipboard2CSVfile.xxx
Move the newly created file into the folder:
<startup Volume>:Users:<yourAccount>:Library:Scripts:
Coopy the table to export into the clipboard
menu Scripts > clipboard2CSVfile
The script will create a CSV file in which every cell value is enclosed between double quotes.
--=====
The Finder's Help explains:
To make the Script menu appear:
Open the AppleScript utility located in Applications/AppleScript.
Select the "Show Script Menu in menu bar" checkbox.
+++++++
Yvan KOENIG (Vallauris FRANCE)
7 juillet 2009
*)
on run
 try
 set enTexte to the clipboard as text
 set fName to (do shell script "date " & quote & "+_%Y%m%d-%H%M%S" & quote) & "." & "csv"
 
 set p2d to path to desktop
 
 tell application "System Events" to make new file at end of p2d with properties {name:fName}
 set enTexte to my remplace(enTexte, return, quote & return & quote)
 set enTexte to my remplace(enTexte, tab, quote & "," & quote)
 if enTexte does not start with quote then set enTexte to quote & enTexte
 if enTexte does not end with quote then set enTexte to enTexte & quote
 write enTexte to file ((p2d as text) & fName)
 on error
 if my parleAnglais() then
 error "The clipboard doesn’t contain text data. Maybe you selected a Numbers sheet !"
 else
 error "Le presse-papiers ne contient pas de données texte. Vous avez peut-être copié une feuille de Numbers !"
 end if
 end try
end run
--=====
(*
replaces every occurences of d1 by d2 in the text t
*)
on remplace(t, d1, d2)
 local l
 set AppleScript's text item delimiters to d1
 set l to text items of t
 set AppleScript's text item delimiters to d2
 set t to l as text
 set AppleScript's text item delimiters to ""
 return t
end remplace
--=====
on parleAnglais()
 local z
 try
 tell application "Numbers" to set z to localized string "Cancel"
 on error
 set z to "Cancel"
 end try
 return (z is not "Annuler")
end parleAnglais
--=====
--[/SCRIPT]

You may click my blue name to get my mail address
attach the script which you are using to a mail
send them to my mailbox.
Tomorrow I will look at it trying to understand what is failing.

Here I get:

"A1","B1","","
2","22","32","D2
3","23","33","D3
4","24","34","D4
5","25","35","D5
6","26","36","D6
7","27","37","D7
8","28","38","D8
9","29","39","D9
10","30","40","D10
11","31","41","D11
12","32","42","D12
13","33","43","D13"

Yvan KOENIG (from FRANCE mardi 7 juillet 2009 22:21:54)