Unwanted Data conversion in Excel

[goodmanjl531]

I have a complex excel sheet that has various data   including taxes with trailing zeros(need 8 significant digits past decimal) i need to keep and date/time in a specific format (YYYY-MM-DD HH:MM:SS.000).  I am using _Excel_RAngerRead to put all data in an array then pull data out to write to a text file for other uses, but it drops my trailing  zeros and converts my dates to decimal anty ideas how to preserve format from excel to the text file?

 

my spreadsheet will show below  

 

but when i run the script io will get 

 

INSERT|TAX_RATE_RULE|NA-US|00601|1|1||||0.08375|FULL|20200201000000|21991231000000||||*|*
INSERT|TAX_RATE_RULE|NA-US|00602|1|1||||0.0675|FULL|20200201000000|20210731000000||||*|*
INSERT|TAX_RATE_RULE|NA-US|00602|1|2||||0.0725|FULL|20210801000000|21991231000000||||*|*

 

IT should look like below with proper date and significate digits on end

INSERT|TAX_RATE_RULE|NA-US|00601|1|1||||0.083750|FULL|2020-02-01 00:00:00.000|2199-12-31 00:00:00.000||||*|*
INSERT|TAX_RATE_RULE|NA-US|00602|1|1||||0.067500|FULL|2020-02-01 00:00:00.000|2021-07-31 00:00:00.000||||*|*
INSERT|TAX_RATE_RULE|NA-US|00602|1|2||||0.072500|FULL|2021-08-01 00:00:00.000|2199-12-31 00:00:00.000||||*|*

  below is my code for this section  (note some extra code removed for visibility  

Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
$sWorkbook1 = "C:\TS\tax\Tables.xlsx"             ;******WORKBOOK to pull details **********
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook1)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $sWorkbook1 & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)



$storedata_5 = _Excel_RangeRead($oWorkbook, "TAX_RATE_RULE", "GROUP5")


For $Line5 = 1 To UBound($storedata_5) - 1

    $e1 = $storedata_5[$Line5][0] & "|"
    $e2 = $storedata_5[$Line5][1] & "|"
    $e3 = $storedata_5[$Line5][2] & "|"
    $e4 = $storedata_5[$Line5][3] & "|"
    $e5 = $storedata_5[$Line5][4] & "|"
    $e6 = $storedata_5[$Line5][5] & "|"
    $e7 = $storedata_5[$Line5][6] & "|"
    $e8 = $storedata_5[$Line5][7] & "|"
    $e9 = $storedata_5[$Line5][8] & "|"
    $e10 = $storedata_5[$Line5][9] & "|"
    $e11 = $storedata_5[$Line5][10] & "|"
    $e12 = $storedata_5[$Line5][11] & "|"
    $e13 = $storedata_5[$Line5][12] & "|"
    $e14 = $storedata_5[$Line5][13] & "|"
    $e15 = $storedata_5[$Line5][14] & "|"
    $e16 = $storedata_5[$Line5][15] & "|"
    $e17 = $storedata_5[$Line5][16] & "|"
    $e18 = $storedata_5[$Line5][17]

    $CPF5 = FileOpen($file5, 1)
    FileWriteLine($CPF5, $e1 & $e2 & $e3 & $e4 & $e5 & $e6 & $e7 & $e8 & $e9 & $e10 & $e11 & $e12 & $e13 & $e14 & $e15 & $e16 & $e17 & $e18)
Next
FileClose($CPF5)

Thanks in advance for help/ideas on this.

 

[Jos]

What happens when you change this line to read the formatted/displayed text?:

_Excel_RangeRead($oWorkbook, "TAX_RATE_RULE", "GROUP5")

Quote

Parameters

$oWorkbook	Excel workbook object
$vWorksheet	[optional] Name, index or worksheet object to be read. If set to keyword Default the active sheet will be used (default = keyword Default)
$vRange	[optional] Either a range object or an A1 range. If set to Default all used cells will be processed (default = keyword Default)
$iReturn	[optional] What to return from the specified cell:     1 - Value (default)     2 - Formula     3 - The displayed text     4 - Value2. The only difference between Value and Value2 is that the Value2 property doesn’t use the Currency and Date data types
$bForceFunc	[optional] True forces to use the _ArrayTranspose function instead of the Excel transpose method (default = False).     See the Remarks section for details.

 

[goodmanjl531]

I switched 

$storedata_5 = _Excel_RangeRead($oWorkbook, "TAX_RATE_RULE", "GROUP5",3)

and now i get no data at all 

[Jos]

3 minutes ago, goodmanjl531 said:

and now i get no data at all 

So, try some debugging instead of these "doesn't work posts"!
What data is stored in the array? Use an _arraydisplay() to check that in your script for debugging and check for errors returned by the udf.

Edited June 20, 2024 by Jos

[Jos]

There is a small mistake in the script where you are opening the $file5 with each loop of the range. Here is another way of doing that:

#include <Array.au3>
#include <Excel.au3>

$file5 = "\xyz.csv"

Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
$sWorkbook1 = "C:\TS\tax\Tables.xlsx"             ;******WORKBOOK to pull details **********
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook1)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $sWorkbook1 & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

$storedata_5 = _Excel_RangeRead($oWorkbook, "TAX_RATE_RULE", "GROUP5")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead", "Error reading range. @error = " & @error & ", @extended = " & @extended)

; show retrieved data for debugging
_ArrayDisplay($storedata_5)

$CPF5 = FileOpen($file5, 1)
For $Line5 = 1 To UBound($storedata_5) - 1
    $oline = ''
    For $col = 0 To 17
        $oline &= $storedata_5[$Line5][0]
        If $col < 17 Then $oline &= "|"
    Next
    FileWriteLine($CPF5, $oline)
Next
FileClose($CPF5)

 

Edited June 20, 2024 by Jos

[goodmanjl531]

i get no display when i use 

$storedata_5 = _Excel_RangeRead($oWorkbook, "TAX_RATE_RULE", "GROUP5",3)
_ArrayDisplay($storedata_5,"TAX_RATE_RULE")

getting error code of 8  

8 - $iReturn = 3 can only return a single cell. $vRange specified > 1 cell.

is there anyway around the 1 cell limit on $iReturn = 3 ?

[Jos]

Ah ...  that is true ....   then I guess you need to retrieve those cells one at a time in case you need the displayed value

[Jos]

In case you only have this data in the sheet you could also do a "SaveAs", but that will save the whole sheet into a csv file.

_Excel_BookSaveAs($oWorkbook, $file5, $xlTextWindows, True)

 

Edited June 20, 2024 by Jos

[goodmanjl531]

I was able to convert my dates to a standard date format and just use sting manipulation to create date in format i need.

 

$predate = $storedata_5[$Line5][11]
    $e12 = StringLeft($predate, 4) & "-" & StringMid($predate, 5, 2) & "-" & StringMid($predate, 8, 2) & " 00:00:00.000|"
        $predate = $storedata_5[$Line5][12]
    $e13 = StringLeft($predate, 4) & "-" & StringMid($predate, 5, 2) & "-" & StringMid($predate, 8, 2) & " 00:00:00.000|"

 

 

Edited June 20, 2024 by goodmanjl531