Jump to content
lrstndm

Read out excel to array

Recommended Posts

lrstndm

Hi All,

How is it possible to read out a Excel file without opening the excel file itself in excel?

With the current posts on the forum it's for me not possible to fix my issue.

I was looking at the Excel.au3 include file but this did not help me.

Could someone help me please?

Regards,

lrstndm

Share this post


Link to post
Share on other sites
Jfish

I do not believe Excel has to be open if you connect to it as if it were a database.  That is the only way I am aware of that it can be queried without opening it (but @water may know better).  This is an older example of a SQL connection to Excel.

Edited by Jfish

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites
water

Why do you need to avoid opening the file using the Excel application?

  • Is there no Excel installed?
  • Do you need to run your script invisible?
  • Anything else?

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
lrstndm

I do not want it to be opened. That is what happens when I tried to retrieve data from the Excel file. And also I was not possible to get some information from the opened Excel file.

Share this post


Link to post
Share on other sites
lrstndm

It needs to be invisible like reading out a text file from memory. Or is this not possible?

Share this post


Link to post
Share on other sites
water

I understand what you want. But If I understand why you want it I can provide a solution!


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
water

It's possible. Please check the help file how to open Excel invisible with the Excel UDF, function _Excel_Open.


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
lrstndm

I have a "key-value" collumn. Which I want to read out. Where I just can enter a key and get the value from the cell next to it.

Share this post


Link to post
Share on other sites
water

Use _Excel_RangeFind to get the row where your key is located. Then use the Offset method to get the value of the column right to it.

$aResult = _Excel_RangeFind($oExcel, "key", "column:column")
$vValue = $oExcel.Range($aResult[0][2]).Offset(0, 1).Value

 


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

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

    • Ahmed101
      By Ahmed101
      I have more than 12 workbooks opened together, if i wanted to attach to the last workbook opened it will take more than 1 minute !
      Is there any solution for that ?
    • HardXOR
      By HardXOR
      Hello AutoIt community
      I run into speed problem in my script wich i cant solve myself, problem is with decoding texture loop - for better explanation, you need extract from file pallete (16x 16 RGB color) and picture data (224 * 128 byte), then use correct color for your picture data.... nothing extra hard and also texture is quite small 224*256
      it is for my car model viewer/later maybe editor GranTurismo 2 from Playstation 1, so its old dataformat and i cant understand why AutoIt take so long to decode texture when good old Playstation almost 2,5 decades old can do that nearly immediately (when you list through cars in shop or garage)
      My first atempt was create all trought dllstructure, because its easier approach, but it was soooo slow (40-50s for create textures) then i upgrade my routine via arrays, first 3D arrays later only 1D, next i put decoding colors outside loop but it is still not enough, my last version took cca 15s wich is still unacceptable for car model viewer when you click on one carmodel from listview (1100 cars for whole game) and you must wait 15-16s for model to load....  oh and i forgot mention some cars have more then 1 color (much more... 8-9-10 etc) soloading take 8-9-10 times more time
      in attachment i post texture file from GranTurismo 2 for one car (contain only 1 color) and also my dll struct version and array version code
      dll struct version - ± 40 sec (33 without saving)
      #include <FileConstants.au3> Global $IMDT[256][256][4] LoadTexture("ufs9r.cdp") Func LoadTexture($file) $fileHandle = FileOpen($file, $FO_BINARY) $header = FileRead($fileHandle, 0x20) ConsoleWrite("header> " & $header & @CRLF) $PAL = FileRead($fileHandle, 0x200) ConsoleWrite("PAL> " & $PAL & @CRLF) FileSetPos($fileHandle, 0x43A0, $FILE_BEGIN) $IMD = FileRead($fileHandle, 0x7000) ConsoleWrite("IMD> " & $IMD & @CRLF) $st = DllStructCreate("BYTE[512]") DllStructSetData($st, 1, $PAL) $struct_PAL = DllStructCreate("WORD[16];WORD[16];WORD[16];WORD[16];WORD[16];WORD[16];WORD[16];WORD[16];WORD[16];WORD[16];WORD[16];WORD[16];WORD[16];WORD[16];WORD[16];WORD[16]", DllStructGetPtr($st)) $struct_IMD = DllStructCreate("BYTE[" & 0x7000 & "]") DllStructSetData($struct_IMD, 1, $IMD) $start = TimerInit() For $i = 0 To 15 For $j = 0 To 223 $cn = 0 For $k = 0 To 127 $bt = DllStructGetData($struct_IMD, 1, $j * 128 + $k + 1) $blue = BitShift(DllStructGetData($struct_PAL, $i + 1, BitAND($bt, 0x0F) + 1), 7) $IMDT[$j][$cn][0] = $blue $green = BitShift(DllStructGetData($struct_PAL, $i + 1, BitAND($bt, 0x0F) + 1), 2) $IMDT[$j][$cn][1] = $green $red = BitShift(DllStructGetData($struct_PAL, $i + 1, BitAND($bt, 0x0F) + 1), - 3) $IMDT[$j][$cn][2] = $red If DllStructGetData($struct_PAL, $i + 1, BitAND($bt, 0x0F) + 1) = 0 Then $IMDT[$j][$cn][3] = 0x00 Else $IMDT[$j][$cn][3] = 0xFF EndIf $cn += 1 $blue = BitShift(DllStructGetData($struct_PAL, $i + 1, BitShift($bt, 4) + 1), 7) $IMDT[$j][$cn][0] = $blue $green = BitShift(DllStructGetData($struct_PAL, $i + 1, BitShift($bt, 4) + 1), 2) $IMDT[$j][$cn][1] = $green $red = BitAND(BitShift(DllStructGetData($struct_PAL, $i + 1, BitShift($bt, 4) + 1), - 3), 0xFF) $IMDT[$j][$cn][2] = $red If DllStructGetData($struct_PAL, $i + 1, BitShift($bt, 4) + 1) = 0 Then $IMDT[$j][$cn][3] = 0x00 Else $IMDT[$j][$cn][3] = 0xFF EndIf $cn += 1 Next Next saveTGA($i) Next ConsoleWrite("t " & TimerDiff($start) & @CRLF) ; +- 40 seconds FileClose($fileHandle) EndFunc Func saveTGA($name) Local $tga[18] = [0x00, 0x00, 0x02, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x01, 0x20, 0x20] Local $data for $i = 0 To 17 $data &= Hex($tga[$i], 2) Next For $i = 0 To 255 For $j = 0 To 255 For $k = 0 To 3 $data &= hex($IMDT[$i][$j][$k], 2) Next Next Next $binary = FileOpen("test\" & $name & ".tga", BitOR($FO_BINARY, $FO_OVERWRITE, $FO_CREATEPATH)) FileWrite($binary, "0x" & $data) FileClose($binary) EndFunc  
      array version - ± 15 sec (under 10s without saving)
      #include <FileConstants.au3> LoadTexture2("ufs9r.cdp") Func LoadTexture2($file) $fileHandle = FileOpen($file, $FO_BINARY) $a = TimerInit() Global $header[0x20] For $i = 0 To UBound($header) - 1 $header[$i] = Int(String(FileRead($fileHandle, 1))) ; read 0x20 bytes Next ConsoleWrite("header " & TimerDiff($a) & @CRLF) $a = TimerInit() Global $PAL[0x100] For $i = 0 To UBound($PAL) - 1 $PAL[$i] = Number(FileRead($fileHandle, 2)) ; read 0x200 (16*16) words Next Global $PALcolor[16 * 16 * 4] For $i = 0 To UBound($PAL) - 1 $PALcolor[$i * 4 + 0] = BitShift($PAL[$i], 7) $PALcolor[$i * 4 + 1] = BitShift($PAL[$i], 2) $PALcolor[$i * 4 + 2] = BitShift($PAL[$i], -3) If $PAL[$i] = 0 Then $PALcolor[$i * 4 + 3] = 0x00 Else $PALcolor[$i * 4 + 3] = 0xFF EndIf Next ConsoleWrite("PAL " & TimerDiff($a) & @CRLF) $a = TimerInit() FileSetPos($fileHandle, 0x43A0, $FILE_BEGIN) Global $IMD[0x7000] For $i = 0 To UBound($IMD) - 1 $IMD[$i] = Int(String(FileRead($fileHandle, 1))) ; read 0x7000 bytes Next ConsoleWrite("IMD " & TimerDiff($a) & @CRLF) Global $IMDT[256*256*4] $a = TimerInit() For $i = 0 To 15 For $j = 0 To 223 $cn = 0 For $k = 0 To 127 $byte = $IMD[$j * 128 + $k] ; byte for decode $index = $j * 1024 + $cn * 4 $index2 = $i * 0x40 + BitAND($byte, 0x0F) * 4 $IMDT[$index + 0] = $PALcolor[$index2 + 0] ; blue $IMDT[$index + 1] = $PALcolor[$index2 + 1] ; green $IMDT[$index + 2] = $PALcolor[$index2 + 2] ; red $IMDT[$index + 3] = $PALcolor[$index2 + 3] ; alpha $cn += 1 $index = $j * 1024 + $cn * 4 $index2 = $i * 0x40 + BitShift($byte, 4) * 4 $IMDT[$index + 0] = $PALcolor[$index2 + 0] ; blue $IMDT[$index + 1] = $PALcolor[$index2 + 1] ; green $IMDT[$index + 2] = $PALcolor[$index2 + 2] ; red $IMDT[$index + 3] = $PALcolor[$index2 + 3] ; alpha $cn += 1 Next Next ;~ $b = TimerInit() saveTGA2($i) ;~ ConsoleWrite("save TGA " & TimerDiff($b) & @CRLF) Next ConsoleWrite("full time " & TimerDiff($a) & @CRLF) ; 16 seconds FileClose($fileHandle) EndFunc Func saveTGA2($name) Local $tga[18] = [0x00, 0x00, 0x02, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x01, 0x20, 0x20] Local $data For $i = 0 To 17 $data &= Hex($tga[$i], 2) Next For $i = 0 To UBound($IMDT) - 1 $data &= Hex($IMDT[$i], 2) Next $binary = FileOpen("test\" & $name & ".tga", BitOR($FO_BINARY, $FO_OVERWRITE, $FO_CREATEPATH)) FileWrite($binary, "0x" & $data) FileClose($binary) EndFunc if anyone can optimize my code I would be very grateful, or pointing me to better solution, thx
      ufs9r.cdp
    • Daniza
      By Daniza
      Hello! where should I start, if I want to have a Progress Bar while waiting for my File to be open, can I use WinWaitActive? Thanks,
    • Evolutionnext
      By Evolutionnext
      I am still a noob and not a programmer, would greatly appreciate your help.
       
      Task:
      Open Excel file with file path and name: C:\Users\GENOBEAUTYPC1\Desktop\ACTIVE BEAUTY LABELS\BeautyMe Label 200ml ACTIVE VERSION.xlsx
      This file path and name is saved in the variable: $sAnswer
      Go to Excel Tab called "formular"
      Go to Cell A1
      Insert the text saved in the variable: $sAnswer2
      ATTENTION!!! This has 2 problems.
      Problem number 1: This text contains special characters that need to be interpreted as raw text. (content is: Gemischt für#30 ml#Mindestens haltbar bis#Maria Wallerstorfer#Anwendung: Täglich 1x morgens auf das gereinigte Gesicht auftragen. Augenkontakt vermeiden.#Über 0 C° und unter 25 C° lagern.#Lot:N8A1028/D30/V2.1#Genome Plus GmbH#Georg-Wrede-St. 13, D-83395 Freilassing#GEN SERUM#DAY)
      Problem number 2: This textis longer than 255 characters.
       
      Can anyone help me?
       
      I try to do it really primitively by opening the excel, waiting until it is open, clicking where the tab is, clicking where the cell is and inserting the content of the variable, but I am stuck at the point where I am limited by 255 characters.
       

      ; Opening the right excel FileChangeDir
                  tooltip("File exists and is called:"&$sAnswer ,300,300)
                  ShellExecute($sAnswer ,"" ,"" ,"" , @SW_MAXIMIZE)
                  sleep(7000)
                  
                  tooltip("Now lets insert the right content into the excel",300,300)
                  MouseClick("left",226,1004)
                  MouseClick("left",52,179)
                  sleep(500)
                  Send("A1")
                  sleep(500)
                  send("{enter}")
                              tooltip("inserting label content",300,300)
                  sleep(500)
                  Send($sAnswer2,  1)
                                          tooltip("inserting INCIS",300,300)
                  sleep(5000)
                  Send($sAnswer3, 1)
                  sleep(5000)
       
       
       
    • FMS
      By FMS
      Hello,
      Does somebody knows a quick way to split eash word to an array in a text?
      I've a text and want to have each word in that text in an array.

      I tried to get in some way multiple seperators inside stringsplit but couldn't get it to work.
      I hope to get an array like :
      $aArray[0] = "PARIJS"
      $aArray[1] = "AFN"
      $aArray[2] = "Air"
      ....
      sample text: PARIJS (AFN) - Air France-KLM heeft de financieel directeur van het Franse nuts- en vervoersbedrijf Veolia, Philipe Capron, in het vizier om de nieuwe topman van de luchtvaartcombinatie te worden. Volgens de krant La Tribune wordt Capron als favoriet beschouwd om de topfunctie te gaan vervullen. AdChoices Advertentie Volgens bronnen tegen La Tribune is de keuze voor Capron zelfs al gemaakt door Air France-KLM en zal zijn voordracht volgende week worden voorgelegd aan de raad van toezicht bij het bedrijf. Capron zou dan de opvolger worden van de opgestapte topman Jean-Marc Janaillac. In mei trad Janaillac af nadat het hem niet was gelukt buiten de bonden om zaken te doen met het eigen personeel. Sindsdien heeft een interim-bestuur de dagelijkse leiding. De zestigjarige Capron is sinds 2014 financieel directeur bij Veolia. Daarvoor werkte hij onder meer bij mediaconcern Vivendi. Minder macht Overigens zijn er ook geruchten dat de nieuwe topman van Air France-KLM direct een zetel zou krijgen in de raad van commissarissen van KLM. Binnen de Nederlandse divisie, die juist alles op alles zet om minder macht naar Parijs te laten vloeien, is men volgens ingewijden verbolgen over die gang van zaken. ,,Stank voor dank", zo klinkt het in de wandelgangen. Eerder zou Janaillac ook al hebben geprobeerd om een dikkere vinger in de pap te krijgen bij KLM, dat door de bank genomen beter presteert dan haar Franse zustermaatschappij. Dit lukte eerder niet, onder meer door inmenging van het Nederlandse kabinet. AdChoices Advertentie Geruchten Een woordvoerder van Air France-KLM wilde niet reageren op de geruchtenstroom. Het concern gaat er vanuit dat binnen afzienbare tijd een nieuwe bestuursvoorzitter benoemd zal worden. Minister Cora van Nieuwenhuizen (Infrastructuur) wilde evenmin ingaan op geruchten. ,,Ik heb hierover nog geen contact gehad met KLM, dus dat wil ik eerst even afwachten'', zei ze. Tweede Kamerleden spraken deze week tijdens een debat nog maar eens hun zorg uit over de ontwikkelingen bij Air France-KLM. Zij drongen aan op herbevestiging van oude afspraken over de relatief zelfstandige positie van KLM binnen de groep.>Exit code: 0 Time: 28.52  
×