_Excel_RangeWrite Not Working With Large Dataset and Force Transpose = True

[Zurch1818]

I'm trying to write a large dataset to a range but am struggling to see if anyone can come up with a suggestion on how to fix this.  Overall, this data is completely public and from the FCC (Data is HERE...the RA.dat, CO.dat, and EN.dat files in the zip) and I'm just trying to get it into an app to make the data more accessible and automated.  I have trimmed up the trouble code and posted below (it does work with small datasets).  I currently have code that can download the zip, unzip it, convert the file to an array, and then I just want to write it to excel so I can import it into our internal app.  The code is below:

Func WriteDataToWorkbook($sSheetName, $aInput)

    Local $iMatch

    Switch $oWorkbook.Sheets(1).Name
        Case "Sheet1"
            ;There is only one sheet...rename the existing sheet
            $oWorkbook.Sheets(1).Name = $sSheetName
        Case Else
            ;The first sheet has already been renamed...add a new sheet to the workbook (if it doesn't already exist).
            Local $aSheets = _Excel_SheetList($oWorkbook)
            ;_ArrayDisplay($aSheets)

            $iMatch = _ArraySearch($aSheets,$sSheetName,0,0,0,0,1,0)

            Switch $iMatch
                Case -1
                    ;The sheet doesn't exist...add it.
                    _Excel_SheetAdd($oWorkbook, -1, False, 1, $sSheetName)
                Case Else
                    ;The sheet already exists...overwrite it's contents
            EndSwitch
    EndSwitch

    ;Write data to the range.
    _Excel_RangeWrite($oWorkbook, $sSheetName, $aInput, Default, True)
EndFunc   ;==>WriteDataToWorkbook

The code will paste data...but it eventually has the columns not matching up and then the bottom of the dataset returns #N/A's.  I have confirmed that the array that gets generated is correct.

As a workaround, I'm trying to write the data to a temp csv and then moving that data to my Excel workbook, but that seems to be quite slow.  Overall, I think I probably could get by without getting this working as there is daily data that comes out (which is much smaller).  I just need to get the full dataset processed manually.  However, there is anther large dataset that does work, but I worry at what point this behavior will occur.

Thanks for your help!

[argumentum]

23 minutes ago, Zurch1818 said:

(Data is HERE...the RA.dat, CO.dat, and EN.dat files in the zip)

they are pipe delimited:

HS|REG|A0076610|1065953|1096978|07/09/1999|Application Granted
HS|REG|A0029394|1024646|125847|07/13/1999|Duplicate Registration Request Received
HS|REG|A0006880|1005846|111122|07/14/1999|Registration Terminated
HS|REG|A0006880|1005846|111122|07/14/1999|Supercede - Internal Correction Applied
HS|REG|A0075991|1065334|1096997|07/14/1999|Application Granted

So, what is your need/problem/wish ?

[water]

You could give function _Excel_BookOpenText a try to directly import the pipe delimited file.

[Zurch1818]

Correct...they are pipe delimited.  I'm doing a little data manipulation with an AutoIt array (trimming out some fat that isn't relevant to us)...and now I'm trying to get the data into our Caspio app (which requires Excel formatted values, so I'm trying to convert my array to that format.

Here's my current workaround (I'm not sure if this will exactly work for you as I may be missing some data) that now works decently:

expandcollapse popup

Func WriteDataToWorkbook($sSheetName, $aInput, $sFormat = "Excel")

    Local $iMatch

    Switch $oWorkbook.Sheets(1).Name
        Case "Sheet1"
            ;There is only one sheet...rename the existing sheet
            $oWorkbook.Sheets(1).Name = $sSheetName
        Case Else
            ;The first sheet has already been renamed...add a new sheet to the workbook (if it doesn't already exist).
            Local $aSheets = _Excel_SheetList($oWorkbook)
            ;_ArrayDisplay($aSheets)

            $iMatch = _ArraySearch($aSheets,$sSheetName,0,0,0,0,1,0)

            Switch $iMatch
                Case -1
                    ;The sheet doesn't exist...add it.
                    _Excel_SheetAdd($oWorkbook, -1, False, 1, $sSheetName)
                Case Else
                    ;The sheet already exists...overwrite it's contents
            EndSwitch
    EndSwitch

    ;Write data to the range.
    Switch $sFormat
        Case "CSV"
            Local $sTempCSV = WriteDataToWorkbookViaCSV($aInput)
            Local $oSOURCE = _Excel_BookOpen($oExcel,$sTempCSV,True)

            Local $sDrive = "", $sDir = "", $sFileName = "", $sExtension = ""
            Local $aPathSplit = _PathSplit($sTempCSV, $sDrive, $sDir, $sFileName, $sExtension)

            _Excel_SheetCopyMove($oSOURCE, Default, $oWorkbook, $sSheetName, False, True)
            _Excel_SheetDelete ($oWorkbook, $sSheetName)
            $oWorkbook.Sheets($sFileName).Name = $sSheetName
            _Excel_BookClose($oSOURCE, False)    ;Close the source file
            FileDelete($sTempCSV)

        Case Else
            _Excel_RangeWrite($oWorkbook, $sSheetName, $aInput, Default, True)
    EndSwitch

    ;MsgBox(0,0,0)

EndFunc   ;==>WriteDataToWorkbook

Func WriteDataToWorkbookViaCSV($aInput)

    Local $sLineToWrite

    Local $sTempFile = _TempFile(Default,"",".csv")
    ConsoleWrite($sTempFile & @CRLF)
    Local $hFileOpen = FileOpen($sTempFile, $FO_OVERWRITE)

    Local $sTempCSV
    For $i = 0 To UBound($aInput) - 1
        $sLineToWrite = '"' & _ArrayToString($aInput, '","', $i, $i, "") & '"' & @CRLF
        ;ConsoleWrite($sLineToWrite)
        FileWrite($hFileOpen, $sLineToWrite)
        ;ConsoleWrite("------------" & @CRLF & $sTempCSV & @CRLF & "------------" & @CRLF)
        ;MsgBox(0,0,0)
    Next

    Return $sTempFile

EndFunc

I guess I'm curious if someone has a better solution than this that isn't so brute force.  My big change from before and now is I was trying to create a super long variable to a file and I flipped it to the current format with the filewrite with a handle.

[argumentum]

Read about the meaning of each entry ( https://transition.fcc.gov/Bureaus/Wireless/Public_Notices/2000/da000046.txt ).
That should help you with the cleanup.

[Zurch1818]

Thanks for finding that definition file argumentum.  I was basing my trimming based off a colleague's work but I don't really know what all the data means.  I do have the array trimming working off what he had deemed to be important.  I can see I need to tweak things a little bit more.

13 hours ago, water said:

You could give function _Excel_BookOpenText a try to directly import the pipe delimited file.

Thanks for the suggestion.  Maybe I could try creating a new pipe delimited text file based on my cleaned up array and then copying that to my file.  I think this might be a little cleaner than my current solution putting in all those extra quotes to work as a csv.

As you can see, I currently have it set up to make a new tab and delete a tab (pretty clunky but it is what I band-aided together).  If I wanted to copy the data instead of the whole tab into a sheet on a different workbook, what's the cleanest way to copy the whole sheet data to an existing sheet.  I assume it would be something with _Excel_RangeCopyPaste but I am hung up on what to use for the $vSourceRange.  Would it just be easiest to set the range like in VBA with creating a COM range object based off the $oWorkbook object?  I'm open to trying a more efficient way.  I think it would be something like this:

Local $vSourceRange = $oWorkbook.ActiveSheet.Usedrange  ;There is only ever going to be one sheet in this workbook. Otherwise should probably not use the ActiveSheet method.

 

[water]

I suggest to try the UsedRange approach

[Zurch1818]

Alright...I've got this thing working.  Final code is here in case anyone wants to use it.  The functions variables still reference a CSV but they are just a relic from when they did work off a CSV.  Now everything is based off a TXT.

expandcollapse popup

Func WriteDataToWorkbook($sSheetName, $aInput, $sFormat = "Excel")

    Local $iMatch

    Switch $oWorkbook.Sheets(1).Name
        Case "Sheet1"
            ;There is only one sheet...rename the existing sheet
            $oWorkbook.Sheets(1).Name = $sSheetName
        Case Else
            ;The first sheet has already been renamed...add a new sheet to the workbook (if it doesn't already exist).
            Local $aSheets = _Excel_SheetList($oWorkbook)
            ;_ArrayDisplay($aSheets)

            $iMatch = _ArraySearch($aSheets, $sSheetName, 0, 0, 0, 0, 1, 0)

            Switch $iMatch
                Case -1
                    ;The sheet doesn't exist...add it.
                    _Excel_SheetAdd($oWorkbook, -1, False, 1, $sSheetName)
                Case Else
                    ;The sheet already exists...overwrite it's contents
            EndSwitch
    EndSwitch

    ;Write data to the range.
    Switch $sFormat
        Case "CSV"
            Local $sTempCSV = WriteDataToWorkbookViaCSV($aInput)
            Local $oSOURCEBook = _Excel_BookOpenText($oExcel, $sTempCSV, Default, $xlDelimited, Default, Default, "|")
            Local $oSOURCESheet = $oSOURCEBook.ActiveSheet
            Local $oSOURERange = $oSOURCESheet.Usedrange  ;There is only ever going to be one sheet in this workbook. Otherwise should probably not use the ActiveSheet method.

            Local $sDrive = "", $sDir = "", $sFileName = "", $sExtension = ""
            Local $aPathSplit = _PathSplit($sTempCSV, $sDrive, $sDir, $sFileName, $sExtension)

            _Excel_RangeCopyPaste($oSOURCESheet, $oSOURERange, $oWorkbook.Sheets($sSheetName).Range("A1"), False, $xlPasteValues)
            _Excel_BookClose($oSOURCEBook, False)    ;Close the source file
            FileDelete($sTempCSV)

        Case Else
            _Excel_RangeWrite($oWorkbook, $sSheetName, $aInput, Default, True)
    EndSwitch

    ;MsgBox(0,0,0)

EndFunc   ;==>WriteDataToWorkbook

Func WriteDataToWorkbookViaCSV($aInput)

    Local $sLineToWrite

    Local $sTempFile = _TempFile(Default, "", ".txt")
    ConsoleWrite($sTempFile & @CRLF)
    Local $hFileOpen = FileOpen($sTempFile, $FO_OVERWRITE)

    Local $sTempCSV
    For $i = 0 To UBound($aInput) - 1
        $sLineToWrite = _ArrayToString($aInput, '|', $i, $i) & @CRLF
        ;MsgBox(0,"Line To Write",$sLineToWrite)
        FileWrite($hFileOpen, $sLineToWrite)
        ;ConsoleWrite("------------" & @CRLF & $sTempCSV & @CRLF & "------------" & @CRLF)
        ;MsgBox(0,0,0)
    Next

    Return $sTempFile

EndFunc   ;==>WriteDataToWorkbookViaCSV

Now that I have a workaround functioning, I'm curious why the _Excel_RangeWrite didn't actually work as I was just trying to put an AutoIt Array to an Excel range.  I've used this function in the past and it has been a pretty reliable (even with the strong-arming the last argument). 

Overall, I've been very happy with your Excel UDF, Water.  It's nice that I can use the Excel COM objects I'm familiar with while having the extra power of AutoIt to more efficiently manipulate large datasets.  It has really helped me really expand the capabilities of the code that I write while still being efficient and to run tasks with the Windows Task Scheduler on a computer solely running your functions multiple times a day.  For someone that is a structural engineer by trade and just a hobby coder, it's been nice that I didn't really need to learn anything significantly new.  It's always been "why learn something new (like Python) when what I know is already efficient?"  Once again, thanks for your effort with this UDF and all the support you have given me over these last 6 years that I've been coding.

[water]

Glad you got it working
I had a quick look at the docu and your code and noticed, that you have been setting the wrong parameter to activate $bForceFunc. You have set parameter #5 but it should be parameter #6

[Zurch1818]

Argh...I'll give it another crack and see what happens and report back. I guess that's why debugging is something that can take the longest time to complete... especially when it's coding done at the end of the day.

Edited May 23 by Zurch1818

[Zurch1818]

@water OK...I've finally made some time to look into this.  My updated code for this part is below.  Now using the 6th argument.

expandcollapse popup

Func WriteDataToWorkbook($sSheetName, $aInput, $sFormat = "Excel")

    Local $iMatch
    Local $oCurrentWorksheet

    Switch $oWorkbook.Sheets(1).Name
        Case "Sheet1"
            ;There is only one sheet...rename the existing sheet
            $oCurrentWorksheet = $oWorkbook.Sheets(1)
            $oCurrentWorksheet.Name = $sSheetName
        Case Else
            ;The first sheet has already been renamed...add a new sheet to the workbook (if it doesn't already exist).
            Local $aSheets = _Excel_SheetList($oWorkbook)
            ;_ArrayDisplay($aSheets)

            $iMatch = _ArraySearch($aSheets, $sSheetName, 0, 0, 0, 0, 1, 0)

            Switch $iMatch
                Case -1
                    ;The sheet doesn't exist...add it.
                    $oCurrentWorksheet = _Excel_SheetAdd($oWorkbook, -1, False, 1, $sSheetName)
                Case Else
                    ;The sheet already exists...overwrite it's contents
                    $oCurrentWorksheet = $oWorkbook.Sheets($sSheetName)
            EndSwitch
    EndSwitch

    ;Write data to the range.
    Switch $sFormat
        Case "CSV" ;"CSV"   ESSENTIALLY TURNING THIS SWITCH OFF AFTER DISCUSSION WITH WATER ON AUTOIT FORUMS.
            Local $sTempCSV = WriteDataToWorkbookViaCSV($aInput)
            Local $oSOURCEBook = _Excel_BookOpenText($oExcel, $sTempCSV, Default, $xlDelimited, Default, Default, "|")
            Local $oSOURCESheet = $oSOURCEBook.ActiveSheet
            Local $oSOURERange = $oSOURCESheet.Usedrange  ;There is only ever going to be one sheet in this workbook. Otherwise should probably not use the ActiveSheet method.

            Local $sDrive = "", $sDir = "", $sFileName = "", $sExtension = ""
            Local $aPathSplit = _PathSplit($sTempCSV, $sDrive, $sDir, $sFileName, $sExtension)

            _Excel_RangeCopyPaste($oSOURCESheet, $oSOURERange, $oCurrentWorksheet.Range("A1"), False, $xlPasteValues)
            _Excel_BookClose($oSOURCEBook, False)    ;Close the source file
            FileDelete($sTempCSV)

        Case Else
            Local $tempResult = _Excel_RangeWrite($oWorkbook, $oCurrentWorksheet, $aInput, "A1", Default, True)

            If $tempResult = 0 Then ConsoleWrite($sSheetName & " - ERROR = " & @error & @CRLF)
    EndSwitch

    MsgBox(0,0,0)

EndFunc   ;==>WriteDataToWorkbook

Func WriteDataToWorkbookViaCSV($aInput)

    Local $sLineToWrite

    Local $sTempFile = _TempFile(Default, "", ".txt")
    ConsoleWrite($sTempFile & @CRLF)
    Local $hFileOpen = FileOpen($sTempFile, $FO_OVERWRITE)

    Local $sTempCSV
    For $i = 0 To UBound($aInput) - 1
        $sLineToWrite = _ArrayToString($aInput, '|', $i, $i) & @CRLF
        ;MsgBox(0,"Line To Write",$sLineToWrite)
        FileWrite($hFileOpen, $sLineToWrite)
        ;ConsoleWrite("------------" & @CRLF & $sTempCSV & @CRLF & "------------" & @CRLF)
        ;MsgBox(0,0,0)
    Next

    Return $sTempFile

EndFunc   ;==>WriteDataToWorkbookViaCSV

It seems it just gets hung up on writing the array to the sheet.  I probably could write the extended error to my code, but since it wasn't even getting to this step after a few minutes of running, I just aborted.  For reference, my workaround code works relatively quickly.  I haven't timed it but it doesn't take that long.  

If you want to try the data for yourself, I have saved my workaround text file to my OneDrive account (DATA).

[water]

Unfortunately I can't test as I no longer have MS Office available.
You could check a few things:

• Run with a smaller $aInput. _Excel_RangeWrite uses _ArrayTranspose internally when called with $bForceFunc = True.
  Seems your input data has about 1 million cells, this could take some time to transpose.
• Maybe there is some invalid data in the array. Excel doesn't properly process such data.
  In the wiki you find a script to test your array for all possible (known) problems.
  https://www.autoitscript.com/wiki/Excel_UDF#Excel_RangeWrite_writes_no_data
• Test every call of a Excel function for errors. Both _Excel_* functions and the direct COM calls you use.

 

[ioa747]

I explored a bit.
look at the results, you might find something useful
 

expandcollapse popup

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

Local $hTimer = TimerInit()

Local $CsvFile = _TXTtoCSV(@ScriptDir & "\RAW_DATA.txt") ; *** Need this file to start <--
ConsoleWrite("$CsvFile=" & $CsvFile & @CRLF)
;~ ShellExecute($CsvFile)
ConsoleWrite("_TXTtoCSV() processed in: " & Round(TimerDiff($hTimer) / 1000, 3) & " seconds " & @LF)
ConsoleWrite("" & @CRLF)

$hTimer = TimerInit()
Local $XLFile = _CSVToXLSX($CsvFile, @ScriptDir & "\test1.xlsx")
ConsoleWrite("$XLFile=" & $XLFile & @CRLF)
ConsoleWrite("_CSVToXLSX() processed in: " & Round(TimerDiff($hTimer) / 1000, 3) & " seconds " & @LF)
ConsoleWrite("" & @CRLF)

ShellExecute($XLFile)

$hTimer = TimerInit()
Local $aTest = _Test(@ScriptDir & "\RAW_DATA.txt")
ConsoleWrite("TestFile=" & @ScriptDir & "\Test.csv" & @CRLF)
ConsoleWrite("_Test() processed in: " & Round(TimerDiff($hTimer) / 1000, 3) & " seconds " & @LF)
ConsoleWrite("" & @CRLF)

_ArrayDisplay($aTest)

;--------------------------------------------------------------------------------------------------------------------------------
Func _CSVToXLSX($CsvFile, $XLFile)
    Local $oExcel, $oWorkbook

    If FileExists($XLFile) Then FileDelete($XLFile)

    ToolTip("please be patient", @DesktopWidth * 0.5, @DesktopHeight * 0.5, "conversion in process...", 1)

    $oExcel = _Excel_Open(False)

    $oWorkbook = _Excel_BookOpen($oExcel, $CsvFile)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "", "Error opening " & $CsvFile)

    _Excel_BookSaveAs($oWorkbook, $XLFile, $xlOpenXMLWorkbook)
    _Excel_BookClose($oWorkbook)
    _Excel_Close($oExcel)

    ToolTip("")

    If FileExists($XLFile) Then Return $XLFile

    Return SetError(1, 0, "")

EndFunc   ;==>_CSVToXLSX
;--------------------------------------------------------------------------------------------------------------------------------
Func _TXTtoCSV($sFilePath)

    Local $CsvFile = @ScriptDir & "\RAW_DATA.csv"
    If FileExists($CsvFile) Then FileDelete($CsvFile)

    Local $sFileData = FileRead($sFilePath)

    ToolTip("please be patient", @DesktopWidth * 0.5, @DesktopHeight * 0.5, "FileRead in process...", 1)

    $sFileData = StringReplace($sFileData, "|", ";")

    ToolTip("please be patient", @DesktopWidth * 0.5, @DesktopHeight * 0.5, "FileWrite in process...", 1)

    Local $hFileOpen = FileOpen($CsvFile, $FO_OVERWRITE)
    FileWrite($hFileOpen, $sFileData)

    ToolTip("")

    If FileExists($CsvFile) Then Return $CsvFile

    Return SetError(1, 0, "")

EndFunc   ;==>_TXTtoCSV
;--------------------------------------------------------------------------------------------------------------------------------
Func _Test($sFilePath)
    Local $aArray

    Local $CsvFile = @ScriptDir & "\Test.csv"
    If FileExists($CsvFile) Then FileDelete($CsvFile)

    ToolTip("please be patient", @DesktopWidth * 0.5, @DesktopHeight * 0.5, "FileRead in process...", 1)
    _FileReadToArray($sFilePath, $aArray, $FRTA_NOCOUNT, "|")

    ToolTip("please be patient", @DesktopWidth * 0.5, @DesktopHeight * 0.5, "FileWrite in process...", 1)
    _FileWriteFromArray($CsvFile, $aArray, Default, Default, ";")
    ToolTip("")

    If FileExists($CsvFile) Then Return $aArray

    Return SetError(1, 0, "")

EndFunc   ;==>_Test
;--------------------------------------------------------------------------------------------------------------------------------

 

[Zurch1818]

Thanks for the input all. 

@water - It seems that my needs are just outside of what this function was designed for.  It works flawlessly with smaller datasets.  It makes sense to me that transposing an array that large can be very resource heavy.  Your _Excel_BookOpenText function works well for my application, so that is what I will continue to use.  Conceptually speaking, it's not the shortest distance to get the data into an Excel file via a crutch file but it actually is pretty efficient time wise.  I was just curious more than anything why that function wasn't working.  I wish I could just work with CSV files but I've noticed that our cloud database seems to sometimes mess up CSV file delimiters.  It's nice this data is already | delimited.

@ioa747 - It's kind of remarkable how much heavier Excel files are vs CSVs.  You are pretty much doing my workaround in reverse with the limitations I experienced.  That file I posted is actually the output from one of my processed arrays (there eventually will be 6 of them...all massive in size) right before it gets fed into the _Excel_BookOpenText function.

[argumentum]

_Excel_RangeWrite Not Working With Large Dataset ...

Well, that's the thing. I don't think that excel is a database. And abusing it is just wrong.
But, is hard to find a programmer in an office environment so people work with what they have, but eventually they are going to need to code something to handle large datasets. And all this excel stuff, ...you are lucky to not run into the file getting corrupted.

I know that am not helping but is something that I feel should be said.

[water]

42 minutes ago, argumentum said:

I know that am not helping but is something that I feel should be said.

I think it's always good to rethink your design decisions when a script doesn't produce the desired results.
I therefore consider your post to be very helpful.

[Zurch1818]

@argumentum - I hear you.  I tend to use CSVs for dumping data as they are just simpler and more lightweight.  I'm guessing my data isn't getting corrupted is because it wasn't Excel in the first place...but a pipe delimited file text file that I needed to tweak some of the values.  For whatever reason, Caspio seems to interact better with Excel files than CSV's, so that's what I'm stuck with.  Caspio can't handle pipe delimited files.

[Zurch1818]

I also agree that Excel is a terrible database tool and I don't believe in using it as such.  Caspio is actually a decent database from my experience with it.  I'm just trying to automate updating a table in there with the tools that are available to me.  I only need to work with a large dataset when I overwrite the table.  Once I get this thing operational, the FCC also publishes daily changes files that are much more manageable (10 to 100 lines or so).  I figure I'd design the code to work for the ridiculous case so I won't have to worry about having two different codes or having to manually do anything.