Help Needed! % data point is being converted to scientific notation (E)

[AltAddict26]

Hello Alteryx Community! This is a tricky one - we are reading in data from an excel survey that can have a wide range of responses from whole numbers, to percentages, to a text comment. I have included an example excel with the data we are pulling as well as a screenshot of the issue.

We want to post the raw data to a SQL server database as VarChar Max for all rows (V_String in Alteryx). Unfortunately, when reading the data, it is converting all the % values with more than one basis point to scientific notation. for example - in the excel it says .45% and below you can see it is being converted to 4.4999999999999997E-3. Please send help! haha We simply want that value to read as .45% or .0045.

 

[BS_THE_ANALYST]

@AltAddict26 open excel, save the file as a CSV. CSV files are read in as string. Excel files bring additional formatting (which seems to be causing the issue).Read the file in as a CSV instead:

[AltAddict26]

We have a directory of files we are reading from, each with multiple tabs. If this is the only way to do this, is there a macro to convert a directory of excel files to CSV's?

[BS_THE_ANALYST]

@AltAddict26 ha, yes. Well I see the issue here. If we read the file in as a .xlsx, we can't then convert it to a .csv as we already caused the issue by importing it as .xlsx .. lol.

I'm currently working on something in Python that will convert XLSX (with multiple tabs) to CSVs. For example, ExcelTest.xlsx has 3 tabs. We will get ExcelTestSheet1.csv, ExcelTestSheet2.csv, ExcelTestSheet3.csv. 

I'll send the solution along shortly.

[Pilsner]

I've managed to get this which works for the sample data you provided. Does this work for your actual data set? 

Let me know how you get on

[BS_THE_ANALYST]

@AltAddict26 I love @Pilsner 's method of converting the data whilst in Alteryx to reconfigure it. It really may be as simple as that. 

If you are spotting strange things happening, I've created a Python Script which takes all XLSX files in a folder and converts them to CSVs. For xlsx files with multiple tabs, they will get File1-Tab1.csv .. File1-Tav2.csv .. etc.

To demonstrate this: 
I've got a folder that contains two excel files, one has two tabs on:

After running the workflow:


Note, 3 extra files in the folder which is awesome. Also, the Python tool outputs the directory. You can then process all the files as you normally would via batch macro etc to bring all the sheets in. 

Only thing you need to do it change the directory tool to point to your folder.

This will not delete the original files.