Copying huge .txt files content to Excel sheets

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
User avatar
Bragato
Posts: 13
Joined: 28 Jun 2017, 07:38

Copying huge .txt files content to Excel sheets

10 Jan 2018, 12:26

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??
User avatar
TheDewd
Posts: 1513
Joined: 19 Dec 2013, 11:16
Location: USA

Re: Copying huge .txt files content to Excel sheets

10 Jan 2018, 12:32

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
Posts: 659
Joined: 20 Oct 2016, 08:20

Re: Copying huge .txt files content to Excel sheets

10 Jan 2018, 13:11

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
}   
____________________________________________________________________________
Windows 10 Pro 64 bit - Autohotkey v1.1.30.01 64-bit Unicode
User avatar
FanaticGuru
Posts: 1908
Joined: 30 Sep 2013, 22:25

Re: Copying huge .txt files content to Excel sheets

11 Jan 2018, 03:26

Odlanir wrote: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
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
BoBo
Posts: 6564
Joined: 13 May 2014, 17:15

Re: Copying huge .txt files content to Excel sheets

11 Jan 2018, 06:38

... 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 :wtf: :?: :?:
Odlanir
Posts: 659
Joined: 20 Oct 2016, 08:20

Re: Copying huge .txt files content to Excel sheets

11 Jan 2018, 08:21

@ 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.
____________________________________________________________________________
Windows 10 Pro 64 bit - Autohotkey v1.1.30.01 64-bit Unicode
User avatar
Bragato
Posts: 13
Joined: 28 Jun 2017, 07:38

Re: Copying huge .txt files content to Excel sheets

11 Jan 2018, 10:18

Odlanir wrote: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
TheDewd wrote: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
FanaticGuru wrote:
Odlanir wrote: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.
User avatar
Bragato
Posts: 13
Joined: 28 Jun 2017, 07:38

Re: Copying huge .txt files content to Excel sheets

11 Jan 2018, 10:43

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?
User avatar
Bragato
Posts: 13
Joined: 28 Jun 2017, 07:38

Re: Copying huge .txt files content to Excel sheets

11 Jan 2018, 11:09

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...
User avatar
FanaticGuru
Posts: 1908
Joined: 30 Sep 2013, 22:25

Re: Copying huge .txt files content to Excel sheets

11 Jan 2018, 16:59

Odlanir wrote:@ 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
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
User avatar
FanaticGuru
Posts: 1908
Joined: 30 Sep 2013, 22:25

Re: Copying huge .txt files content to Excel sheets

11 Jan 2018, 17:11

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
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks

Return to “Ask for Help (v1)”

Who is online

Users browsing this forum: balawi28, Bing [Bot], bobstoner289, ntepa and 179 guests