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??
Copying huge .txt files content to Excel sheets
Re: Copying huge .txt files content to Excel sheets
Could it be a limitation of Excel?
https://excel.tips.net/T003163_Characte ... Cells.htmlExcel 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.
Re: Copying huge .txt files content to Excel sheets
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
Windows 10 Pro 64 bit - Autohotkey v1.1.30.01 64-bit Unicode
- FanaticGuru
- Posts: 1908
- Joined: 30 Sep 2013, 22:25
Re: Copying huge .txt files content to Excel sheets
It seems it would be better to just get rid of using the clipboard all together if you are this close with COM.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 }
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
}
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
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
Re: Copying huge .txt files content to Excel sheets
Damn right. I'm as well asked myself why someone is going this way... but then I have never tried putting so much text into a single cell.
Re: Copying huge .txt files content to Excel sheets
@ 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.
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
Windows 10 Pro 64 bit - Autohotkey v1.1.30.01 64-bit Unicode
Re: Copying huge .txt files content to Excel sheets
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.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 worked just fine if I copy everyting to a .txt
This. I think this is the main problem, using the script from the quote above I succesfully copied everything to a .txtTheDewd wrote:Could it be a limitation of Excel?https://excel.tips.net/T003163_Characte ... Cells.htmlExcel 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.
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.FanaticGuru wrote:It seems it would be better to just get rid of using the clipboard all together if you are this close with COM.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 }
Untested, but seems like it should work but then I have never tried putting so much text into a single cell.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 }
FG
Re: Copying huge .txt files content to Excel sheets
The problem is with FileRead and ^v in Excel... This script is almost working:
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?
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}
}
A possible solution would be using a variable? How can I solve this?
Re: Copying huge .txt files content to Excel sheets
Finally, this worked:
SLEEP because AHK is too fast the Windows to catch up...
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
}
- FanaticGuru
- Posts: 1908
- Joined: 30 Sep 2013, 22:25
Re: Copying huge .txt files content to Excel sheets
I see. You are using Excel's smart clipboard abilities to convert CR & LF to cell down.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 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
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
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
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
- FanaticGuru
- Posts: 1908
- Joined: 30 Sep 2013, 22:25
Re: Copying huge .txt files content to Excel sheets
Here is all COM code using Odlanir's file looping framework:
FG
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
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
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
Who is online
Users browsing this forum: JKJadan, Psych0p4th and 266 guests