Reads Excel (xlsx) Fast

Post your working scripts, libraries and tools
IMEime
Posts: 537
Joined: 20 Sep 2014, 06:15

Reads Excel (xlsx) Fast

20 Mar 2017, 07:43

[EDIT]
**********************************************************************************************
It came to me a couple of days ago though.
It is quite old specifications (Let's say millions of years or so..)


Just have a (archeological) fun.
That is my real intention here.

**********************************************************************************************


I wrote a brief code for fun.

It reads xlsx file's content.
(strings and numbers-simply data.)

First of all.
I made a testing.xlsx file.
It has 100,000 cells within a sheet.
Each of it has 10 alpha-numeric random characters.
For instance, "d4j9415852".
And all of the cells are unique (no duplications).

Now.
I tried to extract its contents and made a txt file.

When I run the COM PIA codes.
It took me 24.9 secs.

And, this "Fast" codes took only 0.8 sec.

OMG, it is 30 times faster.
That is good, real good !!!

Though, it is just a level of pseudo code, it works.

Regards



COM PIA codes (it is slow, too slow)

Code: Select all

myExcel := "Testing.xlsx"		
startTime  :=  A_TickCount															
oExcel := ComObjCreate( "Excel.Application" )
oExcel.Workbooks.Open( myExcel ) 		
For eachCell In oExcel.ActiveSheet.UsedRange.Columns(1).Cells
	myContents .= eachCell.Text "`n"
oExcel.Quit				
oExcel  =		
resultFile := RegExReplace( myExcel, "\.xlsx") "_resultFile.txt"
FileAppend,% myContents, % resultFile
;Run, % resultFile,,, Max	
MsgBox % A_TickCount - startTime
my "Fast" codes

Code: Select all

myExcel := "Testing.xlsx" 	
startTime  :=  A_TickCount	
tempFolder := RegExReplace( myExcel, ".*\K\\.*") "\_Excel_UnZip\"		
tempName := RegExReplace( myExcel, "\.xlsx") ".zip"
FileCopy, % myExcel , % tempName
FileCreateDir, % tempFolder
tempObject := ComObjCreate("Shell.Application")
tempObject.Namespace(tempFolder).CopyHere( tempObject.Namespace(tempName).items, 4|16)	
FileDelete, % tempName
FileEncoding, UTF-8
FileRead, contentNumber, % tempFolder "\" "xl\worksheets\sheet1.xml"	
FileRead, contentString, % tempFolder "\" "xl\sharedStrings.xml"			
stringArray := []
While @ := RegExMatch( contentString, "<t>(.+?)</t>", _, @ ? StrLen(_) + @ : 1 )  	
	stringArray.Insert( _1)
myPattern := "<c r=""[A-Z]+[0-9]+""( t=""s"")?><v>(.+?)</v></c>"
While @ := RegExMatch( contentNumber, myPattern, _, @ ? StrLen(_) + @ : 1 )  		
	myContents .= ( StrLen( _1) ? stringArray[1+_2] : _2 ) "`n"		
FileRemoveDir, % tempFolder, 1
resultFile := RegExReplace( myExcel, "\.xlsx") "_Extracted.txt"
FileAppend,% myContents, % resultFile
;Run, % resultFile,,, Max	
MsgBox % A_TickCount - startTime
When you are going to use someone else's script, Please just leave a brief comment saying thank you.
타인의 스크립트를 이용할 때는 최소한의 감사 표시를 남기시기 바랍니다. 개싸가지 도적질은 그만 하시고..
Last edited by IMEime on 22 Mar 2017, 22:12, edited 11 times in total.
User avatar
Blackholyman
Posts: 1272
Joined: 29 Sep 2013, 22:57
Facebook: socialjsz
Google: +Jszapp
Location: Denmark
Contact:

Re: Reads Excel (xlsx) Fast

20 Mar 2017, 09:03

Also check out:
[hr][/hr]The Monthly AutoHotkey Webinars

My Autohotkey Blog
:dance: [hr][/hr]
IMEime
Posts: 537
Joined: 20 Sep 2014, 06:15

Re: Reads Excel (xlsx) Fast

20 Mar 2017, 10:47

Nice try.

Though, it is still slower 3 times than mine.

Mine is less than 1s.
Yours is more than 2s. Always..

Regards.
ozzii
Posts: 314
Joined: 30 Oct 2013, 06:04

Re: Reads Excel (xlsx) Fast

21 Mar 2017, 04:20

Doesn't work with xlsm files ;(
IMEime
Posts: 537
Joined: 20 Sep 2014, 06:15

Re: Reads Excel (xlsx) Fast

21 Mar 2017, 06:09

ozzii wrote:Doesn't work with xlsm files ;(
I have tested it with xlsx not with xlsm.
What is xlsm ? Sorry, I do not know it.
ozzii
Posts: 314
Joined: 30 Oct 2013, 06:04

Re: Reads Excel (xlsx) Fast

22 Mar 2017, 09:06

Same but with macros.
I've changed xlsx in the code with xlsm, but same.
IMEime
Posts: 537
Joined: 20 Sep 2014, 06:15

Re: Reads Excel (xlsx) Fast

22 Mar 2017, 10:55

I don no
I wrote all I know in the codes
That is all
I have nothing else to say

Regards
aalizadeh
Posts: 7
Joined: 13 Feb 2017, 06:21

g933 tasten code

22 Mar 2017, 17:03

hallo zusammen.
ich möchte für g933 headset eine script schreiben.
wie kann ich die tasten code von g933 finden.
mit freundlichen Grüßen
Amir Alizadeh
FanaticGuru
Posts: 1269
Joined: 30 Sep 2013, 22:25

Re: Reads Excel (xlsx) Fast

22 Mar 2017, 17:11

IMEime wrote:
Nice try.

Though, it is still slower 3 times than mine.

Mine is less than 1s.
Yours is more than 2s. Always..

Regards.
My results are reversed from yours.

Below code of yours (with a modified path to my test folder) executes always more than 2 seconds for me.

Code: Select all

myExcel := A_Desktop "\Test\Test.xlsx" 	
tempFolder := RegExReplace( myExcel, ".*\K\\.*") "\_Excel_UnZip\"		
tempName := RegExReplace( myExcel, "\.xlsx") ".zip"
FileCopy, % myExcel , % tempName
FileCreateDir, % tempFolder
tempObject := ComObjCreate("Shell.Application")
tempObject.Namespace(tempFolder).CopyHere( tempObject.Namespace(tempName).items, 4|16)	
FileDelete, % tempName
FileEncoding, UTF-8
FileRead, contentNumber, % tempFolder "\" "xl\worksheets\sheet1.xml"	
FileRead, contentString, % tempFolder "\" "xl\sharedStrings.xml"			
stringArray := []
While @ := RegExMatch( contentString, "<t>(.+?)</t>", _, @ ? StrLen(_) + @ : 1 )  	
	stringArray.Insert( _1)
myPattern := "<c r=""[A-Z]+[0-9]+""( t=""s"")?><v>(.+?)</v></c>"
While @ := RegExMatch( contentNumber, myPattern, _, @ ? StrLen(_) + @ : 1 )  		
	myContents .= ( StrLen( _1) ? stringArray[1+_2] : _2 ) "`n"		
FileRemoveDir, % tempFolder, 1
resultFile := RegExReplace( myExcel, "\.xlsx") "_Extracted.txt"
FileAppend,% myContents, % resultFile
This code always less than a second.

Code: Select all

oExcel := ComObjCreate("Excel.Application")
oExcel.Workbooks.Open(A_Desktop "\Test\Test.xlsx") 
oExcel.DisplayAlerts := False
oExcel.ActiveWorkbook.SaveAs(A_Desktop "\Test\Test.csv", 6)
oExcel.Quit()
And most of that time is just the ComObjCreate("Excel.Application") which is the overhead it takes to start Excel running.

Yours is a nice demonstration that Excel files are really just specialized Zip files (at least in newer versions) and can be manipulated without Excel.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

[Function] Timer - Create and Manage Timers
FanaticGuru
Posts: 1269
Joined: 30 Sep 2013, 22:25

Re: Reads Excel (xlsx) Fast

22 Mar 2017, 17:22

ozzii wrote:Doesn't work with xlsm files ;(
It works with xlsm files for me.
Xlsx and Xlsm file structures are very similar. Xlsm just has a few extra files in the Zip but the same folder layout.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

[Function] Timer - Create and Manage Timers
IMEime
Posts: 537
Joined: 20 Sep 2014, 06:15

Re: Reads Excel (xlsx) Fast

22 Mar 2017, 20:38

@FG

It depends.

<< XML method>>
Cells 10,000 --> 0.7s
Cells 100,000 --> 0.7s
Cells 1,000,000 --> 38s (not 3.8s)

<< COM Save as>>
Cells 10,000 --> 1.8s
Cells 100,000 --> 2.2s
Cells 1,000,000 --> 5.5s

(AHK 64 Unicode, Office 2013 32bit, Win 8.1, Core i7)

Regards

-Ahh.. sorry, I forgot, I have two VSTO settings (Ribbons) in my everyday Excel. That could make it slower.
-On second thought, the result still has its meaning to me. I need all geared Excle, I do not need bare/clean Excel. It is useless to me.
sv270190
Posts: 43
Joined: 06 Feb 2014, 11:48
Facebook: svsammaponnu@gmail.com

Re: Reads Excel (xlsx) Fast

18 Jun 2017, 12:30

kind attention: Blackholyman
saving-excel-file-data-to-csv
your codes works good. thank you.

1. kindly modify the codes so that the excel file is open and active sheet is saved as csv file

2. all the sheets of the active work book is saved as csv files

3. normal fileselectfile save all sheets as CSV files

it will greatly help us in office work
S.V. SRINIVASAN
SRIVILLIPUTTUR
TAMIL NADU
User avatar
AlphaBravo
Posts: 447
Joined: 29 Sep 2013, 22:59

Re: Reads Excel (xlsx) Fast

12 Jan 2019, 12:20

Hello IMEime,

I have noticed your reference to this script from "Comparing 2 excel Files takes Forever to finish" request, I gave it a bit of testing and while it is for sure faster than iterating through Excel cells via COM, I noticed that if a cells is formatted (Bold/Italic/Underlined/formatted as text/foreground color/background color etc.) it will be skipped from the result text file.
IMEime
Posts: 537
Joined: 20 Sep 2014, 06:15

Re: Reads Excel (xlsx) Fast

14 Jan 2019, 03:03

@AlphaBravo

Ho.. really ?
Nice comment

I just tested only with extremely simple xlsx.

For your reference, I guess you know already well (or not)
All of its information are documented Perfectly ahhhhh ten or twenty years ago.
You can find it out somewhere else for sure

Any way
I will check it out with time

bye

Return to “Scripts and Functions”

Who is online

Users browsing this forum: au6, Google [Bot] and 25 guests