Getting quotes around my fields when exporting to CSV
apple Verified ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Hello all,
New to the forums.
I've done some digging but no luck.
Can anyone PLEASE help out with this. I'm exporting a .xlsx file to .csv but none of my fields are being wrapped in quotes. I've got commas in the content and when I'm doing a database import it's throwing everything off.
For instance.
I'm getting this - 1,2,3,4,5
I need this - "1","2","3","4","5"
Any help would be greatly appreciated.
12 replies
Sign in or join Beachball Help to reply.
- 0
Peggy ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
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.
- 0
mhiggins ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Thanks for the reply.
I am, in fact, asking about numbers. In the past I have done an export to .csv with no problems. I'm running into this issue on 3 .xlsx files that I am opening in numbers.
For whatever reason, I'm not getting the quotes. Is there a way to explicitly define that I want quotes on all fields?
Note : Some of the fields contain single digits, no data at all, a string of text or multiple lines of HTML.
Thanks again for the help. If anyone has any feedback on this I would really appreciate it.
- 0
Peggy ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
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.

- 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
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)
- 0
mhiggins ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Thanks for the reply guys,
I tried your suggestion on copying and pasting with styles into a new document. No luck there.
I also tried running your script, KOENIG Yvan. It does create the document but it is blank.
I am copying the whole table directly out of Numbers. It's saved in the clipboard as a "Number Clipboard Object". Am I doing something wrong? This would be a killer fix if I could get it running.Thanks again for the help! I look forward to hearing from you both.
- 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
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)
- 0
mhiggins ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
I'm not sure what's going on but the new code doesn't produce a .csv file for me.
- 0
mhiggins ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
When I copy the table in Excel it works using the old script you provided.
Thanks for all the help!
- 0
mhiggins ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
OK, turns out it didn't.
It doesn't put a closing quote at the end of each line or the beginning of every line after the first.
If it makes a difference my file is 5,000 lines long. - 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
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)
- 0
mhiggins ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
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.
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 ) but the last field doesn't have a closing quote and the first field doesn't have an opening quote.
That's what has happened for me so far.
- 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
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)