Jump to content
Sign in to follow this  
drbyte

Match values in Array with values in Excel

Recommended Posts

drbyte

I fill an array with values from pdf file's found in a directory.

Now i need to find de corrsponding values in the array and in the excel sheet, and place "X" in the
cell at the left of the corresponding value.

How to do this?

See below the code i made so far to fill the 2D array with the value's

(a lot of code plagarized from Kylomas and soonyee91 :shifty:  )

; ------------------------------------------------------------------------------------
; create pdf files in @scriptdir \test\ for testing
; ------------------------------------------------------------------------------------
Local $result[16]

$result[0] = "Could be any name - ABCD001.pdf"
$result[1] = "Could be any name - ABCD003.pdf"
$result[2] = "Could be any name - ABCD005.pdf"
$result[3] = "Could be any name - ABCD007.pdf"
$result[4] = "Could be any name - ABCD009.pdf"
$result[5] = "material-1a_124567.pdf"
$result[6] = "material-1b_12345_589752467.pdf"
$result[7] = "material-1c_12345_nesting.pdf"
$result[8] = "material-1d_us_04030-2010-09-03.pdf"
$result[9] = "material-1e_us_Certificaat.pdf"
$result[10] = "material-1f_z35_SL1020_01.pdf"
$result[11] = "material-1g_harden_W1305050.pdf"
$result[12] = "material-1h_veredelen_SL1025_02.pdf"
$result[13] = "material-1k_painting.pdf"
$result[14] = "material-1i_ndt_RM0199.pdf"
$result[15] = "material-1j_ndt_Certificaat.pdf"


For $1 = 0 To UBound($result) - 1
    FileOpen(@ScriptDir & '\test\' & $result[$1], 16)
Next
; end creation pdf file's

; list all .pdf type files from a specific directory
$atemp_Files = _FileListToArray(@ScriptDir & '\test\', '*.pdf', 1)
_ArrayDisplay($atemp_Files, "Directory loaded")

; Remove all file names with "material-" in the pdf file
For $i = UBound($atemp_Files) - 1 To 0 Step -1
    If StringInStr($atemp_Files[$i], "material-", 0) Then
        _ArrayDelete($atemp_Files, $i)
    EndIf
Next
_ArrayDisplay($atemp_Files, "Material pdf's removed")

; create final result array with 2 dimensions
Local $aFiles[UBound($atemp_Files)][2]

; populate 1ST dimension of final result array with complete file name (not path)
For $1 = 0 To UBound($atemp_Files) - 1
    $aFiles[$1][0] = $atemp_Files[$1]
Next

; populate 2ND dimension of final result array with stripped out file name
Local $aTmp
For $1 = 1 To UBound($aFiles) - 1
    $aTmp = StringRegExp($aFiles[$1][0], '[a-zA-Z]{1,4}[0-9]{1,3}', 3)
    If IsArray($aTmp) Then $aFiles[$1][1] = $aTmp[0]
Next
_ArrayDisplay($aFiles, "2nd Array filled")
_ArrayDelete($aFiles, 0)

; ------------------------------------------------------------------------------------
; iterate array looking for element with "paint" in the name
; ------------------------------------------------------------------------------------
;~ For $1 = 0 To UBound($aFiles) - 1
;~  If StringInStr($aFiles[$1][1], 'ndt') > 0 Then
;~      ConsoleWrite('Found "ndt" in element ' & $1 & ' of array' & @LF)
;~  EndIf
;~ Next

post-80136-0-09091400-1377551238_thumb.j

Thnx in advance!

Edited by drbyte

Share this post


Link to post
Share on other sites
water

To proces Excel I would suggest to have a look at the Excel UDF that comes with AutoIt.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
JCEF

I do not know if I have this right ...
See if this is what you want ...

#include <File.au3>
#include <Array.au3>
#include <Excel.au3>

; ------------------------------------------------------------------------------------
; create pdf files in @scriptdir \test\ for testing
; ------------------------------------------------------------------------------------
Local $result[16]

$result[0] = "Could be any name - ABCD001.pdf"
$result[1] = "Could be any name - ABCD003.pdf"
$result[2] = "Could be any name - ABCD005.pdf"
$result[3] = "Could be any name - ABCD007.pdf"
$result[4] = "Could be any name - ABCD009.pdf"
$result[5] = "material-1a_124567.pdf"
$result[6] = "material-1b_12345_589752467.pdf"
$result[7] = "material-1c_12345_nesting.pdf"
$result[8] = "material-1d_us_04030-2010-09-03.pdf"
$result[9] = "material-1e_us_Certificaat.pdf"
$result[10] = "material-1f_z35_SL1020_01.pdf"
$result[11] = "material-1g_harden_W1305050.pdf"
$result[12] = "material-1h_veredelen_SL1025_02.pdf"
$result[13] = "material-1k_painting.pdf"
$result[14] = "material-1i_ndt_RM0199.pdf"
$result[15] = "material-1j_ndt_Certificaat.pdf"


For $1 = 0 To UBound($result) - 1
    FileOpen(@ScriptDir & '\test\' & $result[$1], 16)
Next
; end creation pdf file's

; list all .pdf type files from a specific directory
$atemp_Files = _FileListToArray(@ScriptDir & '\test\', '*.pdf', 1)
_ArrayDisplay($atemp_Files, "Directory loaded")

; Remove all file names with "material-" in the pdf file
For $i = UBound($atemp_Files) - 1 To 0 Step -1
    If StringInStr($atemp_Files[$i], "material-", 0) Then
        _ArrayDelete($atemp_Files, $i)
    EndIf
Next
_ArrayDisplay($atemp_Files, "Material pdf's removed")

; create final result array with 2 dimensions
Local $aFiles[UBound($atemp_Files)][2]

; populate 1ST dimension of final result array with complete file name (not path)
For $1 = 0 To UBound($atemp_Files) - 1
    $aFiles[$1][0] = $atemp_Files[$1]
Next

; populate 2ND dimension of final result array with stripped out file name
Local $aTmp
For $1 = 1 To UBound($aFiles) - 1
    $aTmp = StringRegExp($aFiles[$1][0], '[a-zA-Z]{1,4}[0-9]{1,3}', 3)
    If IsArray($aTmp) Then $aFiles[$1][1] = $aTmp[0]
Next
_ArrayDisplay($aFiles, "2nd Array filled")




; ------------------------------------------------------------------------------------
; iterate array looking for element with "paint" in the name
; ------------------------------------------------------------------------------------
$oExcel = _ExcelBookOpen(@ScriptDir & "\test\FileExcel.xlsx", 0)

For $1 = 1 To UBound($aFiles) - 1
    For $i = 2 To 21
        If $aFiles[$1][1] = $oExcel.Sheets("Folha1").Range("A" & $i).Value Then
            $oExcel.Sheets("Folha1").Range("B" & $i).Value = StringUpper("x")
        EndIf
    Next
Next

_ExcelBookClose($oExcel)
_ArrayDelete($aFiles, 0)

MsgBox(0, "", "Concluído", 2)
Exit
  • Like 1

Share this post


Link to post
Share on other sites
soonyee91

drbyte, 

The concept of the solution for your problem is the same like what we discuss before. Use _arraysearch to check whether there is a match. If there is mark X on the corresponding column.

You can refer to my code below:

#include <File.au3>
#include <Array.au3>
#include <Excel.au3>

; ------------------------------------------------------------------------------------
; create pdf files in @scriptdir \test\ for testing
; ------------------------------------------------------------------------------------
Local $result[16]

$result[0] = "Could be any name - ABCD001.pdf"
$result[1] = "Could be any name - ABCD003.pdf"
$result[2] = "Could be any name - ABCD005.pdf"
$result[3] = "Could be any name - ABCD007.pdf"
$result[4] = "Could be any name - ABCD009.pdf"
$result[5] = "material-1a_124567.pdf"
$result[6] = "material-1b_12345_589752467.pdf"
$result[7] = "material-1c_12345_nesting.pdf"
$result[8] = "material-1d_us_04030-2010-09-03.pdf"
$result[9] = "material-1e_us_Certificaat.pdf"
$result[10] = "material-1f_z35_SL1020_01.pdf"
$result[11] = "material-1g_harden_W1305050.pdf"
$result[12] = "material-1h_veredelen_SL1025_02.pdf"
$result[13] = "material-1k_painting.pdf"
$result[14] = "material-1i_ndt_RM0199.pdf"
$result[15] = "material-1j_ndt_Certificaat.pdf"


For $1 = 0 To UBound($result) - 1
    FileOpen(@ScriptDir & '\test\' & $result[$1], 10)
Next
; end creation pdf file's

; list all .pdf type files from a specific directory
$atemp_Files = _FileListToArray(@ScriptDir & '\test\', '*.pdf', 1)
_ArrayDisplay($atemp_Files, "Directory loaded")

; Remove all file names with "material-" in the pdf file
For $i = UBound($atemp_Files) - 1 To 0 Step -1
    If StringInStr($atemp_Files[$i], "material-", 0) Then
        _ArrayDelete($atemp_Files, $i)
    EndIf
Next
_ArrayDisplay($atemp_Files, "Material pdf's removed")

; create final result array with 2 dimensions
Local $aFiles[UBound($atemp_Files)][2]

; populate 1ST dimension of final result array with complete file name (not path)
For $1 = 0 To UBound($atemp_Files) - 1
    $aFiles[$1][0] = $atemp_Files[$1]
Next

; populate 2ND dimension of final result array with stripped out file name
Local $aTmp
For $1 = 1 To UBound($aFiles) - 1
    $aTmp = StringRegExp($aFiles[$1][0], '[a-zA-Z]{1,4}[0-9]{1,3}', 3)
    If IsArray($aTmp) Then $aFiles[$1][1] = $aTmp[0]
Next
_ArrayDisplay($aFiles, "2nd Array filled")
_ArrayDelete($aFiles, 0)



;this is just to let the workbook looks like your snapshot example
Local $oExcel=_ExcelBookNew()
_ExcelWriteCell($oExcel,"Number",1,1)
_ExcelWriteCell($oExcel,"Report",1,2)
_ExcelWriteCell($oExcel,"ABCD001",2,1)
$oExcel.Range("A2").Select
 $oExcel.Selection.AutoFill($oExcel.Range("A2:A21"), 0)

 ; this is the code for matching with the column A with the array you got.
For $i=0 to 19; based on the example you give: ABCD001 ~ ABCD020
    if _arraysearch($aFiles,$oExcel.Cells($i+2,1).value,0,0,1,2,1,1) <>-1 Then

        _ExcelWriteCell($oExcel,"X",$i+2,2)
    EndIf
Next
Edited by soonyee91
  • Like 1

Share this post


Link to post
Share on other sites
drbyte

@soonyee91

Perfect solution!

I'am learning more and more on how to handle this.

Thnx a lot.

Share this post


Link to post
Share on other sites
drbyte

@JCEF

I'am also testing your code at the moment.

Wat does "$oExcel.Sheets("Folha1")" mean is this the name of the sheet name?

Did some experimenting it is indeed the name of the worksheet.

There is a small mistake in your code, corrected code below.

For $1 = 0 To UBound($aFiles) - 1

Is has to be from 0 to Ubound, otherwise i will miss the first record of the array.

Edited by drbyte

Share this post


Link to post
Share on other sites
JCEF

@JCEF

I'am also testing your code at the moment.

Wat does "$oExcel.Sheets("Folha1")" mean is this the name of the sheet name?

Did some experimenting it is indeed the name of the worksheet.

There is a small mistake in your code, corrected code below.

For $1 = 0 To UBound($aFiles) - 1

Is has to be from 0 to Ubound, otherwise i will miss the first record of the array.

 

Good night!

In fact this sentence "$ oExcel.Sheets (" Folha1 ")" refers to the name of the first sheet (in portuguese). I found it interesting to identify the sheet because in future situations may be useful to you if you have to pass data to more than one sheet.

I confess I have not looked very closely at your code. I focused on the end and just took in the values ​​of array '$aFiles' and put them in Excel.

For this potential error 'For $1 = 1 To UBound ($ aFiles) - 1' I do not agree with you. If you analizares the values ​​passed by the arrays, certainly you will see that the value of 'Index 0' is just the number of files contained in the folder analyzed at the beginning of the script. I think that number is not necessary to transpose into Excel sheet, correct? May even bring you problems in the future, I think. Even you are not being consistent with what you wrote in your script. If you look closely near the end when you "; populate 2ND end result of dimensional array with stripped October file name" you use my discretion.

 

thus:

Your line:

     'For $ i = UBound ($ atemp_Files) - 1 To 0 Step -1'

I would change to: 

    'For $ i = UBound ($ atemp_Files) - 1 To 1 Step -1'

and ...

Your line:

     'For $ 1 = 0 To UBound ($ atemp_Files) - 1'

I would change to:

     'For $ 1 = 1 To UBound ($ atemp_Files) - 1'

But all this is just my opinion.

Hope this helps.

Share this post


Link to post
Share on other sites
water

In fact

$oExcel.Sheets("Folha1")"

doesn't do anything. No method is being called, no property is being assigned or read.

If it would - as an example - activate the named sheet it wouldn't work if the user decides to rename the sheet. Some kind of error checking is needed.

Side note: My rewrite of the Excel UDF allows to work with multiple worksheets, worksbooks and Excel instances at a time.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
JCEF

In fact

$oExcel.Sheets("Folha1")"

doesn't do anything. No method is being called, no property is being assigned or read.

If it would - as an example - activate the named sheet it wouldn't work if the user decides to rename the sheet. Some kind of error checking is needed.

Side note: My rewrite of the Excel UDF allows to work with multiple worksheets, worksbooks and Excel instances at a time.

 

Good night!

I'm just a beginner in Excel and Autoit and liked that you confirm if this is wrong, because I want to learn more every day.

Effectively, I could / should have created a variable for the name of the sheet ("Folha1").

The phrase was inserted in this context:

;.......
$oExcel = _ExcelBookOpen(@ScriptDir & "\test\FileExcel.xlsx", 0)

For $1 = 1 To UBound($aFiles) - 1
    For $i = 2 To 21
        If $aFiles[$1][1] = $oExcel.Sheets("Folha1").Range("A" & $i).Value Then
            $oExcel.Sheets("Folha1").Range("B" & $i).Value = StringUpper("x")
        EndIf
    Next
Next

_ExcelBookClose($oExcel)
_ArrayDelete($aFiles, 0)

;.......

Thank you in advance for your attention.



Edited by JCEF

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
Sign in to follow this  

×