Appending sales data to a MS SQL-Server table as a VARCHAR with only two decimal places via python

by Tom Crews   Last Updated October 09, 2019 21:26 PM

This is a specific question for a python ETL process involving pandas and MS SQL-Server, with a sqlalchemy connection.

If sales data is received as a number with more than two decimal places (ex: $1,245.456), how can it be appended to a sales table that is a VARCHAR data type. The sales column is a VARCHAR and cannot be altered to a different datatype.

my_sales_file.xlsx looks like this:

Product | Sales
--------|--------
Apple   | 1235.456
Banana  | 567.54
Pear    | 43.435
Peach   | 432.32

Example code:

import pandas as pd
import sqlalchemy
from sqlalchemy.types import String

con_string = 'dummy_con_string'
engine = sqlalchemy.create_engine(con_string)

# read the file, force string datatype
df = pd.read_excel('my_sales_files.xlsx', dtype=str)

# round sales to a decimal value
df['Sales'] = df['Sales'].apply(lambda x: str(round(float(x), 2)))

# append to a ms sql table
df.to_sql(name='my_table',
          con=engine,
          if_exists='append',
          dtype={'Sales': String})
SELECT SUM(CAST(Sales as float)
FROM my_table;

results display as a decimal with trailing decimal values.

Additional validation:

select * from my_table
where LEN(Sales)-CHARINDEX('.',sales) > 2 and CHARINDEX('.'sales) <> 0;

I am stuck with a table that takes in sales data as a VARCHAR and need a solution to for rounding and appending messy sales with python such that all rounding is preserved.



Answers 1


The following will format and round the number to two decimals.

Example

Declare @YourTable Table ([Product] varchar(50),[Sales] varchar(50))  Insert Into @YourTable Values 
 ('Apple','1235.456')
,('Banana','567.54')
,('Pear','43.435')
,('Peach','432.32')
,('Fig','32')

Select *
      ,AsString = format(try_convert(money,Sales),'0.00') 
 From @YourTable

Returns

Product Sales       AsString
Apple   1235.456    1235.46  << Notice Rounded
Banana  567.54      567.54
Pear    43.435      43.44
Peach   432.32      432.32
Fig     32          32.00     << Notice two decimals
John Cappelletti
John Cappelletti
October 09, 2019 21:25 PM

Related Questions



import and mapping csv to sqlalchemy dynamically

Updated January 27, 2019 11:26 AM

Filling pandas dataframe with foreign key

Updated May 22, 2019 15:26 PM