Web AppBuilder (Arcgis online) : Exporting to cvs file changes account number to scientific format. How to fix it?

[LeLuong]

All,

I'm using Web AppBuilder  on Arcgis online.  Tool to export attribute to cvs file changes account number to scientific format. How to fix it?  Any work around solution is appreciated.  Thanks for your help.

[jcarlson]

That's an Excel setting, not a WAB export setting. We see that a lot with Parcel Numbers when we export things, too. Try these:

1. Make the column wider.
   
2. Set the column format explicitly to "Number"
   

You'll notice that the cells are filled with '#' characters, which just means the column needs to be widened to display the contents. You'll probably need to tell it not to display decimal points, too.

[LeLuong]

I don't see any option for you to choose your file you want to export to beforehand.  The tool automatically creates one and saves it to your download folder.  If I open the  cvs file and change the column format to number I'll loose all the leading 0 in my account number.  Thanks.

[jcarlson]

Oh, dealing with a leading zero. That's, again, an Excel thing. It's trying to be helpful when it sees what it thinks is a numeric column. Opening the same CSV in a text editor shows the actual values.

In order to tell Excel what the column is "on the way in", you need to use the Text Import Wizard. By itself, it defaults to "General", which handles your numbers in the way you're seeing. Since it drops the leading zero, even setting it to "Text" after the fact won't bring it back.

Open an empty spreadsheet, then use Data → Get External Data → From Text and select your CSV. In the import wizard, select the column, then set the data format to "Text", and it should retain the data.

[LeLuong]

I'm creating a web appbuilder app for my users with the goal to help them get what they need faster and easier.  So honestly all the steps above is not exactly we all have in mind.  I'm hoping there're other less complicated ways than that because this is something my users use daily.  They do query and export the result to CVS file multiple times a day, not just 1 time thing.  Thank you very much for your help.  It's much appreciated. 

[jcarlson]

I guess the point, though, is that this "issue" is entirely an Excel thing in how it opens text-based data files. There's nothing you can do in your web app that will change this, as the data is being exported by WAB fully intact.

If your users happen to have Excel, they're going to run into this every time, no matter where they get the data from. If they use different programs, such as LibreOffice as @Brian_Wilson mentioned, they may never see the "problem" at all.

As Brian also mentioned, you could insert a non-numeric character into the field, but you shouldn't have to alter your data just to accommodate a design choice made by Microsoft.

[Brian_Wilson]

Perhaps the CSV file is not the problem and you are reading a long number (15 digits or more) into a program like Excel which just automatically forces it into a double?

You have to tell the importer that the column has text in it. The ugly way is to change every row in the CSV so that number has a nonnumeric character in it. The better way is to tell the import step the column is text.

In LibreOffice the default is to let you set each column's data type. In Excel you can do a command "From Text/CSV" and tell it "do not detect data types" which means you have to then set each column manually.

In either cases once the account number is converted to a double, it's cooked and you cannot change it back. You have to get the import step right.

[LeLuong]

I got what you mean.  My issue is how " to tell the importer".  I'm using the built-in export to CVS file from query result (AGOL).  Just wonder if there's any other tool(s) out there that I can use to export to CVS file and let me choose my own file to export to.  In that case I can manually set up a blank template file with text format for the columns I want.

[Brian_Wilson]

Which tool do you currently use to generate the CSV file? Is it from the Attribute table widget?

I thought there was another widget that could export to XLS files but I am not seeing it right now.

[Brian_Wilson]

Darn -- it's in OpenLayers. I'm learning how to write Widgets right now, that's why I read your posting.