TheDcoder

Trying to loop through every row in a Excel sheet

22 posts in this topic

#1 ·  Posted (edited)

Hello :)
I am relatively new to the world of Microsoft Office and the Excel UDF.

I am trying to loop through every row in a spreadsheet and get the text/values from each column in the given row... so far I have looked into the Help file for the Excel UDF and the wiki page for Excel UDF but I have no idea about how this is done :(... This is all I have in my script:

Global $oExcel = _Excel_Open(False, False, False, False, True)
Global Const $sSpreadsheet = @ScriptDir & '\data.xlsx'
Global $oSpreadsheet = _Excel_BookOpen($oExcel, $sSpreadsheet, True, False)

; ...

I am placing my bet on the _Excel_Range functions... especially _Excel_RangeRead. I don't know how $vRange works so I would be glad if someone can point me in the right direction :D. What I would ideally like is to get all of the contents of the spreadsheet (it's just a normal text one) in a 2D array.

Thanks in Advance!

Edited by TheDcoder
Add tags to the thread

AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Spoiler

My contributions to the AutoIt Community

Some messages & Apologizes:

If I hurt you, Please accept my apologies, I never (regardless of the situation) mean to hurt anybody!!!

Also, I am very busy with my project so I will appear in the last row of the online list, if you want to contact me: Email@TheDcoder.xyz

Or you can have a nice chat with me in freenode, I use the same nick on freenode too!

3fHNZJ.gif

PLEASE JOIN ##AutoIt AND HELP THE IRC AUTOIT COMMUNITY!

Share this post


Link to post
Share on other sites



#2 ·  Posted

You can use something like:

#include <Array.au3>
#include <Excel.au3>
Global $oExcel = _Excel_Open(False, False, False, False, True)
Global Const $sSpreadsheet = @ScriptDir & '\data.xlsx'
Global $oSpreadsheet = _Excel_BookOpen($oExcel, $sSpreadsheet, True, False)
Global $aSpreadsheet = _Excel_RangeRead($oSpreadsheet)
_ArrayDisplay($aSpreadsheet)

 

1 person likes this

Share this post


Link to post
Share on other sites

#3 ·  Posted

Wow! :o
It was so easy! :thumbsup:
Also, if possible, is there way to exclude blank rows in the _Excel_RangeRead function?


AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Spoiler

My contributions to the AutoIt Community

Some messages & Apologizes:

If I hurt you, Please accept my apologies, I never (regardless of the situation) mean to hurt anybody!!!

Also, I am very busy with my project so I will appear in the last row of the online list, if you want to contact me: Email@TheDcoder.xyz

Or you can have a nice chat with me in freenode, I use the same nick on freenode too!

3fHNZJ.gif

PLEASE JOIN ##AutoIt AND HELP THE IRC AUTOIT COMMUNITY!

Share this post


Link to post
Share on other sites

#4 ·  Posted

No unfortunately, well not that I've ever seen without manipulating the document first, it's probably easier just to loop through the array and remove the empty rows.

Share this post


Link to post
Share on other sites

#5 ·  Posted

I see... Okay, thanks for the help :)


AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Spoiler

My contributions to the AutoIt Community

Some messages & Apologizes:

If I hurt you, Please accept my apologies, I never (regardless of the situation) mean to hurt anybody!!!

Also, I am very busy with my project so I will appear in the last row of the online list, if you want to contact me: Email@TheDcoder.xyz

Or you can have a nice chat with me in freenode, I use the same nick on freenode too!

3fHNZJ.gif

PLEASE JOIN ##AutoIt AND HELP THE IRC AUTOIT COMMUNITY!

Share this post


Link to post
Share on other sites

#6 ·  Posted

I came upon this function a while ago if you want to have a premade way of removing blanks:

Func _ArrayRemoveBlanks(ByRef $arr) ;self explanatory
  $idx = 0
  For $i = 0 To UBound($arr) - 1
    If $arr[$i] <> "" Then
      $arr[$idx] = $arr[$i]
      $idx += 1
    EndIf
  Next
  ReDim $arr[$idx]
EndFunc

Just makes it super simple, I copy this into my scripts whenever I need to work with arrays.


UHJvZmVzc2lvbmFsIENvbXB1dGVyZXI=

Share this post


Link to post
Share on other sites

#7 ·  Posted

@anthonyjr2 That's a strange way to remove blanks from an (1D) array... I wonder why it rewrites a row with the same contents? ($arr[$idx] = $arr[$i])


AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Spoiler

My contributions to the AutoIt Community

Some messages & Apologizes:

If I hurt you, Please accept my apologies, I never (regardless of the situation) mean to hurt anybody!!!

Also, I am very busy with my project so I will appear in the last row of the online list, if you want to contact me: Email@TheDcoder.xyz

Or you can have a nice chat with me in freenode, I use the same nick on freenode too!

3fHNZJ.gif

PLEASE JOIN ##AutoIt AND HELP THE IRC AUTOIT COMMUNITY!

Share this post


Link to post
Share on other sites

#8 ·  Posted

To be fair I never actually checked to see how it worked, I got it from here a long time ago:

But from looking at it now, what it's doing is going through and checking to see if each element is blank. If it isn't blank, it adds it to a new array. That way in the end the array will only contain non-blank elements. So that line is just putting the nonblank elements into a new array.


UHJvZmVzc2lvbmFsIENvbXB1dGVyZXI=

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

5 minutes ago, anthonyjr2 said:

it adds it to a new array

I can only see one array and that is $arr ;)

5 minutes ago, anthonyjr2 said:

That way in the end the array will only contain non-blank elements.

No, what it's doing is using $idx to count all "non-blank" elements and and resizing/trimming the array using ReDim

Edited by TheDcoder

AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Spoiler

My contributions to the AutoIt Community

Some messages & Apologizes:

If I hurt you, Please accept my apologies, I never (regardless of the situation) mean to hurt anybody!!!

Also, I am very busy with my project so I will appear in the last row of the online list, if you want to contact me: Email@TheDcoder.xyz

Or you can have a nice chat with me in freenode, I use the same nick on freenode too!

3fHNZJ.gif

PLEASE JOIN ##AutoIt AND HELP THE IRC AUTOIT COMMUNITY!

Share this post


Link to post
Share on other sites

#10 ·  Posted

Oh, you're right there is only one array. Regardless it still basically does the same thing, just modifying the original array instead of creating a temporary one.


UHJvZmVzc2lvbmFsIENvbXB1dGVyZXI=

Share this post


Link to post
Share on other sites

#11 ·  Posted

@anthonyjr2 Your statement only makes it look smarter to be honest :D. There are several flaws... not to mention the obvious unnecessary rewriting of elements.... or that is what I thought until now. It actually moves all non-empty elements up, shifts all gaps to the bottom and then truncates the array. I have to admit, it's clever... but not very effective! Still does the job though, so I guess we can use it when feeling lazy :).


AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Spoiler

My contributions to the AutoIt Community

Some messages & Apologizes:

If I hurt you, Please accept my apologies, I never (regardless of the situation) mean to hurt anybody!!!

Also, I am very busy with my project so I will appear in the last row of the online list, if you want to contact me: Email@TheDcoder.xyz

Or you can have a nice chat with me in freenode, I use the same nick on freenode too!

3fHNZJ.gif

PLEASE JOIN ##AutoIt AND HELP THE IRC AUTOIT COMMUNITY!

Share this post


Link to post
Share on other sites

#12 ·  Posted

I suggest to loop through the array; write the line numbers of empty rows to a string so you get a valid range for _ArrayDelete; then call _ArrayDelete a single time passing the mentioned string.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#13 ·  Posted

@water That is what I had in mind :).

I am urged to create another function which can remove all blank elements with the most efficient way to do it... but if I do that, I will be pushing my personal projects again... they have crossed the deadline a long time ago :(


AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Spoiler

My contributions to the AutoIt Community

Some messages & Apologizes:

If I hurt you, Please accept my apologies, I never (regardless of the situation) mean to hurt anybody!!!

Also, I am very busy with my project so I will appear in the last row of the online list, if you want to contact me: Email@TheDcoder.xyz

Or you can have a nice chat with me in freenode, I use the same nick on freenode too!

3fHNZJ.gif

PLEASE JOIN ##AutoIt AND HELP THE IRC AUTOIT COMMUNITY!

Share this post


Link to post
Share on other sites

#14 ·  Posted

I admit I didn't have performance in mind when I was looking for a solution....usually when I am throwing a project together I use the first working answer I come across :sweating:

 

P.S. This is my 200th post :D

1 person likes this

UHJvZmVzc2lvbmFsIENvbXB1dGVyZXI=

Share this post


Link to post
Share on other sites

#15 ·  Posted

Congrats :D

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#16 ·  Posted

Yep, I can understand :).

P.S Congratulations on the 200th post

1 person likes this

AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Spoiler

My contributions to the AutoIt Community

Some messages & Apologizes:

If I hurt you, Please accept my apologies, I never (regardless of the situation) mean to hurt anybody!!!

Also, I am very busy with my project so I will appear in the last row of the online list, if you want to contact me: Email@TheDcoder.xyz

Or you can have a nice chat with me in freenode, I use the same nick on freenode too!

3fHNZJ.gif

PLEASE JOIN ##AutoIt AND HELP THE IRC AUTOIT COMMUNITY!

Share this post


Link to post
Share on other sites

#17 ·  Posted

I think it depends on what the end result you want for example do you only want to remove blank rows or blank cells?  For example would you want to just remove Row 3 or all blanks and move them up?

|   A   |   B   |
|   A1  |   A1  |
|       |   A2  |
|       |       |
|   A4  |       |
|   A5  |   A5  |

Anyway I figured out how to do it within Excel and then export it to an Array, just need to comment/uncomment the Row or Cell section in the code to see both examples.

#include <Array.au3>
#include <Excel.au3>
Global Const $xlByRows = 1
Global Const $xlByColumns = 2
Global Const $xlPrevious = 2
Global Const $xlUp = -4162
Global $oExcel = _Excel_Open(False, False, False, False, True)
Global $sWorkbook = @ScriptDir & '\Filename.xlsx'
Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, True, False)

;~ #### Begin Remove All Blank Rows #### ~;
    Global $oEntireRow, $iLastRow = $oWorkbook.ActiveSheet.Cells.Find('*', $oWorkbook.ActiveSheet.Cells(1, 1), Default, Default, $xlByRows, $xlPrevious).Row
    For $i = $iLastRow To 1 Step - 1
        $oEntireRow = $oWorkbook.ActiveSheet.Cells($i, 1).EntireRow
        If $oExcel.WorksheetFunction.CountA($oEntireRow) = 0 Then $oEntireRow.Delete
    Next
;~ #### Begin Remove All Blank Rows #### ~;

;~ #### Begin Remove All Blank Cells #### ~;
;~  $oWorkbook.ActiveSheet.UsedRange.Cells.SpecialCells($xlCellTypeBlanks).Delete($xlUp)
;~ #### End Remove All Blank Cells #### ~;

Global $aWorkbook = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.UsedRange)
_Excel_BookClose($oWorkbook, False)
_Excel_Close($oExcel)

 _ArrayDisplay($aWorkbook)

 

Share this post


Link to post
Share on other sites

#18 ·  Posted

@Subz It works! I have no idea how... but it does work :D. Although I have noticed that it can take a while (around 5 seconds for me) for the code to filter all blank rows :)


AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Spoiler

My contributions to the AutoIt Community

Some messages & Apologizes:

If I hurt you, Please accept my apologies, I never (regardless of the situation) mean to hurt anybody!!!

Also, I am very busy with my project so I will appear in the last row of the online list, if you want to contact me: Email@TheDcoder.xyz

Or you can have a nice chat with me in freenode, I use the same nick on freenode too!

3fHNZJ.gif

PLEASE JOIN ##AutoIt AND HELP THE IRC AUTOIT COMMUNITY!

Share this post


Link to post
Share on other sites

#19 ·  Posted

instead of the loop you can use the specials as well (works especially well if you have to only check one column, should be similar for checking the whole row).  Would look something like:

#include <Array.au3>
#include <Excel.au3>
Global $oExcel = _Excel_Open(False, False, False, False, True)
Global Const $sSpreadsheet = @ScriptDir & '\filename.xlsx'
Global $oSpreadsheet = _Excel_BookOpen($oExcel, $sSpreadsheet, True, True)


$oExcel.activesheet.Range("A1:A10").SpecialCells($xlCellTypeBlanks).EntireRow.Delete = True


Global $aSpreadsheet = _Excel_RangeRead($oSpreadsheet , Default , Default , 1)
_Excel_BookClose($oSpreadsheet, FALSE)
_Excel_Close($oExcel , FALSE , TRUE)
_ArrayDisplay($aSpreadsheet)

 


,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


Link to post
Share on other sites

#20 ·  Posted

@iamtheky That would work if you had a key column (with blank rows to show new range, see example below), but wouldn't work with the example I posted above, which is why I used the loop and the CountA function against each row (for deleting rows anyway).  Which is why it's slow to get a result,  As mentioned above it really depends on the workbook layout and also how you want the information returned.

|   A   |   B   |
|   A1  |   B1  |
|   A2  |   B2  |
|       |       |
|   A4  |   B4  |
|   A5  |       |
|   A6  |   B6  |
|       |       |
|   A8  |   B8  |

 

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now

  • Similar Content

    • VeryGut
      By VeryGut
      I'm trying to insert the following formula in cell A2 using my script:
      =if(A1=""; "YES"; "NO")
      To my understanding, the line of code should be similar to this:
      _Excel_RangeWrite($MasterFile, Default, "=if(A1=""; "YES"; "NO")", "A2")
      However, it does not work, probably due to the multiple quotation marks that confuse the script :C
      How do I avoid this problem?
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have a question about Excel, i have to create several charts one below the other dynamically.
      I have thought to use:
       
      $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count  
      And then to use it in this way:
      $Graph_position = "=Test1!A"&$iRowCount+2&":K"&$iRowCount+24 But it doesn't work with charts.
      Does anyone have a suggestion?
       
    • LoneWolf_2106
      By LoneWolf_2106
      Hi all,
      i have an empty csv file, i have a non formatted text file.
      What do i want to do?
      I want to automate the process "get external data" in Excel, i want to import the data from the text file and basically create a csv file with a specific character encoding.
      Is it possible with AutoIT?
       
    • breakbadsp
      By breakbadsp
      I  want to create a excel file from my script if it does not exist.
      _ExcelBookOpen throws error=2 if file does not exist, after this error i want to create new file at this point.
      can i use _FileCreate()?
      _Logger($sLogPath, "{INFO}------: Opening Excel File: " & $sExcelPath& "") While 1 Local $oExcelTestResult = _ExcelBookOpen($sExcelPath) If @error = 2 Then If not _FileCreate($sResExcelPath) Then MsgBox(0, "Error", "Error In Opening REsult Excel File: Error: " & String(@error)) _Logger($sLogPath, "{ERROR}------: Result Excel File does not exist.. tried to create new but :ERROR : " & String(@error) & "") ExitLoop Else _Logger($sLogPath, "{INFO}------: Result Excel File does not exist.. **Created New**: ") EndIf Else ExitLoop EndIf WEnd  
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to store an entire row of a Excel workbook into an array.  The row index is stored in a variable.
      How can i do it?
      Thanks in advance for your support.