Leading zeros in csv exports removed

efreimuth
efreimuth Member
in Charting

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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • efreimuth
    efreimuth Member

    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 Coach

    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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • efreimuth
    efreimuth Member

    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 Coach

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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • efreimuth
    efreimuth Member

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

    Capture.PNG

    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 Coach

    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')

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • efreimuth
    efreimuth Member

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

Categories