Leading zeros in csv exports removed

efreimuth
efreimuth βšͺ️

I have several scheduled reports where the leading zeros in certain fields are removed. The field itself is formatted as text, and it shows up properly in the cards, however once it is emailed in a .csv file those zeros are gone. Is there a way, maybe even in the ETL or the card, that I can prevent this from happening?

Tagged:

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon πŸ”΅
    Accepted Answer

    If you open up the file in a text editor such as notepad are the leading zeros missing? I know Excel will often remove leading zeros if you just try and view it in there.

Answers

  • efreimuth
    efreimuth βšͺ️

    They are in Notepad - but is there a way to prevent the extra step of having to go from Notepad to Excel?

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟀

    Domo will frequently take it's "best guess" on the data type as you import it. unless you're importing it with Workbench I don't believe you can explicitly set the data type.


    what you can do in MySQL or Magic 2.0 is use an LPAD() function to Left Pad the data.

    https://www.w3schools.com/mysql/func_mysql_lpad.asp

  • efreimuth
    efreimuth βšͺ️

    Thanks @jaeW_at_Onyx, my issues are more related to the exporting of the card to csv. I still tried the LPAD() function in Magic 2.0 but ended up with the same results - no leading zeros. I appreciate the help anyways, I still learned a new function that I'm sure will help in other areas.

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟀

    put a space in front of the lpad or a '.'

  • efreimuth
    efreimuth βšͺ️

    Where should it go exactly?

    Example string: 100.804.6015.3601, LE.BA.CC.LC

    Some LE's are 054, 056 etc. While some LC's are 0001, or 0039.

    Attached screenshot is how I'm splitting them using LPAD(), previously I was just using the "Split Columns".

    Unfortunately the preview showed a row that didn't have the data to split, but when it does it will show the proper split strings, and the format is text all the way through the card. And like @MarkSnodgrass mentioned, opening the .csv file in Notepad shows the leading zeros. They just go away when the .csv is opened in Excel.

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon πŸ”΅

    When entering 0046 directly into Excel, for example, in order to not have Excel change it 46, you have to preface it with an apostrophe. You could try adding an apostrophe in your formula tile, but I am concerned how that will render on any cards you have built. If you don't care about the cards and just worry about the CSV file, I would add a CONCAT function to go with your LPAD function to add the leading apostrophe. It would look like this, for example:

    CONCAT(''',LPAD('LE',3,'LE')

  • efreimuth
    efreimuth βšͺ️

    I like the thought - however it's giving me an error. I'm starting to think this conversation needs to be had oustide the Domo community because you were right the first time that it's doing the right thing and spitting out the leading zeros, Excel just doesn't like it. And of course my end users can't just use Domo, we have to use Excel for some facets of our work. I really appreciate the help from both of you!!

Sign In or Register to comment.