Bert Posted November 10, 2006 Share Posted November 10, 2006 (edited) 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 popupFunc _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 The Vollatran project My blog: http://www.vollysinterestingshit.com/ Link to comment Share on other sites More sharing options...
Bert Posted November 11, 2006 Author Share Posted November 11, 2006 bump The Vollatran project My blog: http://www.vollysinterestingshit.com/ Link to comment Share on other sites More sharing options...
Bert Posted November 12, 2006 Author Share Posted November 12, 2006 bump bump bumpity bump..... The Vollatran project My blog: http://www.vollysinterestingshit.com/ Link to comment Share on other sites More sharing options...
Bert Posted November 13, 2006 Author Share Posted November 13, 2006 bumpish! Anyone got a idea on this one? The Vollatran project My blog: http://www.vollysinterestingshit.com/ Link to comment Share on other sites More sharing options...
Bert Posted November 13, 2006 Author Share Posted November 13, 2006 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 The Vollatran project My blog: http://www.vollysinterestingshit.com/ Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now