[Solved] Create an array from filtered Excel sheet

[UEZ]

SpecialCells: 64.8110058748907
RangeRead: 23518.2372043567
ArrayConcat: 3263.37492148338

 

[UEZ]

Using the clipboard is the fastest version but clipboard will be overwritten:

expandcollapse popup

Local $fTimer = TimerInit()

$oRange = $oWorkbook.ActiveSheet.Usedrange.SpecialCells($xlCellTypeVisible)
_Excel_RangeCopyPaste($oWorkbook.Activesheet, $oWorkbook.ActiveSheet.AutoFilter.Range)
$sClipboard_Excel = ClipGet() ;tab delimited
$aResult = _CSVSplit($sClipboard_Excel, @TAB)

ConsoleWrite(TimerDiff($fTimer) & @CRLF)

_ArrayDisplay($aResult)


; #FUNCTION# ====================================================================================================================
; Name...........: _CSVSplit
; Description ...: Converts a string in CSV format to a two dimensional array (see comments)
; Syntax.........: CSVSplit ( $aArray [, $sDelim ] )
; Parameters ....: $aArray  - The array to convert
;                  $sDelimiter  - Optional - Delimiter set to comma by default (see 2nd comment)
; Return values .: Success  - Returns a two dimensional array or a one dimensional array (see 1st comment)
;                  Failure  - Sets @error to:
;                 |@error = 1 - First parameter is not a valid string
;                 |@error = 2 - Second parameter is not a valid string
;                 |@error = 3 - Could not find suitable delimiter replacements
; Author ........: czardas
; Comments ......; Returns a one dimensional array if the input string does not contain the delimiter string
;                ; Some CSV formats use semicolon as a delimiter instead of a comma
;                ; Set the second parameter to @TAB To convert to TSV
; ===============================================================================================================================
Func _CSVSplit($string, $sDelimiter = ",") ; Parses csv string input and returns a one or two dimensional array
    If Not IsString($string) Or $string = "" Then Return SetError(1, 0, 0) ; Invalid string
    If Not IsString($sDelimiter) Or $sDelimiter = "" Then Return SetError(2, 0, 0) ; Invalid string

    $string = StringRegExpReplace($string, "[\r\n]+\z", "") ; [Line Added] Remove training breaks
    Local $iOverride = 63743, $asDelim[3] ; $asDelim => replacements for comma, new line and double quote
    For $i = 0 To 2
        $asDelim[$i] = __GetSubstitute($string, $iOverride) ; Choose a suitable substitution character
        If @error Then Return SetError(3, 0, 0) ; String contains too many unsuitable characters
    Next
    $iOverride = 0

    Local $aArray = StringRegExp($string, '\A[^"]+|("+[^"]+)|"+\z', 3) ; Split string using double quotes delim - largest match
    $string = ""

    Local $iBound = UBound($aArray)
    For $i = 0 To $iBound - 1
        $iOverride += StringInStr($aArray[$i], '"', 0, -1) ; Increment by the number of adjacent double quotes per element
        If Mod($iOverride + 2, 2) = 0 Then ; Acts as an on/off switch
            $aArray[$i] = StringReplace($aArray[$i], $sDelimiter, $asDelim[0]) ; Replace comma delimeters
            $aArray[$i] = StringRegExpReplace($aArray[$i], "(\r\n)|[\r\n]", $asDelim[1]) ; Replace new line delimeters
        EndIf
        $aArray[$i] = StringReplace($aArray[$i], '""', $asDelim[2]) ; Replace double quote pairs
        $aArray[$i] = StringReplace($aArray[$i], '"', '') ; Delete enclosing double quotes - not paired
        $aArray[$i] = StringReplace($aArray[$i], $asDelim[2], '"') ; Reintroduce double quote pairs as single characters
        $string &= $aArray[$i] ; Rebuild the string, which includes two different delimiters
    Next
    $iOverride = 0

    $aArray = StringSplit($string, $asDelim[1], 2) ; Split to get rows
    $iBound = UBound($aArray)
    Local $aCSV[$iBound][2], $aTemp
    For $i = 0 To $iBound - 1
        $aTemp = StringSplit($aArray[$i], $asDelim[0]) ; Split to get row items
        If Not @error Then
            If $aTemp[0] > $iOverride Then
                $iOverride = $aTemp[0]
                ReDim $aCSV[$iBound][$iOverride] ; Add columns to accomodate more items
            EndIf
        EndIf
        For $j = 1 To $aTemp[0]
            If StringLen($aTemp[$j]) Then
                If Not StringRegExp($aTemp[$j], '[^"]') Then ; Field only contains double quotes
                    $aTemp[$j] = StringTrimLeft($aTemp[$j], 1) ; Delete enclosing double quote single char
                EndIf
                $aCSV[$i][$j - 1] = $aTemp[$j] ; Populate each row
            EndIf
        Next
    Next

    If $iOverride > 1 Then
        Return $aCSV ; Multiple Columns
    Else
        For $i = 0 To $iBound - 1
            If StringLen($aArray[$i]) And (Not StringRegExp($aArray[$i], '[^"]')) Then ; Only contains double quotes
                $aArray[$i] = StringTrimLeft($aArray[$i], 1) ; Delete enclosing double quote single char
            EndIf
        Next
        Return $aArray ; Single column
    EndIf

EndFunc   ;==>_CSVSplit

; #INTERNAL_USE_ONLY# ===========================================================================================================
; Name...........: __GetSubstitute
; Description ...: Searches for a character to be used for substitution, ie one not contained within the input string
; Syntax.........: __GetSubstitute($string, ByRef $iCountdown)
; Parameters ....: $string   - The string of characters to avoid
;                  $iCountdown - The first code point to begin checking
; Return values .: Success   - Returns a suitable substitution character not found within the first parameter
;                  Failure   - Sets @error to 1 => No substitution character available
; Author ........: czardas
; Comments ......; This function is connected to the function _CSVSplit and was not intended for general use
;                  $iCountdown is returned ByRef to avoid selecting the same character on subsequent calls to this function
;                  Initially $iCountown should be passed with a value = 63743
; ===============================================================================================================================
Func __GetSubstitute($string, ByRef $iCountdown)
    If $iCountdown < 57344 Then Return SetError(1, 0, "") ; Out of options
    Local $sTestChar
    For $i = $iCountdown To 57344 Step -1
        $sTestChar = ChrW($i)
        $iCountdown -= 1
        If Not StringInStr($string, $sTestChar) Then
            Return $sTestChar
        EndIf
    Next
    Return SetError(1, 0, "") ; Out of options
EndFunc   ;==>__GetSubstitute

 

It takes around 800 ms to generate the array.

 

Edited December 3, 2015 by UEZ

[water]

Using the internal transpose function makes the script much faster:

Local $hSpecialCells, $iSpecialCells = 0, $hRangeRead, $iRangeRead = 0, $hArrayConcat, $iArrayConcat = 0
$hSpecialCells = TimerInit()
$oRange = $oWorkbook.ActiveSheet.Usedrange.SpecialCells($xlCellTypeVisible)
$iSpecialCells = TimerDiff($hSpecialCells)
Local $aResult[1][$oRange.columns.Count], $aContent
; Read the data of all Ranges in the Area and concatenate the returned arrays.
For $oArea In $oRange.Areas
    $hRangeRead = TimerInit()
    $aContent = _Excel_RangeRead($oWorkbook, Default, $oArea, Default, True)
    $iRangeRead = $iRangeRead + TimerDiff($hRangeRead)
    $hArrayConcat = TimerInit()
    _ArrayConcatenate($aResult, $aContent)
    $iArrayConcat = $iArrayConcat + TimerDiff($hArrayConcat)
Next
MsgBox(0, "", "SpecialCells: " & $iSpecialCells & @CRLF & _
              "RangeRead: " & $iRangeRead & @CRLF & _
              "ArrayConcat: " & $iArrayConcat)

What do you get now?

Edited December 3, 2015 by water
Fixed: _Excel_RangeReadEX

[UEZ]

_Excel_RangeReadEX?

[water]

Drop the EX. Was for testonly. 

[UEZ]

Much better now:

SpecialCells: 37.1230689016705
RangeRead: 879.62468028853
ArrayConcat: 3876.63181144516

I think _ArrayConcatenate can be replaced by a faster array function.

Edited December 3, 2015 by UEZ

[water]

So it seems the internal Excel function "Transpose" has a problem

[water]

I think I will provide a solution to get the filtered rows in a single array and add that to the Excel wiki.

Done

[UEZ]

I'm gettinbg this result for an individual _ArrayConcatenate function:

Local $hSpecialCells, $iSpecialCells = 0, $hRangeRead, $iRangeRead = 0, $hArrayConcat, $iArrayConcat = 0
$hSpecialCells = TimerInit()
$oRange = $oWorkbook.ActiveSheet.Usedrange.SpecialCells($xlCellTypeVisible)
$iSpecialCells = TimerDiff($hSpecialCells)

Local $iUBound1 = 10000
Local $iUBound2 = $oRange.columns.Count
Local $aResult[$iUBound1][$iUBound2], $aContent, $iX, $iY, $iPosY = 0
; Read the data of all Ranges in the Area and concatenate the returned arrays.
For $oArea In $oRange.Areas
    $hRangeRead = TimerInit()
    $aContent = _Excel_RangeRead($oWorkbook, Default, $oArea, Default, True)
    $iRangeRead += TimerDiff($hRangeRead)
    $hArrayConcat = TimerInit()
;~     _ArrayConcatenate($aResult, $aContent)
    For $iY = 0 To UBound($aContent) - 1
        For $iX = 0 To UBound($aContent, 2) - 1
            $aResult[$iPosY][$iX] = $aContent[$iY][$iX]
        Next
        $iPosY += 1
        If $iPosY = $iUBound1 Then
            ReDim $aResult[$iPosY + $iUBound1][$iUBound2]
            $iUBound1 += $iPosY
        EndIf
    Next
    $iArrayConcat += TimerDiff($hArrayConcat)
Next
ReDim $aResult[$iPosY][$iUBound2]

MsgBox(0, "", "SpecialCells: " & $iSpecialCells & @CRLF & _
              "RangeRead: " & $iRangeRead & @CRLF & _
              "ArrayConcat: " & $iArrayConcat)

 

SpecialCells: 400.998033492214
RangeRead: 885.23066438404
ArrayConcat: 94.1944913149327