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

[White_Whale]

[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 39 times

캡처.PNG (81.88 KiB) Viewed 2507 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 ?

[MannyKSoSo]

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.

[flyingDman]

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

[awel20]

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)

---

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

---

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]

@MannyKSoSo thanks for your tip. But.. looping is not my questiong.

[White_Whale]

@flyingDman oh that looks positive, I will check it out for sure. Thank

[White_Whale]

@awel20 hmmm, I appreciated your kind explanations. I check it out and get back to you.

[White_Whale]

I have edited top most main article
-attaching "Samples"
Thanks

[White_Whale]

@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]

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]

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 (45.88 KiB) Viewed 2482 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]

ah, "lineCounts" should be "columnCounts" but that is not the point here

[White_Whale]

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.

[Datapoint]

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

Code: Select all

RegExReplace(myContent, ".+\r\n",, lineCounts)

ah, "lineCounts" should be "columnCounts" but that is not the point here

Did you change the RegEx to count tabs (columns)?

[White_Whale]

@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]

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	

[flyingDman]

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]

@flyingDman yes, you got a point. Fortunately, that is under my control. Strictly. Thanks..

[White_Whale]

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