Query Excel

[Bucktail]

Hoping someone can help here and point me in the right direction. I have some experience with Autoit be at a very limited level. The script I would like to write is beyond my current knowledge. Hope someone can show me how to write or at least point me to a previous post that may possibly answer my request.

I need Autoit to query an Excel document and return some specs. Below is a more in-depth steps I'm looking for.

I would start with an Excel document that has part numbers and specs from one maybe two cells.

Want Autoit to open a Input Box asking for a Part Number

The user enters a part number and Autoit will then go to the excel document and look for this part number. If the requested part number is valid, Autoit will return the specs for this part number. "This part number uses this spec XXXXXXXX"

If the part number is not there Autoit will return indicating that this is not a valid part number.

If I cancel the script I would like Autoit to then close the Excel document and it self.

Also can Autoit read the excel document without opening it or does excel need to be run?

[Xenobiologist]

Should be no problem.

Besides, I would convert the excel file to a csv file. This way, you will not need excel to check the file.

[saywell]

and once it's in csv format, you could import it into sqlite and do a proper job with a proper database!!

William

[Bucktail]

Thanks for the tip. Any ideas on how to start writing this script?

[dara]

Thanks for the tip. Any ideas on how to start writing this script?

I don't know how to hide excel but you can hide OpenOffice Calc or LibreOffice Calc when working on spreadsheets.

ooCalc must still be installed (they are free anyway).

[MrMitchell]

Thanks for the tip. Any ideas on how to start writing this script?

If you go the Excel route then you need the Excel UDF (#include <Excel.au3>) and refer to the Excel functions in the help file. For example you'd need _ExcelBookOpen() and _ExcelReadSheetToArray() or _ExcelReadCell(). This isn't the recommended method.

Where does the data from the Excel spreadsheet come from? Perhaps you can query that data source directly?

Edit: If you're just using Excel to open the original file which is in fact just a CSV file, highly recommend what Xeno suggested, and it would be faster.

Edited April 13, 2011 by MrMitchell

[Bucktail]

The Excel document is a list of part numbers and their specs that I created. So it could be in xls or csv. Doesn't matter to me.

I can't imagine that this would have to be a very long script. I just have no idea what this script would be like. I was hoping for some examples from the experts here to learn from.

[MrMitchell]

I can't imagine that this would have to be a very long script. I just have no idea what this script would be like. I was hoping for some examples from the experts here to learn from.

_ExcelReadCell() documentation in the help file has an example of how to create a new excel spreadsheet then use a loop to read through the cells. Check it out and post a question about anything that doesn't make sense... I'd post an example closer to what you're trying to do but I have to leave now, sorry.

Since the data originates from you, you should use a real database to store/modify/retrieve the data as suggested earlier. Of course if you're pressed for time and just want to get something running, the Excel functions should work just fine. Either way you go you'll get plenty of help, just probably won't get someone to write the whole thing.

Edited April 13, 2011 by MrMitchell

[ptrex]

@All

Maybe this can help ?

Rgds,

ptrex

[Xenobiologist]

Just post a example csv file and what you need from it. Should be 5 min work to get the script done.

Read csv, putting data into an array, get the needed data.

[MrMitchell]

Ok an example of opening excel given the file name, read the table/sheet into an array, then loop thru the array to find the part number you're looking for then use that row number to get specs from the same row in the next column. This is very quick and dirty and needs a lot of cleaning up but you get the idea... hopefully

My example data:

And code:

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

$sSourceFile = "C:\temp\test.xls"
$oExcel = _ExcelBookOpen($sSourceFile)

$partToFind = "123abc"
ConsoleWrite ($partToFind & ": " & _FindPart($partToFind) & @CRLF)

$partToFind = "something I don't have"
ConsoleWrite ($partToFind & ": " & _FindPart($partToFind) & @CRLF)

Func _FindPart($part)
    Local $return = "Not Found!"

    $aSheet = _ExcelReadSheetToArray($oExcel, 2, 1, 0, 2)   ;Read sheet starting from row 2, column 1, read all rows, read only 2 columns
;~  _ArrayDisplay($aSheet)  ;Uncomment to display the array just to get a visual on the last function

    For $i = 1 To $aSheet[0][0]         ;[0][0] is number of rows, [0][1] is number of columns
    ;~  ConsoleWrite($aSheet[$i][1] & ", " & $aSheet[$i][2] & @CRLF)    ;Uncomment to list everything as an example
        If StringCompare($aSheet[$i][1], $partToFind) = 0 Then $return = $aSheet[$i][2]
    Next

    Return $return
EndFunc

_ExcelBookClose($oExcel, 0)

[Bucktail]

In response to Xenobiologist.

The CSV file consists of two columns of numbers. Column A is the part number to search and Column B is the spec that I would like returned.

Does that help?

Thanks for the help.

[Xenobiologist]

Hi,

not that hard. Here is a very short and simple solution.

;Your file -->

;~ Number;Specification
;~ 1;Test
;~ 2;Hugo
;~ 3;Mega
;~ 4;Yes
;~ 5;NO


#include<Array.au3>
Global $content = FileRead(@ScriptDir & '\lockup.csv')
Global $re = StringRegExp($content, '(\w+);|\w+', 3)
_ArrayDisplay($re)
$nr = InputBox('Title', 'Number:')
$result = _ArraySearch($re, $nr)
If @error Then
    MsgBox(16, 'error', 'Not found Nr: ' & $nr)
Else
    MsgBox(64, 'Specification is : ', $re[$result + 1])
EndIf

Mega

[Bucktail]

I will take a look at the script you put together and see if I can understand how to get it to work for me. I should have posted this example earlier. I am looking for some thing that would work similar to this script I made by copying a script I found and changed to work for me.

I just don't know what commands would be needed to make a script to search column A of the csv file for the requested part number and return the information that is in column B in the cell next to the part number I am searching.

;   Checks for PDF file and then opens the requested PDF file.

$path = "\\CorpData\Data\drawingpdfs\"
$ext = ".pdf"

; Loops until the user gives a valid PDF file or cancels
$bLoop = 1
While $bLoop = 1
    $file = InputBox("PDF File To Open", "Please enter in the PDF file number you want to open and click OK","","",250,175)
    If @error = 1 Then
        Exit
    Else
        $filepath = ($path & $file & $ext)
        If FileExists($filepath) Then
            ShellExecute( $filepath, "" , "" , "" , @SW_MAXIMIZE )
            $bLoop = 0    ; Exit the loop - ExitLoop would have been an alternative too :)
        Else
            MsgBox(0, "       NOT VALID", "Not a Valid Number!")
        EndIf
    EndIf
WEnd

[Xenobiologist]

Okay, if it is just a loopup you need to get done then I would go for an ini file.

Should be very easy to create for you

[PDF-FILES]

1=bla.pdf

2=hugo.pdf

3=mix.pdf

4=andsoon.pdf

Now you can use the INI-Functions of Autoit, which should make your job easier than before.

expandcollapse popup

;Your file -->

;~ [PDF-FILES]
;~ 1=bla.pdf
;~ 2=hugo.pdf
;~ 3=mix.pdf
;~ 4=andsoon.pdf

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#Region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Form1", 282, 447, 192, 124)
$Group1 = GUICtrlCreateGroup("Group1", 8, 0, 265, 441)
$Edit1 = GUICtrlCreateEdit("", 24, 80, 233, 353)
GUICtrlSetData(-1, "Edit1")
$Input1 = GUICtrlCreateInput("Input1", 80, 16, 177, 21)
$Label1 = GUICtrlCreateLabel("PDF-NR", 24, 16, 44, 17)
$Button1 = GUICtrlCreateButton("Open PDF", 16, 48, 235, 25, $WS_GROUP)
GUICtrlCreateGroup("", -99, -99, 1, 1)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

Global $path = @ScriptDir & '\', $file = 'lockup.ini'
GUICtrlSetData($Edit1, FileRead($path & $file))

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
;~          If FileExists($path & IniRead($path & $file, 'PDF-FILES', GUICtrlRead($Input1), '')) Then
            ConsoleWrite('!' & $path & IniRead($path & $file, 'PDF-FILES', GUICtrlRead($Input1), '') & @CRLF)
;~          ShellExecute($path & IniRead($path & $file, 'PDF-FILES', GUICtrlRead($Input1), "", "", "", @SW_MAXIMIZE)
;~          EndIf
    EndSwitch
WEnd

[Bucktail]

@Xenobiologist

I tried the last example you posted and it prompts for a PDF what I posted as an example is something I used for my PDF file search script. This one needs to search a csv file.

I'm having some success with the first one you posted. I just need to find some time to understand it some more. I'll let you know if I need some help.

Thanks

[Bucktail]

@Xenobiologist

The first script you wrote is sort of working. I just need to see if it can be cleaned up and looped. Right now what you have written returns only the first number. for example. If I search for 12347 I only get "16" When what I want returned is what was in the cell "16 - 0.25"

Here is a sample of my data in my csv file.

12345 22 - 0.12

12346 22 - 0.25

12347 16 - 0.25

12348 16 - 0.25

12349 20 - 0.31

Also Is there a way to NOT display the Array?

[Bucktail]

OK here is what I was able to put together with the help of the script from Xenobiologist and combining it with what I had used in a past script.

For some reason when I click on the cancel button it does not exit out completely and I don't understand why. I also need it to loop back to the InputBox if a bogus number is given.

Still would like to know if there a way to NOT display the Array? My plan is to use this out on the shop floor and the array popup with confuse the user.

Anybody with some answers for me?

;Your file -->

;~ Number;Specification
;~ 1;Test
;~ 2;Hugo
;~ 3;Mega
;~ 4;Yes
;~ 5;NO

#include<Array.au3>


Global $content = FileRead('C:\WireStrip\wirepns.csv')
Global $re = StringRegExp($content, '(\w+);|\w+', 3)

_ArrayDisplay($re)

; Loops until the user gives a valid file or cancels
$bLoop = 1
While $bLoop = 1
    $nr = InputBox("Wire Spec To Find", "Please enter the file number spec you want to find and click OK","","",250,175)
    $result = _ArraySearch($re, $nr)
    If @Error = 1 Then ; Exit if Cancel is chosen.
        Exit
    Else
        If @error Then
            MsgBox(16, 'ERROR', 'No Spec Found for ' & $nr)
            $bLoop = 0    ; Exit the loop - ExitLoop would have been an alternative too :)
        Else
            MsgBox(0, 'Spec Is : ', $re[$result + 1])
            Exit
        EndIf
    EndIf
WEnd

[MrMitchell]

Still would like to know if there a way to NOT display the Array? My plan is to use this out on the shop floor and the array popup with confuse the user.

Anybody with some answers for me?

Try removing from your code:

_ArrayDisplay($re)

[Bucktail]

OK in case anyone is wondering I was able to figure it out. Here is what I came up with. Thanks to those that helped. I was able to learn something.

#include<Array.au3>


Global $content = FileRead('C:\WireStrip\wirepns.csv')
Global $re = StringRegExp($content, '(\w+);|\w+', 3)

;_ArrayDisplay($re)

; Loops until the user gives a valid file or cancels
$bLoop = 1
While $bLoop = 1
    $nr = InputBox("Wire Spec To Find", "Please enter the file number you want to find and click OK","","",200,175)
    If @Error = 1 Then ; Exits if Cancel is chosen.
        Exit
    Else
        $result = _ArraySearch($re, $nr)
        If @error Then
            MsgBox(16, 'ERROR', 'No Spec Found for ' & $nr)
        Else
            MsgBox(0, 'Spec Is : ', $nr & " = " &$re[$result + 1] & " - "&$re[$result + 2] & $re[$result + 3]) ; this line returns 3 results.
                $bLoop = 0    ; Exit the loop - ExitLoop would have been an alternative too :)
        EndIf
    EndIf
WEnd

Edited April 15, 2011 by Bucktail