search

hankinsoft / SQLPro

SQLPro bug & features tracking.
100 stars 26 forks source link

Export CSV missing float precision #954

Closed cwolcott closed 6 months ago

cwolcott commented 6 months ago

Describe the bug When I export a MS SQL Server table using Export --> CSV ... the export data is only showing 6 digits of the float.

To Reproduce Steps to reproduce the behavior:

  1. Define a table with a column of float(8)
  2. Insert data for the column "0.063417250592446794"
  3. Export as CSV
  4. Export as JSON
  5. Compare exports

Expected behavior I would have hoped the export using CSV would export the full precision.

Screenshots Column A01 in CSV is exported as 0.063417 Column A01 in JSON is exported as 0.063417250592446794

CSV Data cLSF004MUT,2,0.063417,1.842364,1.933057,1.670451,1.629125,1.666147,1.85854,1.683202,0.189895,0.196116,0.191245,0.187234,0.190054,0.185247,0.185758,0.196368,0.064313,1.875138,1.529287,1.504085,1.452995,1.213925,1.323564,1.304689,0.198561,0.185602,0.183898,0.182892,0.183143,0.181577,0.185228,0.191216,1.606184,0.188377,0.22825,0.005217,NULL,NULL,0.505997,2005-02-16 00:00:00.000,cLSF 2-11-05.xls,Mutant with SCF-EXTRACT,0,NULL

JSON Data

{
    "L02" : 0.18289170258345899,
    "E01" : 1.62912530895189,
    "Negative_Aver" : 0.18837710236359323,
    "Negative_STDEV" : 0.0052168591632252362,
    "J01" : 0.196116105258135,
    "O01" : 0.185758110158664,
    "line" : null,
    "E02" : 1.45299548878791,
    "Positive_CV" : null,
    "J02" : 0.18560184002826699,
    "C01" : 1.93305718953989,
    "Comment" : "Mutant with SCF-EXTRACT",
    "O02" : 0.18522806169013201,
    "H01" : 1.68320155388367,
    "M01" : 0.190053518368479,
    "C02" : 1.52928670549565,
    "H02" : 1.30468881245719,
    "A01" : 0.063417250592446794,
    "M02" : 0.18314331294282399,
    "F01" : 1.6661467969672401,
    "Positive_STDEV" : 0.22825010342430266,
    "K01" : 0.191245354776237,
    "P01" : 0.196368023028443,
    "A02" : 0.064312846244013802,
    "Assay_Plate_ID" : "cLSF004MUT",
    "F02" : 1.21392509288198,
    "Assay_ID" : 2,
    "K02" : 0.18389798229809501,
    "D01" : 1.67045147833913,
    "Assay_Date" : "2005-02-16 00:00:00.000",
    "P02" : 0.19121580913935901,
    "I01" : 0.18989464329353001,
    "Negative_CV" : null,
    "N01" : 0.185246795006871,
    "wavelength" : 0,
    "D02" : 1.5040853567358301,
    "I02" : 0.198561388210966,
    "B01" : 1.84236405717907,
    "XLS_Name" : "cLSF 2-11-05.xls",
    "N02" : 0.18157700446392799,
    "G01" : 1.85854005092235,
    "L01" : 0.18723398657010201,
    "B02" : 1.8751380387007499,
    "Z_Factor" : 0.50599682509228772,
    "G02" : 1.3235638038900801,
    "Control_Aver_Value" : 1.6061835524809023
  },

Environment details (please complete the following information):

Additional context The MS SQL Server table defines column A01 as float(8) null

I would use the JSON export to gain the precision, but my target database is Oracle and the last version you were able to release via the AppStore was SQLPro for Oracle Version 2023.50 (Build 575.5) and thus does not have Import --> JSON.

Is there any alternate way to access the latest build for Oracle other than the AppStore?

hankinsoft commented 6 months ago

Could you try exporting from SQL Server using this build: https://sqlprostudio.s3.us-east-1.amazonaws.com/studio/SQLProStudio.2023.81.app.zip

cwolcott commented 6 months ago

Well the export does match what is showing in the UI, but different than what the JSON export dumps.

SQLPro Studio Version 2023.73 (Build 111073.5) value of JSON export was 0.063417250592446794 SQLPro Studio Version 2023.73 (Build 111073.5) value of CSV export was 0.063417

SQLPro Studio Version 2023.81 (Build 111081.1) value of JSON export was 0.063417250592446794 SQLPro Studio Version 2023.81 (Build 111081.1) value of CSV export was 0.0634172505924468

image

I will have someone view the data in SQL Server Management Studio (SSMS) in the morning to see what the precision is.

cwolcott commented 6 months ago

Interesting the select from SQL Server Management Studio also returns 0.0634172505924468. So I guess that means I like the new CSV export, but curious why JSON is exporting that value differently.

hankinsoft commented 6 months ago

When you did the insert did you run an insert statement or insert via the UI? If you inline insert, sometimes the app will show the value you entered rather than the 'updated value that the serve stores'. Most of the time these values are the same, however for high precision values the server does not always store what you have entered - if you were to do the export while the placeholder value is still visible (without querying from the server to the the actual value), I could see this happening. I can't get a json export to show 0.063417250592446794 without doing the above state. That's my guess, but with the csv and the grid both matching SQL server I'm going to consider this closed.

cwolcott commented 5 months ago

The initial insert of the data into MS SQL Server was done by a web application many years ago.

I am now migrating the data to Oracle and have released a new Website for our organization (Molecular Targets Program (MTP) for the National Cancer Institute (NCI)).

This was done on the real dataset and not my made up testcase. So in SQLPro Studio connected to MS SQL Server I ran Export To CSV and noticed the original truncation between the exported data and the data in the database. While preparing the ticket I noticed there was an Export to JSON and that is where I saw an even longer data value.

So for now I am OK with closing #954 based on the fix you made to Export to CSV.