Expand all | Collapse all

CSV Export problem

  • 1.  CSV Export problem

    Posted 05-08-2020 08:08
    I have several subscriptions and notifications sent out on a regular basis.  Some of the users are requesting the data sent in a CSV file rather than being embedded in the email.  The problem I am encountering is when the csv file is created, it is truncating some of the numbers into scientific notation even though they are in a text field in Quick Base.  Is there any way to prevent the csv file from changing the number format?

    Paul Peterson

  • 2.  RE: CSV Export problem

    Posted 05-08-2020 17:50
    Do not open the CSV file.  Use Get Data  or From Text/CSV in a new excel sheet and select the csv file. It will load csv file data correctly without scientific notation.

    Ravi Bala

  • 3.  RE: CSV Export problem

    Posted 05-08-2020 17:53
    Thank you

    Paul Peterson

  • 4.  RE: CSV Export problem

    Posted 05-09-2020 08:46
    I tried that approach and am still getting the numbers in scientific notation

    Paul Peterson

  • 5.  RE: CSV Export problem

    Posted 05-11-2020 17:12
    Edited by Austin K 05-11-2020 17:31
    **Read my second reply before trying this**

    So no matter what you open the file in it is in scientific notation? Or are you only trying Excel? Because this is an issue with Excel usually. Depending how long the numbers are Excel really does not like playing with things over 15 digits. In my testing this happens with all numbers that have 12 or more digits.

    I use the web version of Excel, this may be slightly different with the actual program. I made sure this was a good test because this data is pulled from Quick Base where I had numbers stored that Excel turned into scientific notation when loaded in. So it should work for you just the same.

    What you can do to convert your numbers back to the full number, and this needs to be done on each and every column with a number, is use the Number Format option. Simply right click the column and select "Number Format..." then select the options as my screenshot shows. This will convert the numbers for you. It fixed numbers for me that Excel had changed to scientific format as well as adding decimals that didn't belong.

    What the other person mentioned should also work. There is a way to import the file and choose what columns will become text, I don't see how with the web Excel though.

    So this is something that the user will need to do on their own each time they load one of these files with the extra long numbers. It is kind of unfortunate but now that you know the issue maybe you can find an automated solution or some way to make sure their Excel is set up properly for this.

  • 6.  RE: CSV Export problem

    Posted 05-11-2020 17:31
    Edited by Austin K 05-11-2020 17:33
    Actually, test this one yourself... It has some issues.

    I noticed that one single number out of like 50 got converted WRONG. The last 4 digits of the number should have been "5352" and this converted them to be "5350". This is the only number to be changed, it is not even the longest one. So I do not know what is going on here.

    If I were you I would search for a way to import columns as text rather than going for a straight conversion. That might be the only way, I'm not sure otherwise. Sorry.

  • 7.  RE: CSV Export problem

    Posted 05-15-2020 12:20
    I should have been more specific, the export data is coming from an Oracle application and the only numbers that are being converted are a 13 digit order number.

    Paul Peterson

  • 8.  RE: CSV Export problem

    Posted 05-19-2020 10:13
    Edited by Austin K 05-19-2020 10:13
    I bet if you were to open the CSV file in Notepad or similar the numbers would not be in scientific notation. The issue is with Excel. Excel is causing any number over 12 digits to be changed into scientific and their own function for changing it back is not accurate either. The inaccuracy is what worries me here... I only had the one number that was not correct but one number is too many in that situation.

    Every solution I see posted says to just convert the numbers in the way I mentioned above or to use a formula in a secondary column to convert it into text, "=CONCATENATE(A1)" in column B. But again the accuracy worries me. After seeing one number be wrong that really shakes my confidence in using Excel at all for converting things. I did see a suggestion that said to format the cells BEFORE you paste the data in and that may work better. 

    That fix might work if you do it on your computer and then mail the file out, I assume the secondary column would work this way. However some of the fixes may heavily depend on the users Excel settings that opens the file. So it might be impossible to fix without having the users make the change themselves but you will have to test that to be sure.