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
