Issue in writing sql data into csv for decimal value to scientific notation

[mg24]

Hi Team,

I am trying to write sql table data into csv.
but facing one issue.

if column has below decimal values, I am not getting same value into csv.

-----input-------
MarketPrice
0.000000000000

output I am getting in csv

-----'OE-12'--------

import csv
rows = cursor.fetchmany(100)

with open("test1.csv","w",newline='') as outfile:
	writer = csv.writer(outfile,delimiter="|",quoting="csv.quote_NONE)
	for row in rows:
		print(row)	
		print(type(row)
	      writer.writerow(row)


Print statement printing below values
print(row)---------> (Decimal('OE-12'),)
print(type(row)--->  <[b]class 'pyodbc.Row'[/b]>

thanks
mg

[Larz60+]

you are missing a close parenthesis on line 8.

format your floats as strings, you can use f-string.
Or better yet, use pandas which makes it all simple,
for an example, see: https://pandas.pydata.org/pandas-docs/ve...l#querying

[mg24]

Hi Larz60,

Thanks for your help,

below code is working for first column.

no = float(row[0])
if no == 0.0:
    writer.writerow(0.0)

how to dynamically check it for all columns
float(row[0]) ------------row[0] is hard coded value here


Thanks
mg

Larz60+ write Nov-28-2022, 07:32 PM:
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.
Fixed for you this time. Please use BBCode tags on future posts.

[deanhystad]

I have no idea what you are trying to accomplish with this:

no = float(row[0])
if no == 0.0:
    writer.writerow(0.0)

Larz60+ already told you how to solve the problem. Either format your own numeric strings or use something like pandas where you can specify how numbers are formatted.

I made some data that contains Decimal values.

from decimal import Decimal
import csv

rows = [[Decimal(f'0E-{x}'),  Decimal(f'1.2E-{x+1}')] for x in range(2, 14, 2)]

with open("test.csv", "w", newline="") as file:
    writer = csv.writer(file, quoting=csv.QUOTE_NONE, delimiter="|")
    writer.writerows(rows)

The test.csv file looks like this:

Output:
0.00|0.0012
0.0000|0.000012
0.000000|1.2E-7
0E-8|1.2E-9
0E-10|1.2E-11
0E-12|1.2E-13

I don't want scientific notation, so I convert the Decimals to strings myself.

from decimal import Decimal
import csv

rows = [[Decimal(f'0E-{x}'),  Decimal(f'1.2E-{x+1}')] for x in range(2, 14, 2)]

with open("test.csv", "w", newline="") as file:
    writer = csv.writer(file, quoting=csv.QUOTE_NONE, delimiter="|")
    for row in rows:
        writer.writerow(map(lambda x: f'{x:f}', row))

The csv file looks like this:

Output:
0.00|0.0012
0.0000|0.000012
0.000000|0.00000012
0.00000000|0.0000000012
0.0000000000|0.000000000012
0.000000000000|0.00000000000012

I don't like all those trailing zeros, so I normalize the Decimal values.

from decimal import Decimal
import csv

rows = [[Decimal(f'0E-{x}'),  Decimal(f'1.2E-{x+1}')] for x in range(2, 14, 2)]

with open("test.csv", "w", newline="") as file:
    writer = csv.writer(file, quoting=csv.QUOTE_NONE, delimiter="|")
    for row in rows:
        writer.writerow(map(lambda x: f'{x.normalize():f}', row))

Output:
0|0.0012
0|0.000012
0|0.00000012
0|0.0000000012
0|0.000000000012
0|0.00000000000012

You can do the same sort of thing with Pandas.

from decimal import Decimal
import pandas as pd

rows = pd.DataFrame([[Decimal(f'0E-{x}'),  Decimal(f'1.2E-{x+1}')] for x in range(2, 14, 2)])
rows.to_csv("test.csv", header=False, index=None, sep="|")

The csv file:

Output:
0.00|0.0012
0.0000|0.000012
0.000000|1.2E-7
0E-8|1.2E-9
0E-10|1.2E-11
0E-12|1.2E-13

To get rid of the scientific notation I specify a float format.

from decimal import Decimal
import pandas as pd

rows = pd.DataFrame([[Decimal(f'0E-{x}'),  Decimal(f'1.2E-{x+1}')] for x in range(2, 14, 2)])
rows.to_csv("test.csv", header=False, index=None, sep="|", float_format="%f")

The csv file:

Output:
0.00|0.0012
0.0000|0.000012
0.000000|1.2E-7
0E-8|1.2E-9
0E-10|1.2E-11
0E-12|1.2E-13

Oops! That didn't work. The reason it didn't work is that all the values are Decimal, not float. I need to convert the values to floats

from decimal import Decimal
import pandas as pd

rows = pd.DataFrame([[Decimal(f'0E-{x}'),  Decimal(f'1.2E-{x+1}')] for x in range(2, 14, 2)]).astype("float")
rows.to_csv("test.csv", header=False, index=None, sep="|", float_format="%f")

csv file:

Output:
0.000000|0.001200
0.000000|0.000012
0.000000|0.000000
0.000000|0.000000
0.000000|0.000000
0.000000|0.000000

Guess I need more precision.

from decimal import Decimal
import pandas as pd

rows = pd.DataFrame([[Decimal(f'0E-{x}'),  Decimal(f'1.2E-{x+1}')] for x in range(2, 14, 2)]).astype("float")
rows.to_csv("test.csv", header=False, index=None, sep="|", float_format="%.14f")

Output:
0.00000000000000|0.00120000000000
0.00000000000000|0.00001200000000
0.00000000000000|0.00000012000000
0.00000000000000|0.00000000120000
0.00000000000000|0.00000000001200
0.00000000000000|0.00000000000012

[mg24]

Hi Deanstyed,

great solution ,

but still unable to utilise correctly in my situation.

I dont want this ------>range(2, 14, 2)
I am ok for 0.0 value.


input data there are 300 columns to check , where is zero value
xyz 29-11-2022 0.0000000000 1.00002
abc 29-11-2022 0.000000000 200

expected output now
xyz | 29-11-2022| 0.0| 1.00002
abc | 29-11-2022| 0.0| 200



rows = [[Decimal(f'0E-{x}'), Decimal(f'1.2E-{x+1}')] for x in range(2, 14, 2)]

with open("test.csv", "w", newline="") as file:
writer = csv.writer(file, quoting=csv.QUOTE_NONE, delimiter="|")
for row in rows:
writer.writerow(map(lambda x: f'{x.normalize():f}', row))

#-----My Code----------

while True:
    rows = cursor.fetchmany(10000)
    if len(rows) == 0:
        print("no records found")
        break
    else:
        for row in rows:
            #writer.writerows(row)  instead of this line , # just to replace single line , I am writing more line
            for row in rows:
                    for x in row:
                           no = float(x)
                           if no ==0:
                               writer.writerows(row)

[deanhystad]

Why is it a problem if the csv file contains 0E-12?

I used pandas to read this csv file that I made for my previous post. This is the csv file I read.

Output:
0.00|0.0012
0.0000|0.000012
0.000000|1.2E-7
0E-8|1.2E-9
0E-10|1.2E-11
0E-12|1.2E-13

This is the program.

import pandas as pd
df = pd.read_csv("test.csv", sep="|", header=None)
print(df)

And this is the data frame it produced.

Output:
     0             1
0  0.0  1.200000e-03
1  0.0  1.200000e-05
2  0.0  1.200000e-07
3  0.0  1.200000e-09
4  0.0  1.200000e-11
5  0.0  1.200000e-13

Do you know that values like 0E-12 are going to be a problem, or is it an unfounded assumption?

[mg24]

Hi Deanhystad,

my python script is working fine for writing sql table data to into csv.
But when tried to import CSV File into database, we are getting error. while validating

Error when importing to sql table … cross checking
bulk load data conversion error (Type mismatch or invalid charecter for the specified codepage)
for row 2. column 18 (Market Price).


---------SQL Table Constraints------------
Market Price(Decimal 16,12) Null

[deanhystad]

What does row 2 look like? Can you post that?

How are you importing the csv file into the database? Can you post that code?

'0E-12' is a perfectly fine string representation of a Decimal value. I don't think it is the source of your error.

[mg24]

hi deanhystad


in my database.

if I run sql query ,

select distinct[Market Price] from table1, I get below data.
I want to write below extracted SQL data into csv files. with delimiter '|"


--------------SQL Table data----------------
Market Price Initial Price
.000000000000 .000000000000
NULL NULL


-----------if I Read CSV Files after extraction -------------
df pd.read_csv(filename,sep='|',usecols=['Market Price','Initialprice])
print(df['Market Price'].unique())

this is creating problem while importing csv data back into sql table.
output [nan,0.]

SQL Tables market Price Columns constraint -----------
market Price (decimal(16,12),null)

conversation error happending.