These codes are not working properly to import .txt file into excel sheet. Please help? Topic is solved

[Sabestian Caine]

Friends I have these codes to import a .txt file into excel sheet-

Code: Select all

f1::
x := ComObjActive("Excel.Application")
q := x.ActiveSheet.QueryTables.Add("TEXT;C:\Users\htc\file to be imported into excel.txt", x.Range("$A$1"))
q.Name := "file to be imported into excel.txt"
q.FieldNames := True
q.RowNumbers := False
q.FillAdjacentFormulas := False
q.PreserveFormatting := True
q.RefreshOnFileOpen := False
q.RefreshStyle := xlInsertDeleteCells := 1
q.SavePassword := False
q.SaveData := True
q.AdjustColumnWidth := True
q.RefreshPeriod := 0
q.TextFilePromptOnRefresh := False
q.TextFilePlatform := 437
q.TextFileStartRow := 73
q.TextFileParseType := xlFixedWidth := 2
q.TextFileTextQualifier := xlTextQualifierDoubleQuote := 1
q.TextFileConsecutiveDelimiter := False
q.TextFileTabDelimiter := True
q.TextFileSemicolonDelimiter := False
q.TextFileCommaDelimiter := False
q.TextFileSpaceDelimiter := False
a := ComObjArray(0x3, 6)
Loop, 6
    a[A_Index - 1] := 1
q.TextFileColumnDataTypes := a
a := ComObjArray(0x3, 5)
for i, v in [17, 1, 27, 32, 10]
    a[i - 1] := v
q.TextFileFixedColumnWidths := a
q.TextFileTrailingMinusNumbers := True
q.Refresh(False)
return

But these codes are not importing .txt file as I want. My .txt file is something like this-


And I want it to be imported in to excel sheet like this-


PLEASE HELP ME CORRECT THESE CODES SO THAT I CAN IMPORT THE .TXT FILE INTO EXCEL AS I WANT....

THANKS A LOT...

[Sabestian Caine]

Friends please help me...
you can download .txt file (sample file) from here-
https://expirebox.com/download/c6beb1e0 ... 34783.html

Thanks...

[Xeo786]

text is not even tab delimited in this TXT file so I use simple text replace trick to just save time,

Code: Select all

FILE := "D:\file to be imported into excel.TXT"
Xl := ComObjActive("Excel.Application")
Loop, read, %FILE%,
{
	line := A_LoopReadLine
	StringReplace, line , line, %a_space%%a_space%%a_space%, |,, all
	StringReplace, line , line, %a_space%%a_space%%a_space%, |,, all
	StringReplace, line , line, %a_space%%a_space%, |,, all
	;StringReplace, line , line, %a_space%, -,, all
	StringReplace, line , line, |||, |,, all
	StringReplace, line , line, ||, |,, all
	StringReplace, line , line, ||, |,, all
	;msgbox, %line%
	loop, parse, line, |
	{
		If (A_LoopField = " Cash" and a_index = 11)
			Xl.ActiveCell.Offset(0, A_INDEX + 1 ).value := A_LoopField
		else{
		Xl.ActiveCell.Offset(0, A_INDEX ).value := A_LoopField
	}
	}
	Xl.ActiveCell.Offset(1, 0 ).select
}
RETURN

[Sabestian Caine]

text is not even tab delimited in this TXT file so I use simple text replace trick to just save time,

Code: Select all

FILE := "D:\file to be imported into excel.TXT"
Xl := ComObjActive("Excel.Application")
Loop, read, %FILE%,
{
	line := A_LoopReadLine
	StringReplace, line , line, %a_space%%a_space%%a_space%, |,, all
	StringReplace, line , line, %a_space%%a_space%%a_space%, |,, all
	StringReplace, line , line, %a_space%%a_space%, |,, all
	;StringReplace, line , line, %a_space%, -,, all
	StringReplace, line , line, |||, |,, all
	StringReplace, line , line, ||, |,, all
	StringReplace, line , line, ||, |,, all
	;msgbox, %line%
	loop, parse, line, |
	{
		If (A_LoopField = " Cash" and a_index = 11)
			Xl.ActiveCell.Offset(0, A_INDEX + 1 ).value := A_LoopField
		else{
		Xl.ActiveCell.Offset(0, A_INDEX ).value := A_LoopField
	}
	}
	Xl.ActiveCell.Offset(1, 0 ).select
}
RETURN

Thanks dear Xeo786.....
your codes are working fine........
you are really great at ahk....

there are few problems also. First is when i run these codes (while selecting cell no a1) then it stats importing the data from cell no b1, on the other hand i want that it should start importing the data from cell no a1. and the second problem is- when i run these codes (while selecting cell no a1) then it first imports the first line then the it selects a2 then it imports the second line then it selects the a3 and so on. i want that it should import the file instantly and there should not be the physical movement of the currently selected cell (i.e. activecell).
i request you to correct these shortcomings too from these codes. However i tried to modify these codes like this to make them work as i want but these codes failed-

Code: Select all

f1::
aa:= 1
FILE := "C:\Users\htc\Desktop\file to be imported into excel.TXT"
Xl := ComObjActive("Excel.Application")
Loop, read, %FILE%,
{
	line := A_LoopReadLine
	StringReplace, line , line, %a_space%%a_space%%a_space%, |,, all
	StringReplace, line , line, %a_space%%a_space%%a_space%, |,, all
	StringReplace, line , line, %a_space%%a_space%, |,, all
	;StringReplace, line , line, %a_space%, -,, all
	StringReplace, line , line, |||, |,, all
	StringReplace, line , line, ||, |,, all
	StringReplace, line , line, ||, |,, all
	;msgbox, %line%
	loop, parse, line, |
	{
		If (A_LoopField = " Cash" and a_index = 11)
			Xl.range("a"aa).Offset(0, A_INDEX + 1 ).value := A_LoopField
		else{
		Xl.range("a"aa).Offset(0, A_INDEX ).value := A_LoopField
		aa++
	}
	}
	Xl.ActiveCell.Offset(1, 0 ).select
}
RETURN

I HOPE YOU WILL HELP FURTHER.

THANKS A LOT....

[Xeo786]

Column issue fixed see a_index for loop,
Screen update or ".visible" might help you.

Code: Select all

FILE := "D:\file to be imported into excel.TXT"
Xl := ComObjActive("Excel.Application")
;Xl.Visible := False                   ; this will hide Excel App
Xl.Application.ScreenUpdating := false ; screen updating off 
Loop, read, %FILE%,
{
	line := A_LoopReadLine
	StringReplace, line , line, %a_space%%a_space%%a_space%, |,, all
	StringReplace, line , line, %a_space%%a_space%%a_space%, |,, all
	StringReplace, line , line, %a_space%%a_space%, |,, all
	;StringReplace, line , line, %a_space%, -,, all
	StringReplace, line , line, |||, |,, all
	StringReplace, line , line, ||, |,, all
	StringReplace, line , line, ||, |,, all
	;msgbox, %line%
	loop, parse, line, |
	{
		If (A_LoopField = " Cash" and a_index = 11)
			Xl.ActiveCell.Offset(0, A_INDEX ).value := A_LoopField
		else{
		Xl.ActiveCell.Offset(0, A_INDEX - 1 ).value := A_LoopField
	}
	}
	Xl.ActiveCell.Offset(1, 0 ).select
}
Xl.Application.ScreenUpdating := true 	; screen updating on
; Xl.Visible := True					; this will show Excel App
RETURN

[Sabestian Caine]

Column issue fixed see a_index for loop,
Screen update or ".visible" might help you.

Code: Select all

FILE := "D:\file to be imported into excel.TXT"
Xl := ComObjActive("Excel.Application")
;Xl.Visible := False                   ; this will hide Excel App
Xl.Application.ScreenUpdating := false ; screen updating off 
Loop, read, %FILE%,
{
	line := A_LoopReadLine
	StringReplace, line , line, %a_space%%a_space%%a_space%, |,, all
	StringReplace, line , line, %a_space%%a_space%%a_space%, |,, all
	StringReplace, line , line, %a_space%%a_space%, |,, all
	;StringReplace, line , line, %a_space%, -,, all
	StringReplace, line , line, |||, |,, all
	StringReplace, line , line, ||, |,, all
	StringReplace, line , line, ||, |,, all
	;msgbox, %line%
	loop, parse, line, |
	{
		If (A_LoopField = " Cash" and a_index = 11)
			Xl.ActiveCell.Offset(0, A_INDEX ).value := A_LoopField
		else{
		Xl.ActiveCell.Offset(0, A_INDEX - 1 ).value := A_LoopField
	}
	}
	Xl.ActiveCell.Offset(1, 0 ).select
}
Xl.Application.ScreenUpdating := true 	; screen updating on
; Xl.Visible := True					; this will show Excel App
RETURN

thanks a lot dear Xeo786..............
you are really great............