Getting data from excel based on first column

[Taxyo]

Hi,

 

I am pretty new to programming, and am in the middle of "starting a project without 100% knowing if it can be done and then researching until I solve it". My code will never be "the cleanest" but I try to make it readable and do what I want. 

 

My problem is the following:

I will each day have an excel-sheet, which will contain new information every time, but structured in the same way. I want to automate the way I handle the data in the sheet.

As of now the data can look something like this:

A  -  First copy these columns
A  -  First copy these columns
A  -  First copy these columns
A  -  First copy these columns
A  -  Until I hit B
B  -  Then copy B columns
B  -  Until I hit C
C  - ETC
C
C
D

What I want to do is first find "A" which will always be in Column A, and then copy all the data to the right of A until I hit "B". 

What I have managed so far is through Excel UDF create and Automatically sort the data each time I export the raw data, to get it sorted by column A. 

 

I belive the next step is to export the data into an Array, which I have managed to do, but I have no idea where to begin in how to make the script "smart enough" as to find all rows which has "A" in column "A", until it hits the next value "B".

 

I hope I managed to make myself clear. Otherwise please ask me to specify. 

 

My current code is basically just manipulating the sheet to a state where I can start reading the data, now I want the script to also pull the data in the right way which is the trickier part. 

 

Spoiler

While 1
   sleep(50)
WEnd

Func Hotkey1()
    _Excel_RangeSort($oWorkbook, Default, Default, "A1:Z300", Default, Default, $xlYes, Default, Default)
        If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeSort Example 3", "Error sorting data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeSort Example 3", "Data successfully sorted in Document")
        sleep(100)
    _Excel_RangeInsert($oWorkbook.activesheet, "B:B")

    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Shipping Date", "B1")

    Global $aResult = _Excel_rangeRead($oWorkbook, default, "A1:H300")
        _ArrayDisplay($aResult, "Excel UDF: _Excel_RangeRead Example 3 - Formulas in column A")



Endfunc

Func Hotkey2()

   Local $aExtract = _ArrayExtract($aResult, 1, 2, 2, 3)
   _ArrayDisplay($aExtract, "Row 1-2 cols 2-3")


EndFunc

 

 

[water]

If I had to code such a program my first question would be: About how many rows and columns do we talk?

Edited October 27, 2019 by water

[Nine]

57 minutes ago, Taxyo said:

My current code is basically just manipulating the sheet to a state where I can start reading the data, now I want the script to also pull the data in the right way which is the trickier part. 

How about showing us your code and giving us an example of the .xls file ?

[Taxyo]

Alright,

 

Here is an example of the .xls file, I had to redact some data as it is a thing for work. So I hope it is possible to work with anonymized data. 

 

Regarding rows: this will always be different each time I export the XLS from our system. But I will always want the same Columns exported/Copied one section at the time. 

Test.xlsx

 

I have in here highlighted each "set of data" that I want the script to find, based on Column A. Column A will always contain groups of data which the script will sort Ascending and then add the column "Shipping date". 

 

The script itself doesn't have to be 100% automated as I would need it to Pause after each subset of copied data as I have to take action on that before it copies the next rows/Columns of data. 

 

The script sofar: I've been toying around testing things so a couple of things might be redundant. But so far it does what I need up until the "extracting data" part. 

expandcollapse popup

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
HotKeySet("{NUMPAD1}", Hotkey1)
HotKeySet("{NUMPAD2}", Hotkey2)
HotKeySet("{ESC}", Terminate)



Global $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeSort Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $oWorkbook = _Excel_BookOpen($oExcel, "@ScriptDir &\'test.xlsx')
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeSort Example", "Error." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf



While 1
   sleep(50)
WEnd

Func Hotkey1()
_Excel_RangeSort($oWorkbook, Default, Default, "A1:Z300", Default, Default, $xlYes, Default, Default)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeSort Example 3", "Error sorting data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeSort Example 3", "Data successfully sorted in Document")
sleep(100)
_Excel_RangeInsert($oWorkbook.activesheet, "B:B")

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Shipping Date", "B1")

Global $aResult = _Excel_rangeRead($oWorkbook, default, "A1:H300")
_ArrayDisplay($aResult, "Excel UDF: _Excel_RangeRead Example 3 - Formulas in column A")



Endfunc

;~ Func Hotkey2()

;~    Local $aExtract = _ArrayExtract($aResult, 1, 2, 2, 3)
;~    _ArrayDisplay($aExtract, "Row 1-2 cols 2-3")


;~ EndFunc

Func Terminate()
   Exit 1
EndFunc

 

 

[water]

Based on your data the following script grabs all data from Excel and writes it to an array. The array then gets sorted (ignoring the header line).
The script loops through this array and counts the number of rows with the same content in column A. If the content changes or at the end of the table it writes the number of rows to the console.

#include <Excel.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Local\test.xlsx")
Global $aUsedRange = _Excel_RangeRead($oWorkbook)
_Excel_BookClose($oWorkbook)
_Excel_Close($oExcel)
_ArraySort($aUsedRange, 0, 1) ; Sort ascending on column 0 (first column), ignores header line
_ArrayDisplay($aUsedRange)
Global $sPrevious = "", $iRows = 0
For $i = 1 To UBound($aUsedRange) - 1
    ; Column A has changed. Add processing of the previous block here
    If $sPrevious <> $aUsedRange[$i][0] And $sPrevious <> "" Then
        ConsoleWrite("Column A changed from '" & $sPrevious & "' to '" & $aUsedRange[$i][0] & "'. " & $iRows & " rows processed." & @CRLF)
        $iRows = 0
    Endif
    $sPrevious = $aUsedRange[$i][0]
    $iRows = $iRows + 1
Next
; End of table reached. Add processing of the last block here
ConsoleWrite("End of table reached. " & $iRows & " rows processed." & @CRLF)

What comes next depends on what you want to do with the "block" of data when column A changes.

 

[Taxyo]

51 minutes ago, water said:

What comes next depends on what you want to do with the "block" of data when column A changes.

 

Wow thank you, every time I come to these forums I realize how little I know and the potential of Auto-it in general. 

 

The next step would be something like this:

Once the first block of data is identified, I want it to send the "block" of data to the clip-board, and then if possible wait to process the next block of data until either a press of a hot-key. Or perhaps a message prompt.

How would I get it to send the data, based on what it found when looping through Column A, and mark column A through H, to clipboard and then "wait" until I ask it to process the next batch of data? 

 

Essentially I need to take manual action of each block of data until it can process the next set of data. (this step could probably be automated in the future but I need to have a proof of concept before I dare to automate this as this involves mailing an external party).

 

Thanks again - this is super helpful and I feel that diving through your code, with my current understanding, gives me a bit of a "next level" insight of what is doable how it is done. 

 

[water]

How should the data be separated when copied to the clipboard?
Tab character (@TAB) to separate the columns of each row, Carriage-Return-Line-Feed (@CRLF) to separate rows?

[water]

I have used @Tab and @CRLF in this example:

expandcollapse popup

#include <Excel.au3>

Global $sPrevious = "", $iRowCount = 0, $sClipData = "", $iStartColumn = 0, $iEndColumn = 7
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Local\test.xlsx")
Global $aUsedRange = _Excel_RangeRead($oWorkbook)
_Excel_BookClose($oWorkbook)
_Excel_Close($oExcel)
_ArraySort($aUsedRange, 0, 1) ; Sort ascending on column 0 (first column), ignores header line
_ArrayDisplay($aUsedRange)
For $iRow = 1 To UBound($aUsedRange, 1) - 1
    ; Column A has changed. Add processing of the previous block here
    If $sPrevious <> $aUsedRange[$iRow][0] And $sPrevious <> "" Then
        ClipPut($sClipData)
        If MsgBox($MB_OKCANCEL, "Test-Script", "Column A changed from '" & $sPrevious & "' to '" & $aUsedRange[$iRow][0] & "'. " & $iRowCount & " rows processed." & @CRLF & _
            "Data has been copied to the clipboard. Please press 'OK' to continue or 'Cancel' to end the script.") = $IDCANCEL Then Exit
        $iRowCount = 0
        $sClipData = ""
    EndIf
    $sPrevious = $aUsedRange[$iRow][0]
    ; Write all columns of the current row to a variable
    For $iColumn = $iStartColumn To $iEndColumn ; If you want to process all used columns use: UBound($aUsedRange, 2) - 1
        If $iColumn = $iStartColumn Then
            If $iRowCount = 0 Then ; First row of a new block
                $sClipData = $aUsedRange[$iRow][$iColumn]
            Else
                $sClipData = $sClipData & @CRLF & $aUsedRange[$iRow][$iColumn]
            EndIf
        Else
            $sClipData = $sClipData & @TAB & $aUsedRange[$iRow][$iColumn]
        EndIf
    Next
    $iRowCount = $iRowCount + 1
Next
; End of table reached. Add processing of the last block here
ClipPut($sClipData)
MsgBox($MB_OKCANCEL, "Test-Script", "End of table reached. " & $iRowCount & " rows processed." & @CRLF & _
"Data has been copied to the clipboard.")

 

[Taxyo]

2 hours ago, water said:

I have used @Tab and @CRLF in this example:

expandcollapse popup

#include <Excel.au3>

Global $sPrevious = "", $iRowCount = 0, $sClipData = "", $iStartColumn = 0, $iEndColumn = 7
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Local\test.xlsx")
Global $aUsedRange = _Excel_RangeRead($oWorkbook)
_Excel_BookClose($oWorkbook)
_Excel_Close($oExcel)
_ArraySort($aUsedRange, 0, 1) ; Sort ascending on column 0 (first column), ignores header line
_ArrayDisplay($aUsedRange)
For $iRow = 1 To UBound($aUsedRange, 1) - 1
    ; Column A has changed. Add processing of the previous block here
    If $sPrevious <> $aUsedRange[$iRow][0] And $sPrevious <> "" Then
        ClipPut($sClipData)
        If MsgBox($MB_OKCANCEL, "Test-Script", "Column A changed from '" & $sPrevious & "' to '" & $aUsedRange[$iRow][0] & "'. " & $iRowCount & " rows processed." & @CRLF & _
            "Data has been copied to the clipboard. Please press 'OK' to continue or 'Cancel' to end the script.") = $IDCANCEL Then Exit
        $iRowCount = 0
        $sClipData = ""
    EndIf
    $sPrevious = $aUsedRange[$iRow][0]
    ; Write all columns of the current row to a variable
    For $iColumn = $iStartColumn To $iEndColumn ; If you want to process all used columns use: UBound($aUsedRange, 2) - 1
        If $iColumn = $iStartColumn Then
            If $iRowCount = 0 Then ; First row of a new block
                $sClipData = $aUsedRange[$iRow][$iColumn]
            Else
                $sClipData = $sClipData & @CRLF & $aUsedRange[$iRow][$iColumn]
            EndIf
        Else
            $sClipData = $sClipData & @TAB & $aUsedRange[$iRow][$iColumn]
        EndIf
    Next
    $iRowCount = $iRowCount + 1
Next
; End of table reached. Add processing of the last block here
ClipPut($sClipData)
MsgBox($MB_OKCANCEL, "Test-Script", "End of table reached. " & $iRowCount & " rows processed." & @CRLF & _
"Data has been copied to the clipboard.")

 

Hi,

 

I got held up at work - I would say that @TAB would probably be best to separate the columns. 

 

I tried your example but it gets held up at various points.

 

First point is row 12:  "==> Unable to parse line.:
?

 

 

And if I comment row 12 manually I get the same error on row 26: 
Unable to parse line: Else

Is there anything I need to manually do to get it working? 

Thank you again for your patience assisting me  

 

[FrancescoDiMuro]

14 minutes ago, Taxyo said:

First point is row 12:  "==> Unable to parse line.:
?

Delete and write (not copy-paste) again the row, since there's an hidden character which is visible using Notepad++ or any other editor which lets you see hidden characters

[water]

No invalid characters found using SciTE or Notepad++ on this line.
I have added the example as a whole:

Test.au3

[Taxyo]

20 hours ago, water said:

No invalid characters found using SciTE or Notepad++ on this line.
I have added the example as a whole:

Test.au3 1.75 kB · 2 downloads

Hi, I just managed to get the time to test this example - and it works wonders. 

 

Something that hit me last night, is if - instead of using @Ctrlf or @Tab as a separator. Is it possible to export the data as a table? Or would you then perhaps manipulate the data which is in the clipboard somehow to format it as a table? 

 

If not possible I will find a workaround somehow, but it would be neat and something I didn't think of beforehand. Which function would I look for to try to get something like that to work? 

Thanks! 

[water]

Please define "table". How would you seprate lines and columns of the table?
If possible please post an example.

[Taxyo]

Right, I suppose it would be easiest to describe the way this will work in the end now that I've had the chance to think deeper about it. Sorry that it's a bit "all over the place" as I am trying to get a grip on what is doable and how I'd like it to end out as there might be a more elegant solution 

 

Basically I would like the copied data to look like something which is copied from an Excel-file, with each "@tab" delimiter ending up in each column of a table. 

 

I.E: When the first row is processed by the script and copied, I'd like the result to look as if I copied the columns from Excel, as in the end I will take the reply and import it to excel again to get the right format to import it to our system. 

A	(blank, this is the field which will be filled by the recipient)	Blank	X	Y	Z	100,00	100,00
A	(blank)	Blank	X	Y	Z	400,00	400,00

 

Now when I paste it it comes as one string, and when that string is pasted to excel it only fills one column. So I guess the solution could either be to use a comma-delimitor and export it as a .csv file which I then import to excel to get desired result? But I am wondering if there might be a more elegant solution already when grabbing the Clipboard data instead of re-manipulating the data afterwards to get the desired format in excel again. 

I hope I made sense? 

[water]

You have to differentiate between the content of a cell and the format.
Assume the format of column "B" has been set wider the content migh have changed from:

(blank, this is
the field which
will be filled by
the recipient)

to

(blank, this is the field
which will be filled by
the recipient)

We do not know enough about the goal you want to reach, but my next question would be:
Why wouldn't you pass the Excel workbook to the user and let him fill in all needed data?

Edited October 29, 2019 by water

[Taxyo]

Hi, 

The reason why the recipient can't get the entire workbook is because in the example we had in Column A, the letter A stands for one Vendor which is to receive all the data about column A -> H

Then letter B is to another Vendor who will recieve the data from A->H (just like the way the  copying works now in the script you so kindly helped me with).

 

In this case what we're exporting and sending is procurement data where we need to ask them for an expected delivery date on our open order lines against them. So we differentiate  the vendor by column A, then send all our open lines, including a blank column which is to be filled with a date. 

 

And this is where I noticed that the current finished result where we get the data as a string isn't compatible with the way we then import the updated table to our system again. Which would be solved if it was possible to export the copied string as an "excel table" as when it comes back with a date in the empty column this will then correspond correctly with our import methods. 

So by looking at the current array first rows the result is now in a string: 

A        Blank    X    Y    Z    400    400
A        Blank    X    Y    Z    100    100

In between "A and "Blank" is the empty column B which currently contains no data, so if I were to delimit this in the way I would want a table I would - I guess type it something like this:

A, B, C, D, E, F, G, H  ; <-- these are the headers which are currently ignored by the script the name is irrelevant as of now
A, "", Blank, X, Y, Z, 400, 400
A, "", Blank, X, Y, Z, 100, 100

So the end result I would in this case get from the first data processing would be a table I could send to the vendor "A" where I basically ask them to fill in the empty square:
(again the header names A-H being irrelevant in my example but just for clarification)

A	B	C	D	E	F	G	H
A	""	Blank	X	Y	Z	100	100
A	""	Blank	X	Y	Z	400	400

 

So everything the script is doing currently is still great - as in it manages to differentiate the data based on vendor and export the data I need to ask for; but if I could format the data accordingly I would save a last step of manual processing and be all set for a process which is as automated as I am currently comfortable with. 

 

Does it make sense or am I just rambling? 

[water]

I understand that you have an Excel workbook with all vendors as input to your script, each vendor should only get his part of this workbook, then fill in his delivery date and return it to you. You then want to take all the returned data from the vendors and write all data back to the input workbook.
Correct?

So why not split the input workbook into pieces and create one workbook for each vendor holding only his data. He then fills in his delivery date and returns the workbook.
As the vendor and delivery item are unique (?) it shouldn't be too hard to fill in the vendors data to your Excel workbook.

If needed you could protect parts of the workbook so the vendor is only able to fill in the delivery date and not modify/delete anything else.

[Taxyo]

Hi, yes your understanding is correct.

The reason we've not split the workbooks has been twofold previously.

1. Splitting the file manually has been more work than just manually selecting the data and pasting it in the E-mail for the vendor to fill in

2. For very small examples, as when Vendor A only has 2 open lines, saving a file and attaching it to our mail-ticketing system takes more time than again just manually selecting the data and pasting it in the mail.

Edit: 3. In some cases the list also contains over 100+ unique vendors which would result in 100+ separate  workbooks per each time we run the list (which is a few times per week). 

 

But for larger entrypoints, we've for instance had occurances where a vendor has over 300 open lines; then yes - we have attached that as a workbook for them to fill out. 

So I am trying to find the in-between solution as to save time in extracting each set of data from the list, but then have it formatted as close as possible to the type of data we need to import it to our system without editing it further manually.

 

So by your question then yes, if each vendor got their own workbook would for sure save time in the latter "import" step, but in some cases take longer time in the "e-mailing step". 

 

Unless splitting a workbook based on our previous stipulations is easily done in AutoIt - which from what I've seen could possibly be done? 

Edited October 29, 2019 by Taxyo

[water]

Will post an example quite soon 😀

[Taxyo]

@water : I gave this some more thought during the night. And yeah you're absolutely right. Being able to split the workbook for each vendor and attach it per mail will definitely be the better solution in the long run - as that is a solution which will work no matter if there are 2 lines on that vendor, or 200 lines on that vendor. And we will instantly get the information back in the correct format to be able to import each file right away. 

 

So yeah, I guess I didn't even consider that solution and tunneled in on the initial thought process. Thanks for the great feedback and ideas.