Home » DbVisualizer Forums » DbVisualizer » DbVisualizer - Support

Thread: decimal number format export problem with scientific notation.

This question is answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 6 - Pages: 1 - Last Post: Jan 31, 2012 5:36 PM Last Post By: Tim Dudek
Tim Dudek

Posts: 32
Registered: 10/06/09
decimal number format export problem with scientific notation.
Posted: Jan 31, 2012 3:41 PM
 
   thread.click_reply Reply
I am exporting decimal data to a csv, and when I run the command as @export I get scientific notation in the output instead of decimal. If I use the export command from the 'export grid' console, the previewed output seems correct. Why doesn't the @export command work as expected?

@export on;

@export set filename="c:\dbvis_results\tmi_gen_list_data_corrected.csv" format="csv" TimeStampFormat="yyyy-MM-dd-HH:mm:ss.SSSSSS" CsvColumnDelimiter= "," CsvIncludeColumnHeader = "false" DecimalNumberFormat = '####.####################' NumberFormat='####';

SELECT
g.SLS_MDL_NO ,
g.SLS_MDL_CBSTN ,
g.GEN_HZ ,
g.GEN_RATG_TYP ,
g.GEN_KW_EXT ,
g.GEN_VOLT_RATG ,
g.GEN_EFCY_PWR_FCTR ,
g.MFR_GEN_NO ,
g.EXCTN_TYP ,
g.CNECT_TYP ,
g.APPL_VAL ,
l.ITM_PROD_TYP ,
l.SEQ_NO ,
l.PARM_DTA_1 ,
l.LAST_UPDT_TS
FROM
n4gk001$.TMI_GEN_DTA_LST l
right JOIN
n4gk001$.TMI_GEN_DTA g
ON
l.SLS_MDL_NO = g.SLS_MDL_NO
AND l.SLS_MDL_CBSTN = g.SLS_MDL_CBSTN
AND l.GEN_HZ = g.GEN_HZ
AND l.GEN_RATG_TYP = g.GEN_RATG_TYP
AND l.GEN_KW_EXT = g.GEN_KW_EXT
AND l.GEN_VOLT_RATG = g.GEN_VOLT_RATG
AND l.GEN_EFCY_PWR_FCTR = g.GEN_EFCY_PWR_FCTR
AND l.MFR_GEN_NO = g.MFR_GEN_NO
AND l.EXCTN_TYP = g.EXCTN_TYP
AND l.CNECT_TYP = g.CNECT_TYP;
--fetch first 11 rows only; --testing only
@export off;

the output in the file Im seeing is where 0E-9 is incorrect.
...
C-175 ,DI,50,SB,2480.00,690,0.80,3425192 ,PM,STAR ,EPG,b,1,0E-9,2010-07-14-08:30:38.768404
...

If I use the export grid option I see :
...
C-175 ,DI,50,SB,2480,690,0.8,3425192 ,PM,STAR ,EPG,b,1,0,2010-07-14 08:30:38
...

where 0 is correct.

I am exporting 13.3 million rows, so I cannot export to the gui, and then choose the export grid, that's why I need the @export command.

What am I doing wrong?
attached is a picture of the settings in the export grid to show you the configuration is the same.

Thanks


UPDATE

As a note, I've been testing with an export settings xml, which also does not work. (copy of mine below)
I've tried reordering the attributes to alphabetical, thinking that for some reason they have to be in order, and I get this behavior:

COMMAND [changed]
@export set CsvColumnDelimiter= "," CsvIncludeColumnHeader = "false" DecimalNumberFormat = '####.####################' filename="c:\dbvis_results\tmi_gen_list_data_corrected.csv" format="csv" NumberFormat='####' TimeStampFormat="yyyy-MM-dd-HH:mm:ss.SSSSSS";

RESULTS
09:15:35 [@EXPORT - 0 row(s), 0.000 secs] Command processed
09:15:35 [@EXPORT - 0 row(s), 0.016 secs] Command processed
09:15:35 [SELECT - 0 row(s), 0.000 secs] The Filename is not set for the @export command
09:15:35 [@EXPORT - 0 row(s), 0.000 secs] Command processed
... 4 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.016/0.000 sec [3 successful, 0 warnings, 1 errors]

when I remove the DecimalNumberFormat attribute, it works, but again, without the correct format.

I double checked the settings in the export grid, and what comes from there is :

<?xml version="1.0" encoding="UTF-8"?>
<DbVisualizer>
<ExportSettings>
<BinaryFileDir>C:\dbVis_results</BinaryFileDir>
<BinaryFormat>File</BinaryFormat>
<BooleanFalseFormat>false</BooleanFalseFormat>
<BooleanTrueFormat>true</BooleanTrueFormat>
<CLOBFileDir>C:\dbVis_results</CLOBFileDir>
<CLOBFormat>File</CLOBFormat>
<CsvColumnDelimiter>,</CsvColumnDelimiter>
<CsvIncludeColumnHeader>false</CsvIncludeColumnHeader>
<CsvIncludeSQLCommand>Don't Include</CsvIncludeSQLCommand>
<CsvRemoveNewlines>false</CsvRemoveNewlines>
<CsvRowCommentIdentifier></CsvRowCommentIdentifier>
<CsvRowDelimiter>\r\n</CsvRowDelimiter>
<DateFormat>yyyy-MM-dd</DateFormat>
<DecimalNumberFormat>####.####################</DecimalNumberFormat>
<Destination>Clipboard</Destination>
<Encoding>Cp1252</Encoding>
<ExcelFileFormat>Binary Excel (xls)</ExcelFileFormat>
<ExcelIncludeColumnHeader>true</ExcelIncludeColumnHeader>
<ExcelIncludeSQLCommand>false</ExcelIncludeSQLCommand>
<ExcelIntroText></ExcelIntroText>
<ExcelTextOnly>false</ExcelTextOnly>
<ExcelTitle>DbVisualizer export output</ExcelTitle>
<Filename>c:\dbvis_results\tmi_gen_list_data_corrected.csv</Filename>
<Format>CSV</Format>
<HtmlIncludeSQLCommand>false</HtmlIncludeSQLCommand>
<HtmlIntroText></HtmlIntroText>
<HtmlTitle>DbVisualizer export output</HtmlTitle>
<NumberFormat>####</NumberFormat>
<QuoteDuplicateEmbedded>true</QuoteDuplicateEmbedded>
<QuoteTextData>None</QuoteTextData>
<SchemaExportDropObjects>true</SchemaExportDropObjects>
<SchemaExportInclTableData>false</SchemaExportInclTableData>
<SchemaExportInclTableIndex>false</SchemaExportInclTableIndex>
<ShowNullAs></ShowNullAs>
<SqlBlockBeginDelim>--/</SqlBlockBeginDelim>
<SqlBlockEndDelim>/</SqlBlockEndDelim>
<SqlIncludeCreateDDL>false</SqlIncludeCreateDDL>
<SqlIncludeDDLCommand></SqlIncludeDDLCommand>
<SqlIncludeDropStmt></SqlIncludeDropStmt>
<SqlIncludeSQLCommand>Don't Include</SqlIncludeSQLCommand>
<SqlIncludeTableData></SqlIncludeTableData>
<SqlIncludeTableIndex></SqlIncludeTableIndex>
<SqlRowCommentIdentifier>--</SqlRowCommentIdentifier>
<SqlSeparator>;</SqlSeparator>
<TimeFormat>HH:mm:ss</TimeFormat>
<TimeStampFormat>yyyy-MM-dd-HH:mm:ss.SSSSSS</TimeStampFormat>
<TxtIncludeColumnHeader>true</TxtIncludeColumnHeader>
<TxtIncludeSQLCommand>Don't Include</TxtIncludeSQLCommand>
<TxtRemoveNewLines>false</TxtRemoveNewLines>
<TxtRowDelimiter>\n</TxtRowDelimiter>
<TxtSpacesBetweenColumns>1</TxtSpacesBetweenColumns>
<XmlIncludeCreateDDL></XmlIncludeCreateDDL>
<XmlIncludeSQLCommand>false</XmlIncludeSQLCommand>
<XmlIncludeTableData></XmlIncludeTableData>
<XmlIncludeTableIndex></XmlIncludeTableIndex>
<XmlIntroText></XmlIntroText>
<XmlStyle>DbVisualizer</XmlStyle>
</ExportSettings>
</DbVisualizer>

Edited by: Tim Dudek on Jan 31, 2012 4:12 PM

Edited by: Tim Dudek on Jan 31, 2012 4:16 PM
Roger Bjarevall


Posts: 5,051
Registered: 12/17/04
Re: decimal number format export problem with scientific notation.
Posted: Jan 31, 2012 4:29 PM    global.in_response_to.tooltip in response to: Tim Dudek
 
   thread.click_reply Reply
Tim,

What DbVisualizer version are you using? And what is your locale (Help->About and the System Properties tab, locate user.country and user.language)?

Regards

Roger
Tim Dudek

Posts: 32
Registered: 10/06/09
Re: decimal number format export problem with scientific notation.
Posted: Jan 31, 2012 4:36 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
 
   thread.click_reply Reply
user.country US
user.language en

Product: DbVisualizer Personal 8.0.2
Build: #1700 (2011/08/08 00:35)
Java VM: Java HotSpot(TM) Client VM
Java Version: 1.6.0_27
Java Vendor: Sun Microsystems Inc.
OS Name: Windows XP
OS Arch: x86
OS Version: 5.1

Roger Bjarevall


Posts: 5,051
Registered: 12/17/04
Re: decimal number format export problem with scientific notation.
Posted: Jan 31, 2012 4:50 PM    global.in_response_to.tooltip in response to: Tim Dudek
 
   thread.click_reply Reply
Tim,

Problem found. Simply make sure all parameters are enclosed with double quotes rather than single quotes:

@export set CsvColumnDelimiter= ","
CsvIncludeColumnHeader="false"
DecimalNumberFormat="####.####################"
Filename="c:\dbvis_results\tmi_gen_list_data_corrected.csv"
Format="csv"
NumberFormat="####"
TimeStampFormat="yyyy-MM-dd-HH:mm:ss.SSSSSS";

This should work better.

Regards

Roger
Roger Bjarevall


Posts: 5,051
Registered: 12/17/04
Re: decimal number format export problem with scientific notation.
Posted: Jan 31, 2012 4:51 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
 
   thread.click_reply Reply
Tim,

On a related note I suggest you upgrade to the latest 8.0.7 version as it include many fixes since 8.0.2. Upgrading Java to 1.6.0_30 may also be a good idea.

Regards

Roger
Tim Dudek

Posts: 32
Registered: 10/06/09
Re: decimal number format export problem with scientific notation.
Posted: Jan 31, 2012 5:35 PM    global.in_response_to.tooltip in response to: Roger Bjarevall
 
   thread.click_reply Reply
Thanks! Sorry about the user error.

I upgraded as well, thanks.

Tim.
Tim Dudek

Posts: 32
Registered: 10/06/09
Re: decimal number format export problem with scientific notation.
Posted: Jan 31, 2012 5:36 PM    global.in_response_to.tooltip in response to: Tim Dudek
 
   thread.click_reply Reply
User Error. Thanks Roger for your help.

Point your RSS reader here for a feed of the latest messages in all forums