Maarten002 Posted October 2, 2012 Share Posted October 2, 2012 Hello all I'm trying to read a sheet of an excel file but for some reason, the script below is giving me the first cel of the second row in stead of the first row. Any idea what i'm doing wrong? Thanks! Const $excelFile=@ScriptDir & "\Test.xlsx" Const $excelSheet = "[Sheet1$]" Global Const $adOpenStatic = 3 Global Const $adLockOptimistic = 3 Global Const $adCmdText = 0x0001 $objConnection = ObjCreate("ADODB.Connection") $objRecordSet = ObjCreate("ADODB.Recordset") $objConnection.Open ("Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=""" & $excelFile & """;" & _ "Extended Properties=""Excel 12.0 xml;HDR=Yes;IMEX=1"";") $objRecordSet.Open ("SELECT * FROM " & $excelSheet, $objConnection, $adOpenStatic, $adLockOptimistic, $adCmdText) $objRecordSet.MoveFirst ConsoleWrite($objRecordSet.Fields(0).value & @CR) Link to comment Share on other sites More sharing options...
water Posted October 2, 2012 Share Posted October 2, 2012 Welcome to AutoIt and the forum! Why don't you just use the builtin Excel UDF? Function _ExcelReadSheetToArray should do what you want. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Maarten002 Posted October 2, 2012 Author Share Posted October 2, 2012 Hi water, thanks for the reply. Problem is that the build in functions are a bit to slow on big files (over 300000 lines), and this script needs to run on big files Link to comment Share on other sites More sharing options...
water Posted October 2, 2012 Share Posted October 2, 2012 Here is a much faster version of function _ExcelReadSheetToArray (taken from Trac Ticket #2219). Processing time of an example sheet was reduced from 45 seconds to .2 seconds. expandcollapse popup; #FUNCTION# ==================================================================================================================== ; Name...........: _ExcelReadSheetToArray ; Description ...: Create a 2D array from the rows/columns of the active worksheet. ; Syntax.........: _ExcelReadSheetToArray($oExcel[, $iStartRow = 1[, $iStartColumn = 1[, $iRowCnt = 0[, $iColCnt = 0]]]]) ; Parameters ....: $oExcel - excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ; $iStartRow - Row number to start reading, defaults to 1 (first row) ; $iStartColumn - Column number to start reading, defaults to 1 (first column) ; $iRowCnt - Count of rows to read, defaults to 0 (all) ; $iColCnt - Count of columns to read, defaults to 0 (all) ; $iColShift - Match R1C1 column position, or start array in column 0. Default is 0 (match R1C1 values) ; Return values .: Success - Returns a 2D array with the specified cell contents by [$row][$col] ; Failure - Returns 0 and sets @error on errors: ; |@error=1 - Specified object does not exist ; |@error=2 - Start parameter out of range ; |@extended=0 - Row out of range ; |@extended=1 - Column out of range ; |@error=3 - Count parameter out of range ; |@extended=0 - Row count out of range ; |@extended=1 - Column count out of range ; Author ........: SEO ; Modified.......: PsaltyDS 01/04/08 - 2D version, litlmike - Column shift parm, ; Footswitch/Golfinhu - Speed enhancement, Spiff59 - protected sheets ; Remarks .......: Returned array has row count in [0][0] and column count in [0][1] (unless ; Except for the counts above, row 0 and col 0 of the returned array are empty, as actual ; cell data starts at [1][1] to match R1C1 numbers. ; By default the entire sheet is returned. ; If the sheet is empty [0][0] and [0][1] both = 0. ; Related .......: ; Link ..........: ; Example .......: No ; =============================================================================================================================== Func _ExcelReadSheetToArray($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0, $iColShift = 0) ; Parameter edits If Not IsObj($oExcel) Then Return SetError(1, 0, 0) If $iStartRow < 1 Then Return SetError(2, 0, 0) If $iStartColumn < 1 Then Return SetError(2, 1, 0) If $iRowCnt < 0 Then Return SetError(3, 0, 0) If $iColCnt < 0 Then Return SetError(3, 1, 0) Local $iLastRow = $oExcel.Activesheet.UsedRange.Rows.Count Local $iLastColumn = $oExcel.Activesheet.UsedRange.Columns.Count If ($iLastRow + $iLastColumn = 2) And $oExcel.Activesheet.Cells(1, 1).Value = "" Then ; empty result Local $avRET[1][2] = [[0, 0]] Return $avRET EndIf ; Parameter edits (continued) If $iStartRow > $iLastRow Then Return SetError(2, 0, 0) If $iStartColumn > $iLastColumn Then Return SetError(2, 1, 0) If $iStartRow + $iRowCnt - 1 > $iLastRow Then Return SetError(3, 0, 0) If $iStartColumn + $iColCnt - 1 > $iLastColumn Then Return SetError(3, 1, 0); Check for defaulted counts If $iRowCnt Then $iLastRow = $iStartRow + $iRowCnt - 1 Else $iRowCnt = $iLastRow - $iStartRow + 1 EndIf If $iColCnt Then $iLastColumn = $iStartColumn + $iColCnt - 1 Else $iColCnt = $iLastColumn - $iStartColumn + 1 EndIf ; Read data Local $aArray = $oExcel.ActiveSheet.Range($oExcel.Cells($iStartRow, $iStartColumn), $oExcel.Cells($iLastRow, $iLastColumn)).Value ; Handle single-cell sheet If Not IsArray($aArray) Then Local $avRET[2][2] = [[1, 1]] If $iColShift Then $avRET[1][0] = $aArray Else $avRET[1][1] = $aArray EndIf Return $avRET EndIf ; Insert Row-0 totals, convert Col/Row array (from Excel) to Row/Col, apply $iColShift Local $avRET[$iRowCnt + 1][$iColCnt + ($iColCnt = 1 Or $iColShift = 0)] = [[$iRowCnt, $iColCnt]] For $i = 1 To $iColCnt For $j = 1 To $iRowCnt $avRET[$j][$i - $iColShift] = $aArray[$i - 1][$j - 1] Next Next Return $avRET EndFunc ;==>_ExcelReadSheetToArray My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Maarten002 Posted October 2, 2012 Author Share Posted October 2, 2012 That sounds a lot faster! Problem is that i'm already using this in a script with over 1000 lines where i use this "object" to get all my info. If i have to rework all this, it would take some time and i don't think my boss would like to hear this . Stupif thing is that i didn't noticed it earlier because in the past days, i was using an excel sheet that did had a header (that wasn't important for the script) and so i didn't noticed the not-reading of the first row... But thanks for the responce, if nobody can tell me what i'm doing wrong, i'll rewrite the script and go with your approuch (or i just add an empty row at the beginning of the sheet...). Link to comment Share on other sites More sharing options...
water Posted October 2, 2012 Share Posted October 2, 2012 Could you set HDR=NO and give it a try? "Extended Properties=""Excel 12.0 xml;HDR=No;IMEX=1"";") My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Maarten002 Posted October 2, 2012 Author Share Posted October 2, 2012 Awesome, that did the trick! Thanks. Link to comment Share on other sites More sharing options...
water Posted October 2, 2012 Share Posted October 2, 2012 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki 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