Forum Discussion

JamieBrown's avatar
JamieBrown
Qrew Trainee
6 years ago

"save as spreadsheet" erasing my leading zeros

I have a Text field that contains numeric account numbers that generally start with Zero  (ie:  04201345).  They show fine in Quickbase, but when i "Save as Spreadsheet", the account number field always erases the leading 0, (IE: 4201345)

Is there a way to make sure those Zero's stay all the time?

------------------------------
Jamie Brown
------------------------------
Data import and export
  • BabiPanjikar's avatar
    BabiPanjikar
    Qrew Assistant Captain
    6 years ago
    Hi Jamie,

    This is a excel behaviour and not a quickbase download issue. Do not double click the csv file directly to open in excel. This will trim the trailing zero and treat it as number instead of text. 
    Try this one. close the excel and open again. Open the file using menu-file-open file, locate your csv and the open.

    ------------------------------
    Babi Panjikar
    ------------------------------
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    6 years ago
    Jamie,

    This is very frustrating and is a Microsoft problem not Quick Base.   You need to import the .csv into a workbook.

    Then you will need to transform the data column to Text to get the prevailing zeros.

    It is a real pain.

    Don


    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------
  • EverettPatterso's avatar
    EverettPatterso
    Qrew Captain
    6 years ago
    Make a formula text field and use that on the report you want to Save as Spreadsheet

    formula text - combine single quote with original account number
    "'" & [Textfield]​


    If you add a single quote to beginning of the text string that contains a number, Excel will show the leading zeros




    ------------------------------
    Everett Patterson
    ------------------------------
  • EmberKrumwied's avatar
    EmberKrumwied
    Qrew Captain
    2 years ago

    I know this is an older post, but thought I would provide my solution for those still looking.  I have this issue as well and this is how I have solved the problem.  Download your data and then change the file format from .csv to .txt.  Open excel, then open the .txt file.  The program will take you through a wizard where you can separate the data out into columns and then set the column format (for your leading zero data) to text.  Then finish the wizard.  All your data will be in columns and you will now see the leading zeros.



    ------------------------------
    Ember
    ------------------------------