_XLArrayRead problem. Data not being returned

[Bert]

I have a script that sorts items in a excel spreadsheet into a ini file. The spreadsheet has data in 4 columns. When I use the function _excelconvert, it works fine. What happens is the B cell is used to sort out what I need in the C column. There are over 16000 lines in the spreadsheet.

My problem is when I try to use _excelconvert2. It isn't capturing anything. I need it to use column D instead of B, then capture the items in column C that match.

example of how I would use _excelconvert:

_excelconvert("PRINTER.NETWORK","HP DESIGNJET","bin/treepaste.ini","DESIGNJET")

example of how I would use _excelconvert2:

_excelconvert2("MICROSOFT","","bin/treepaste.ini","microsoft")

Note: the "" basically allows for all items found to be returned. If I add a character such as a "A", it would only return items that start with "A".

expandcollapse popup

Func _excelconvert($bcell,$match, $file1, $key1)
    ;$bcell : B cell item 
    ;$match: What is used in the C cell to filter. String starts from the left
    ;$file1: Path to save string to. INI file is used
    ;$key1: The key used in the ini where the string is written to.
local $s_StringOfSingleLine
$s_FilePath=@ScriptDir&"\bin\USD_Configuration_Items.xls" 
$XLArray=_XLArrayRead($s_FilePath,1,"B1:C50000");&$i_LastRow-1)
_XLClose($s_FilePath,1)
local $_dataget 

for $i=0 to ubound ($XLArray,2)-1
if $XLArray[0][$i]<>"" then
if $XLArray[0][$i]=$bcell then $_dataget&="|"&$XLArray[1][$i]  ;&"|"
EndIf
Next
;MsgBox(0,"",$_dataget) ;test to see if data is being returned
;IniWrite("bin\test.ini", "server", "server", $_dataget) ;for testing. do not delete

$data = $_dataget
$dataARRAY = StringSplit($data, "|")
$output = ""

For $i = 1 To $dataARRAY[0]
    If $match == StringLeft($dataARRAY[$i], StringLen($match)) Then
        $output = $output & $dataARRAY[$i] & "|"
    EndIf
Next

$output = StringTrimRight($output, 1)
IniWrite($file1,$key1,"KEY",$output)
endfunc

Func _excelconvert2($Dcell,$match, $file1, $key1)
    ;$bcell : D cell item 
    ;$match: What is used in the C cell to filter. String starts from the left
    ;$file1: Path to save string to. INI file is used
    ;$key1: The key used in the ini where the string is written to.
local $s_StringOfSingleLine
$s_FilePath=@ScriptDir&"\bin\USD_Configuration_Items.xls" 
$XLArray=_XLArrayRead($s_FilePath,1,"D1:C50000");&$i_LastRow-1)
_XLClose($s_FilePath,1)
local $_dataget 

for $i=0 to ubound ($XLArray,2)-1
if $XLArray[0][$i]<>"" then
if $XLArray[0][$i]=$Dcell then $_dataget&="|"&$XLArray[1][$i]  ;&"|"
EndIf
Next
MsgBox(262144,"",$_dataget) ;test to see if data is being returned
IniWrite("bin\test.ini", "test", "test", $_dataget) ;for testing. do not delete

$data = $_dataget
$dataARRAY = StringSplit($data, "|")
$output = ""

For $i = 1 To $dataARRAY[0]
    If $match == StringLeft($dataARRAY[$i], StringLen($match)) Then
        $output = $output & $dataARRAY[$i] & "|"
    EndIf
Next

$output = StringTrimRight($output, 1)
IniWrite($file1,$key1,"KEY",$output)
endfunc

The spreadsheet has 4 columns, and 16000 lines.

Edited November 11, 2006 by vollyman

[Bert]

bump

[Bert]

bump bump bumpity bump.....

[Bert]

bumpish!

Anyone got a idea on this one?

[Bert]

Nevermind. I figured it out. I was reading the array wrong. The fixed code:

Func _excelconvert2($Dcell,$match, $file1, $key1)
    ;$bcell : D cell item 
    ;$match: What is used in the C cell to filter. String starts from the left
    ;$file1: Path to save string to. INI file is used
    ;$key1: The key used in the ini where the string is written to.
local $s_StringOfSingleLine
$s_FilePath=@ScriptDir&"\bin\USD_Configuration_Items.xls" 
$i_LastRow=_XLLastRow($s_FilePath,1,0)
$XLArray=_XLArrayRead($s_FilePath,1,"C1:D"&$i_LastRow-1)
_XLClose($s_FilePath,1)
local $_dataget 
for $i=0 to ubound ($XLArray,2)-1
if $XLArray[1][$i]<>"" then
if $XLArray[1][$i]=$Dcell then $_dataget&="|"&$XLArray[0][$i]  
EndIf
Next
MsgBox(262144,"",$_dataget) ;test to see if data is being returned
;IniWrite(@ScriptDir &"\bin\test.ini", "test", "test", $_dataget) ;for testing. do not delete
$data = $_dataget
$dataARRAY = StringSplit($data, "|")
$output = ""
For $i = 1 To $dataARRAY[0]
    If $match == StringLeft($dataARRAY[$i], StringLen($match)) Then
        $output = $output & $dataARRAY[$i] & "|"
    EndIf
Next
$output = StringTrimLeft($output, 1)
$output2 = StringTrimRight($output, 1)
IniWrite($file1,$key1,"KEY",$output2)
endfunc