Array of Arrays for MS Excel VBA txt file reading (some different txt format) Topic is solved

Get help with using AutoHotkey and its commands and hotkeys
White_Whale
Posts: 73
Joined: 27 Sep 2019, 01:44

Array of Arrays for MS Excel VBA txt file reading (some different txt format)

27 Sep 2019, 02:01

[EDIT start]
I thought, I could have nobody's answer.
But, I am wrong
Several members have interested in.
Thanks.
So, I am attacing "Sample" files here.
a Zip file has 4 files;
1. sampleTabSeparatedValue.txt : my problem is here
2. sampleTabSeparatedValue.tsv : same one just change the extension name
3. sampleTabSeparatedValue_BlindlyImport.xlsx : Imported(read) it at MS Excel manually but just follows "Default" settings. Looks quite bad.
4. sampleTabSeparatedValue_SmartImport.xlsx : what I wanted from the file number "1" using proper VBA function and argument settings.
samples.zip
(16.17 KiB) Downloaded 21 times
캡처.PNG
캡처.PNG (81.88 KiB) Viewed 1752 times
I am not considering open it with regular COM route and read it line by line looping.
Because, it should be slow, extremely slow when I have lots of lines.

[EDIT end]


Hi..

I am struggling to write some porting code from MS Excel VBA to AHK.

I have a Tab separated text file.
And I'd like to read some column(s) as "text" format - not as automatic.
So, tried to use following VBA function.

Code: Select all

Workbooks.OpenText Filename:= _
        "myFilePath.txt" , _
        Bulah.. Bulah..
		Array(Array(1, 2), Array(2, 2))
AHK codes for array part goes;

Code: Select all

array1 := ComObjArray(0xC, 2)
array2 := ComObjArray(0xC, 2)
myArray := ComObjArray(0xC, 2)

array1[0] := 1, array1[1] := 2
array2[0] := 2, array2[1] := 2

myArray[0] := array1
myArray[1] := array2
But, AHK Help document says "VT_ARRAY" is not supported in this situation.
I do not understand it clearly though, sounds like I can not make arry of arrays.
And actually, "myArray" does not work as I wanted - whole function works well though.

Any good tips, Please ?
Last edited by White_Whale on 28 Sep 2019, 00:26, edited 3 times in total.
MannyKSoSo
Posts: 440
Joined: 28 Apr 2018, 21:59

Re: Array of Arrays for MS Excel VBA txt file reading (some different txt format)

27 Sep 2019, 09:21

I'm not sure you need to do to much converting, since it sounds like this is just a regular csv (except with tab delimiters). With that in mind I think this would be the easiest approach.

Code: Select all

Loop, Parse, InputVar, `n, `r  ; Specifying `n prior to `r allows both Windows and Unix files to be parsed.
{
	Loop, Parse, A_LoopField, `t
	{
	}
}
You would be able to loop through each of the rows, then each of the columns without much problem. You could also push this data to an array if you wanted to or do whatever you wanted. But please correct me if I am wrong on something so we can further help you.
User avatar
flyingDman
Posts: 883
Joined: 29 Sep 2013, 19:01

Re: Array of Arrays for MS Excel VBA txt file reading (some different txt format)

27 Sep 2019, 11:25

Excel can read .tsv (tab separated values) files directly but you have to associate .tsv files with Excel. Once you have done that (takes a few seconds and only has to be done once) just use:

Code: Select all

file2open := "C:\Users\xxx\Scripts\Tab delimited.tsv"
Xl := ComObjCreate("Excel.Application") 				            ;create a handle to a new excel application
Xl.Visible := true 										            ;by default excel sheets are invisible this makes them visible
Wrkbk := Xl.Workbooks.Open(file2open) 					            	;handle to specific workbook
and format the columns to your liking. For instance xl.range("b:b").NumberFormat := "@" to format column B as text.

If you have a tab separated file with a .csv extension (or any other extension for that matter), rename the file to .tsv first.

You can also read the file in a variable and then paste it in a new Excel sheet using:

Code: Select all

xl.Range("A1").PasteSpecial(-4104)

which is much faster than than parsing loops
Last edited by flyingDman on 27 Sep 2019, 15:37, edited 1 time in total.
awel20
Posts: 211
Joined: 19 Mar 2018, 14:09

Re: Array of Arrays for MS Excel VBA txt file reading (some different txt format)

27 Sep 2019, 12:43

Looks like you were doing the arrays right. This seems to work for me

Code: Select all

xlDelimited := 1
xlTextFormat := 2
xlTrue := -1
FieldInfo := ComObjArray(0xC, 2)
Arr1 := ComObjArray(3, 2) ; 0xC works too
Arr2 := ComObjArray(0xC, 2)
Arr1[0] := 1, Arr1[1] := xlTextFormat
Arr2[0] := 2, Arr2[1] := xlTextFormat
FieldInfo[0] := Arr1, FieldInfo[1] := Arr2
Excel := ComObjActive("Excel.Application")
Excel.Workbooks.OpenText(A_Desktop "\New Text Document.txt",,, xlDelimited,,, xlTrue,,,,,, FieldInfo)

White_Whale wrote:
27 Sep 2019, 02:01
But, AHK Help document says "VT_ARRAY" is not supported in this situation.
I do not understand it clearly though, sounds like I can not make arry of arrays.
I think you are referencing this part of the docs from ComObjArray()
The VARTYPE is restricted to a subset of the variant types. Neither the VT_ARRAY nor the VT_BYREF flag can be set.
I think that means that you cannot set it to VT_ARRAY because it always has that type. For example,

Code: Select all

FieldInfo := ComObjArray(0xC, 2)
MsgBox % Format("0x{:x}", ComObjType(FieldInfo)) ; 0x200C = VT_ARRAY | VT_VARIANT

White_Whale wrote:
27 Sep 2019, 02:01
And actually, "myArray" does not work as I wanted - whole function works well though.
How does it not work as you wanted? It looks like it should apply the "xlTextFormat" format to columns 1 and 2. It does that for me.

One thing to keep in mind is that OpenText does not return an object, so you can't do this

Code: Select all

var := Excel.Workbooks.OpenText(...)
var.SaveAs(...) ; var is not an object so this won't work
You would need to get the workbook some other way. For example, using ActiveWorkbook

Code: Select all

Excel.Workbooks.OpenText(...)
Excel.ActiveWorkbook.SaveAs(...)
White_Whale
Posts: 73
Joined: 27 Sep 2019, 01:44

Re: Array of Arrays for MS Excel VBA txt file reading (some different txt format)

27 Sep 2019, 23:51

@MannyKSoSo thanks for your tip. But.. looping is not my questiong.
White_Whale
Posts: 73
Joined: 27 Sep 2019, 01:44

Re: Array of Arrays for MS Excel VBA txt file reading (some different txt format)

27 Sep 2019, 23:53

@flyingDman oh that looks positive, I will check it out for sure. Thank
White_Whale
Posts: 73
Joined: 27 Sep 2019, 01:44

Re: Array of Arrays for MS Excel VBA txt file reading (some different txt format)

27 Sep 2019, 23:54

@awel20 hmmm, I appreciated your kind explanations. I check it out and get back to you.
White_Whale
Posts: 73
Joined: 27 Sep 2019, 01:44

Re: Array of Arrays for MS Excel VBA txt file reading (some different txt format)

28 Sep 2019, 00:20

I have edited top most main article
-attaching "Samples"
Thanks
Last edited by White_Whale on 28 Sep 2019, 01:48, edited 1 time in total.
White_Whale
Posts: 73
Joined: 27 Sep 2019, 01:44

Re: Array of Arrays for MS Excel VBA txt file reading (some different txt format)

28 Sep 2019, 00:22

@flyingDman I have changed extension name only from "txt" to "tsv" like you said. No. There was no difference at all. But, that was nice try. Regards
White_Whale
Posts: 73
Joined: 27 Sep 2019, 01:44

Re: Array of Arrays for MS Excel VBA txt file reading (some different txt format)

28 Sep 2019, 00:47

I tried

Code: Select all

myFilePath := "sampleTabSeparatedValue.txt"
FileRead, myContent, % myFilePath			
RegExReplace(myContent, ".+\r\n",, lineCounts)
myArray := ComObjArray(0xC, lineCounts)              												
Loop, % lineCounts
{
	tempArray%A_Index% := ComObjArray(0xC, 2)
	tempArray%A_Index%[0] := 1
	tempArray%A_Index%[1] := 2
	myArray[A_Index - 1] := tempArray%A_Index%                  								
}
xl := ComObjCreate("Excel.Application")	
xl.Workbooks.OpenText(myFilePath, 65001, 1, 1,,,,,,,, myArray)
;xl.Workbooks.OpenText(myFilePath, 65001, 1, 1)						;  Blind result, because, not used "ComObjArray" yet      xlDelimited = 1  
xl.Visible := True	
WinMaximize, ahk_class XLMAIN			
White_Whale
Posts: 73
Joined: 27 Sep 2019, 01:44

Re: Array of Arrays for MS Excel VBA txt file reading (some different txt format)

28 Sep 2019, 01:04

almost there
I have counted "commas" wrongly - too much of it there - so added one more comma.
Suddenly it worked.

Code: Select all

xl.Workbooks.OpenText(myFilePath, 65001, 1, 1, -4142,,,,,,,, myArray) 	;  xlNone = -4142
캡처.PNG
캡처.PNG (45.88 KiB) Viewed 1727 times

Several things to go.
-currently 3 cells, I can not tell you for sure how many comes in the future... Bad, very bad..
I wrote code as "text" clearly.

Thanks @awel20 !!
White_Whale
Posts: 73
Joined: 27 Sep 2019, 01:44

Re: Array of Arrays for MS Excel VBA txt file reading (some different txt format)

28 Sep 2019, 01:13

ah, "lineCounts" should be "columnCounts" but that is not the point here
White_Whale
Posts: 73
Joined: 27 Sep 2019, 01:44

Re: Array of Arrays for MS Excel VBA txt file reading (some different txt format)

28 Sep 2019, 01:25

Code: Select all

xl.Workbooks.OpenText(myFilePath, 65001, 1, 1, -4142, False, True, False, False, False, False, False, myArray, True) 
I hate this kind of situation.
It never completes.
When you read it while changing "default settings" manually. It made "perfect" result.
But, automation sucks.
User avatar
Datapoint
Posts: 76
Joined: 18 Mar 2018, 17:06

Re: Array of Arrays for MS Excel VBA txt file reading (some different txt format)  Topic is solved

28 Sep 2019, 09:03

White_Whale wrote:
28 Sep 2019, 00:47
I tried

Code: Select all

	tempArray%A_Index%[0] := 1
Shouldn't it be the column number? Like this:

Code: Select all

	tempArray%A_Index%[0] := A_Index
White_Whale wrote:
28 Sep 2019, 00:47

Code: Select all

RegExReplace(myContent, ".+\r\n",, lineCounts)
White_Whale wrote:
28 Sep 2019, 01:13
ah, "lineCounts" should be "columnCounts" but that is not the point here
Did you change the RegEx to count tabs (columns)?
White_Whale
Posts: 73
Joined: 27 Sep 2019, 01:44

Re: Array of Arrays for MS Excel VBA txt file reading (some different txt format)

28 Sep 2019, 09:16

@Datapoint good point, I have to check that again. Thanks - (I have changed RegEx part properly already, as I mentioned)
...
..
.
when I check VBA records(automatic from MS Excel), it goes

Code: Select all

FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2))
.
..
...
Wow..... You are quite right !!!
Done it
Perfcet
I made a good mistake.

I really appreciated about it
I have spent more than a day for that array.. Nice..
White_Whale
Posts: 73
Joined: 27 Sep 2019, 01:44

Re: Array of Arrays for MS Excel VBA txt file reading (some different txt format)

28 Sep 2019, 09:25

sorry Guys ! I'd like to make "Good comment" for two guys, but, I have no choice but to select one of you...... pls forgive me.

Code: Select all

myFilePath := "sampleTabSeparatedValue.txt"
FileRead, myContent, % myFilePath	
RegExMatch(myContent, "(.+?)\r\n", firstLine)			
RegExReplace(firstLine1, "\t",, columnCounts)
columnCounts := columnCounts + 1								
xl := ComObjCreate("Excel.Application")	
myArray := ComObjArray(0xC, columnCounts)              												
Loop, % columnCounts
{
	tempArray%A_Index% := ComObjArray(0xC, 2)
	tempArray%A_Index%[0] := A_Index               
	tempArray%A_Index%[1] := 2
	myArray[A_Index - 1] := tempArray%A_Index%                  								
}
xl.Workbooks.OpenText(myFilePath, 65001, 1, 1, -4142,,,,,,,, myArray) 	;   xlDelimited = 1      xlNone = -4142 									
xl.Visible := True	
WinMaximize, ahk_class XLMAIN	
User avatar
flyingDman
Posts: 883
Joined: 29 Sep 2013, 19:01

Re: Array of Arrays for MS Excel VBA txt file reading (some different txt format)

28 Sep 2019, 14:59

Your column count is based on the number of `t in the first line. The overall column count may be higher. Counting the columns in the entire file most likely requires you to loop through every line.
White_Whale
Posts: 73
Joined: 27 Sep 2019, 01:44

Re: Array of Arrays for MS Excel VBA txt file reading (some different txt format)

28 Sep 2019, 17:33

@flyingDman yes, you got a point. Fortunately, that is under my control. Strictly. Thanks..
White_Whale
Posts: 73
Joined: 27 Sep 2019, 01:44

Re: Array of Arrays for MS Excel VBA txt file reading (some different txt format)

28 Sep 2019, 22:09

I have confirmed;
It is "15" times faster to read it at once than looping at MS Excel

-tested, 5,000 lines, each has 10 items - 8 of thme is almost "yes/no" kind of flags, and the remain 2 are a sentence, let's say 10 words or something length normal sentence.
-faster records 2.5 secs vs slower 37 secs
-satisfied

Regards

Return to “Ask For Help”

Who is online

Users browsing this forum: Google [Bot], kenmoore111, lwalls, vmech, w64bit, Xeo786 and 68 guests