Benches Uncategorized

12

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.

  1. 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.

    User uploaded file

  2. 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.

  3. 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.

    User uploaded file

  4. 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)

  5. 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.

  6. 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)

  7. 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.

  8. 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!

  9. 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.

  10. 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)

  11. 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.

  12. 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)