Algeberic/Scientific formatting issue into SQl server from Alteryx

Question

Hello,

Wondering if anyone could help me? I've been challenged to create a new workflow from scratch and my skills in alteryx aren't the best, as I am fairly new to Alteryx Designer, yet I have had a play around and I have made the workflow.However, the problem I am having is that once the data is joined by SKU column and inputted into a SQL server, the SKU column and the EAN (barcode) column seem to be coming out as Scientific/Algebra figures eg. '1.8e+007’- as seen below. I thought this wouldn't be the end of the world as I could convert it in excel to a number afterwards, but it seems to be spitting out the complete wrong SKU replacing the last algeberic numbers with 0's and not the specific 11 digit number I am expecting. I checked the numbers in the original excel files the data comes from and they don't appear to be in this format within excel, so unsure why would appear like this when inputted into SQL from alteryx.
I have tried multiple methods to get around this like getting alteryx to cleanse the data, making sure these particular columns are not string fields but double fields, using formula tools to convert the probelmatic columns to number before it goes into the tables (as seen below). However, nothing I've tried so far appears to be making any difference, I wondered if anyone knew where I might be going wrong. I do run two workflows before this to get the data into the input tables for the final output workflow. So I'm not sure how far the issue goes back, but I thought I would be able to convert into a number regardless at any point in a workflow. Because of how large the data set is, I cant rely on making any changes in Excel afterwards as it is too big too download, therefore it is essential it goes into SQL as the correct number from alteryx.  I seem to have got the data I need, but can't do anything with it, due to what I think is such a small issue with formatting. Thanks,Will

danilang · Answer

hi @Smith401

When I use your file the values come in as expected in version 2021.1

When you have numbers that are meant to be treated as strings, i.e. SKUs, EANs, employee ids, etc. it's always a good idea to use a Select tool immediately after the Input tools to force the columns to strings.  That way when you write the the data to SQL, creating a new table in the process, the resulting columns will be varchars in the tables as well.  When you read them back out of the tables, they won't show the scientific notation issue.

Dan

Smith401 · Answer

Hi Dan,

I've attached an example file containing a few lines of what would be inputted from excel into SQl. There are 3 data sets I use, which go into 3 separate SQL tables. I then extract the data from these 3 SQL tables in the master workflow, which is this one, where the 3 SQL inputs can be seen to start with. The plan is to join them all up through the SKU column. The join seems to work successfully, but the formatting seems to be lost, which is where I am having these scientific/algerba format issues in the final SQL output.

Thanks,

Will

SKU example.xlsx

danilang · Answer

Hi @Smith401

how are you getting the SKU's into Alteryx.  If they're input through Excel, can you post a sample input file that displays this issue?  Just a few lines would be enough.  If you read them from a database, try using a Cast statement in the input SQL to force the numbers to be read in as text.

Dan