Copying huge .txt files content to Excel sheets

[Bragato]

Basically, it would be this:

Path = "0001"
Copy content from: C:\Project\%path%\PARTEPL.LST
Paste To: C:\Users\user\Dropbox\Profissional\Project\Sheets\Sheet.xlsx Sheets("DATA") Cell(A1)

Path = "0002"
Copy content from: C:\Project\%path%\PARTEPL.LST
Paste To: C:\Users\user\Dropbox\Profissional\Project\Sheets\Sheet.xlsx Sheets("DATA") Cell(B1)

Path = "0003"
Copy content from: C:\Project\%path%\PARTEPL.LST
Paste To: C:\Users\user\Dropbox\Profissional\Project\Sheets\Sheet.xlsx Sheets("DATA") Cell(C1)
....
Until Path = "0032" and Cell(AF1)

I only got "FileRead, clipboard, C:\TQS\HF Premium\PILAR\0001\PARTEPL.LST" to read the text. BUT when I ctrl+V to get the file content, just the part of the .txt is copied, because it has 10k lines and 105k words, it's huge. So I think that the FileRead function has some sort of size limitation.

So to copy all of the .txt content succesfully i did this:

F1::
Run, C:\Project\0001\PARTEPL.LST
sleep 500
send ^a
send ^c
IfWinExist, PARTEPL.LST
WinClose

But I don't know how to loop the Path correctly using "0000" formatting and to paste to excel sheet.

I think that a loop to the Path 1 to 32 and Excel column A to AF can do this, but I don't know how to code this.

Can anyone please help me??

[TheDewd]

Could it be a limitation of Excel?

Excel allows you to enter up to 32,767 characters in each cell. All of these characters will show up in the Formula bar just fine. The problem comes with the display limitation. There is a limit that Excel will display only the first 1,024 characters in each cell.

https://excel.tips.net/T003163_Characte ... Cells.html

[Odlanir]

Try this. Tested wit 65k lines.

Code: Select all

XL := ComObjCreate("Excel.Application")
XL.Visible := true
WB := XL.Workbooks.open(" C:\Users\user\Dropbox\Profissional\Project\Sheets\Sheet.xlsx")
WS := WB.Sheets("Data")
loop, 32
{
   FileRead, varx, %  "C:\Project\" format("{:04}",a_index) "\PARTEPL.LST"
   Clipboard := varx
   ClipWait, 1
   ws.cells(1,a_index).select
   sendinput, ^v
}   

[FanaticGuru]

Try this. Tested wit 65k lines.

Code: Select all

XL := ComObjCreate("Excel.Application")
XL.Visible := true
WB := XL.Workbooks.open(" C:\Users\user\Dropbox\Profissional\Project\Sheets\Sheet.xlsx")
WS := WB.Sheets("Data")
loop, 32
{
   FileRead, varx, %  "C:\Project\" format("{:04}",a_index) "\PARTEPL.LST"
   Clipboard := varx
   ClipWait, 1
   ws.cells(1,a_index).select
   sendinput, ^v
}   

It seems it would be better to just get rid of using the clipboard all together if you are this close with COM.

Code: Select all

XL := ComObjCreate("Excel.Application")
XL.Visible := true
WB := XL.Workbooks.open(" C:\Users\user\Dropbox\Profissional\Project\Sheets\Sheet.xlsx")
WS := WB.Sheets("Data")
loop, 32
{
   FileRead, varx, %  "C:\Project\" format("{:04}",a_index) "\PARTEPL.LST"
   ws.cells(1,a_index).value := varx
}   

Untested, but seems like it should work but then I have never tried putting so much text into a single cell.

FG

[BoBo]

... but then I have never tried putting so much text into a single cell.

Damn right. I'm as well asked myself why someone is going this way

[Odlanir]

@ FanaticGuru.
There's a HUGE difference. My code paste the clipboard contents starting fom row 1 ( column incremented by loop ) and populate as many rows as the input file rows. So every iteration should populate a single column with the file records, line by line till column 32.
Your code try to put all the file contents in a single cell.
I've supposed that's what the OP wants. Maybe I'm wrong.

[Bragato]

Try this. Tested wit 65k lines.

Code: Select all

XL := ComObjCreate("Excel.Application")
XL.Visible := true
WB := XL.Workbooks.open(" C:\Users\user\Dropbox\Profissional\Project\Sheets\Sheet.xlsx")
WS := WB.Sheets("Data")
loop, 32
{
   FileRead, varx, %  "C:\Project\" format("{:04}",a_index) "\PARTEPL.LST"
   Clipboard := varx
   ClipWait, 1
   ws.cells(1,a_index).select
   sendinput, ^v
}   

Didn't copied everything to excel, similar result as the one I posted, but the interaction with excel was perfect. I had to put sleep 50 between each line because the script was skipping files.
It worked just fine if I copy everyting to a .txt

Could it be a limitation of Excel?

Excel allows you to enter up to 32,767 characters in each cell. All of these characters will show up in the Formula bar just fine. The problem comes with the display limitation. There is a limit that Excel will display only the first 1,024 characters in each cell.

https://excel.tips.net/T003163_Characte ... Cells.html

This. I think this is the main problem, using the script from the quote above I succesfully copied everything to a .txt

Try this. Tested wit 65k lines.

Code: Select all

XL := ComObjCreate("Excel.Application")
XL.Visible := true
WB := XL.Workbooks.open(" C:\Users\user\Dropbox\Profissional\Project\Sheets\Sheet.xlsx")
WS := WB.Sheets("Data")
loop, 32
{
   FileRead, varx, %  "C:\Project\" format("{:04}",a_index) "\PARTEPL.LST"
   Clipboard := varx
   ClipWait, 1
   ws.cells(1,a_index).select
   sendinput, ^v
}   

It seems it would be better to just get rid of using the clipboard all together if you are this close with COM.

Code: Select all

XL := ComObjCreate("Excel.Application")
XL.Visible := true
WB := XL.Workbooks.open(" C:\Users\user\Dropbox\Profissional\Project\Sheets\Sheet.xlsx")
WS := WB.Sheets("Data")
loop, 32
{
   FileRead, varx, %  "C:\Project\" format("{:04}",a_index) "\PARTEPL.LST"
   ws.cells(1,a_index).value := varx
}   

Untested, but seems like it should work but then I have never tried putting so much text into a single cell.

FG

Didn't work, as Odlanir said, it goes all to the first cell only, not divided into rows, and so the 32767 char limit by excel.

[Bragato]

The problem is with FileRead and ^v in Excel... This script is almost working:

Code: Select all

XL := ComObjCreate("Excel.Application")
XL.Visible := true
WB := XL.Workbooks.open("C:\Project\Sheet.xlsx")
WS := WB.Sheets("DATA2")
loop, 32
{
	
	Run, %  "C:\Project\" format("{:04}",a_index) "\PARTEPL.LST"
	WinWait, PARTEPL.LST
	sendinput, ^a
	sendinput, ^c
	ClipWait, 1
	WinClose, PARTEPL.LST
	WinActivate, Sheet.xlsx
	WinWait, Sheet.xlsx
	sendinput, ^v
	SEND {RIGHT}
}

It copied all the .txt contents, BUT, it only opened the "...\0001\PARTEPL.LST" file, it's like a_index didn't incremented.
A possible solution would be using a variable? How can I solve this?

[Bragato]

Finally, this worked:

Code: Select all

F1::
loop, 32
{

Run, % "C:\Project\" format("{:04}",a_index) "\PARTEPL.LST"
WinWait, PARTEPL.LST
SLEEP 40
sendinput, ^a
SLEEP 40
sendinput, ^c
SLEEP 40
ClipWait, 1
SLEEP 40
WinClose, PARTEPL.LST
WinActivate, Sheet.xlsm
WinWait, Sheet.xlsm
SLEEP 40
sendinput, ^v
SLEEP 40
SEND ^{UP}
SLEEP 40
SEND {RIGHT}
SLEEP 40
}

SLEEP because AHK is too fast the Windows to catch up...

[FanaticGuru]

@ FanaticGuru.
There's a HUGE difference. My code paste the clipboard contents starting fom row 1 ( column incremented by loop ) and populate as many rows as the input file rows. So every iteration should populate a single column with the file records, line by line till column 32.
Your code try to put all the file contents in a single cell.
I've supposed that's what the OP wants. Maybe I'm wrong.

I see. You are using Excel's smart clipboard abilities to convert CR & LF to cell down.

I misunderstood, as apparantly others did, what OP was trying to do.

Below is an example of breaking the lines up manually and using COM completely without the clipboard.

Code: Select all

FileRead, FileData, % A_Desktop "\Test\Test.txt"
FileLines := StrSplit(FileData, "`n", "`r") 

Max := FileLines.MaxIndex()
SafeArray := ComObjArray(12, Max, 1)

for Index, Line in FileLines
	SafeArray[(Index-1),0] := Line

xlApp := ComObjActive("Excel.Application")
xlApp.Range("A1:A" Max).value := SafeArray

This is more reliable but might be slower. The clipboard can cause timing issues but Excel is quick in its smart processing of the clipboard. But still, I tried on a text file of 27k lines and 156k words which took less than a second.

I tried doing it across 32 columns rebuilding the SafeArray each time like this:

Code: Select all

FileRead, FileData, % A_Desktop "\Test\Test.txt"

xlApp := ComObjActive("Excel.Application")
xlApp.ScreenUpdating := false
Loop, 32
{
FileLines := StrSplit(FileData, "`n", "`r") 

Max := FileLines.MaxIndex()
SafeArray := ComObjArray(12, Max, 1)

for Index, Line in FileLines
	SafeArray[(Index-1),0] := Line

xlApp.Range("A1:A" Max).OffSet(0, A_Index).value := SafeArray
}
xlApp.ScreenUpdating:= true

With the same large file it took about 6 seconds. The clipboard code is using more total Sleep than that.

FG

[FanaticGuru]

Here is all COM code using Odlanir's file looping framework:

Code: Select all

xlApp := ComObjCreate("Excel.Application")
xlApp.Visible := true
xlWB := xlApp.Workbooks.open(" C:\Users\user\Dropbox\Profissional\Project\Sheets\Sheet.xlsx")
xlWS := xlWB.Sheets("Data")
xlApp.ScreenUpdating := false
loop, 32
{
	FileRead, FileData, %  "C:\Project\" format("{:04}",a_index) "\PARTEPL.LST"
	FileLines := StrSplit(FileData, "`n", "`r") 
	Max := FileLines.MaxIndex()
	SafeArray := ComObjArray(12, Max, 1)
	for Index, Line in FileLines
		SafeArray[(Index-1),0] := Line
	xlApp.Range("A1:A" Max).OffSet(0, A_Index).value := SafeArray
}
xlApp.ScreenUpdating:= true

FG