Inconsistent result when reading from CSV file

Get help with using AutoHotkey and its commands and hotkeys
asghar5676
Posts: 18
Joined: 22 Aug 2017, 01:24

Inconsistent result when reading from CSV file

20 May 2019, 21:12

Hi all,

I have a simple CSV file called Template.CSV (attached) with couple of columns and rows.
I would like to read the content of it and SEND it through. I also want to replace the blank cells with TAB. Here is my code:

Code: Select all

!F12::				;Alt + F12
;SetKeyDelay, 400
#SingleInstance force
SplitPath, A_ScriptFullPath, ofname, ofdir, ofext, ofnamene, odrv	; Ensures a consistent starting directory.
loop, read, %ofdir%\Template.csv
	{
		If (A_Index > 1)	;To skip the title row
		{
		
			Loop, parse, A_LoopReadLine, CSV
			{
				If (A_LoopField = "") ; if "cell" is empty
					Send {tab}
				Else
					{
						Send %A_LoopField%
						Send {tab}
					}
			}
		Send {Enter}
		}
	}
return	
However, I get wierd results. See below:
1.JPG
1.JPG (136.52 KiB) Viewed 831 times
As you can see, AutoHotKey is adding zeros and ones after Reveision letters from row number 2 onwards! The values in column no.2 of my CSV file is "A" but as you can see from screenshot above, the "A1" and "A0" values have been sent for this column.

The code also skips some blank cells but not all :headwall:

Can someone help plz?
Attachments
Template.csv
(1.55 KiB) Downloaded 23 times
Albireo
Posts: 851
Joined: 16 Oct 2013, 13:53

Re: Inconsistent result when reading from CSV file

21 May 2019, 03:50

I do not have time right now to analyze what goes wrong in your program.
Made a little different solution
- In the beginning of the program you need #Persistent
- instead of "Send", a file (Result.txt) is created with the result in

Code: Select all

;SetKeyDelay, 400
#SingleInstance force
#Persistent

SplitPath, A_ScriptFullPath, ofname, ofdir, ofext, ofnamene, odrv	; Ensures a consistent starting directory.

ResultFile = %A_ScriptDir%\Result.txt
If FileExist(Resultfile)
	FileDelete %ResultFile%

Return

!F12::	; Alt + F12
	MsgBox the Convert is begin!
	Loop, read, %ofdir%\Template.csv
	{	If (A_Index > 1)	;To skip the title row
		{	Loop, parse, A_LoopReadLine, CSV
			{	; MsgBox % A_LoopField "`n"StrLen(A_LoopField)
				If ( A_LoopField = "" ) ; if "cell" is empty
				{	FileAppend `t, %ResultFile%
					; Send {tab}
				}
				else
				{	FileAppend %A_LoopField% `t, %ResultFile%
						; Send %A_LoopField%
						; Send {tab}
				}
			}
		FileAppend %A_LoopField% `n, %ResultFile%
		; Send {Enter}
		}
	}
	MsgBox the Convert is ready!
Return

ESC::
	MsgBox The program is ended
	ExitApp
Return
Can these changes produce a desired result? (I had solved this problem differently)
just me
Posts: 6517
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: Inconsistent result when reading from CSV file

21 May 2019, 04:37

You might also want to try:
  1. Open an empty Notepad window.
  2. Run the following script.
  3. Press yout hotkey !F12.
  4. Look what happens.

Code: Select all

#NoEnv
; Contents of Template.csv
CSV =
(
Document Number,Revision,Title,Type,Status,Discipline,Blank Column,Print Size,Alternative Reference,Revision Date,Created By,Comments,Required for Handover,Blank Column
DD-D80.01,A,Title for Doc DD-D80.01-A.pdf,Drawing,For Tender,Civil,,A1,,16/05/2019,Civil Consulting Group Pty Ltd,,,
DD-D80.02,A,Title for Doc DD-D80.02-A.pdf,Drawing,For Tender,Civil,,A2,,17/05/2019,Civil Consulting Group Pty Ltd,,,
DD-D80.03,A,Title for Doc DD-D80.03-A.pdf,Drawing,For Tender,Civil,,A1,,18/05/2019,Civil Consulting Group Pty Ltd,,,
DD-D80.04,A,Title for Doc DD-D80.04-A.pdf,Drawing,For Tender,Civil,,A1,,19/05/2019,Civil Consulting Group Pty Ltd,,,
DD-D80.05,A,Title for Doc DD-D80.05-A.pdf,Drawing,For Tender,Civil,,A3,,20/05/2019,Civil Consulting Group Pty Ltd,,,
DD-D80.06,A,Title for Doc DD-D80.06-A.pdf,Drawing,For Tender,Civil,,A2,,21/05/2019,Civil Consulting Group Pty Ltd,,,
DD-D80.07,A,Title for Doc DD-D80.07-A.pdf,Drawing,For Tender,Civil,,A2,,22/05/2019,Civil Consulting Group Pty Ltd,,,
DD-D80.08,A,Title for Doc DD-D80.08-A.pdf,Drawing,For Tender,Civil,,A2,,23/05/2019,Civil Consulting Group Pty Ltd,,,
DD-D80.09,A,Title for Doc DD-D80.09-A.pdf,Drawing,For Tender,Civil,,A3,,24/05/2019,Civil Consulting Group Pty Ltd,,,
DD-D80.10,A,Title for Doc DD-D80.10-A.pdf,Drawing,For Tender,Civil,,A1,,25/05/2019,Civil Consulting Group Pty Ltd,,,
DD-D80.11,A,Title for Doc DD-D80.11-A.pdf,Drawing,For Tender,Civil,,A0,,26/05/2019,Civil Consulting Group Pty Ltd,,,
DD-D80.12,A,Title for Doc DD-D80.12-A.pdf,Drawing,For Tender,Civil,,A1,,27/05/2019,Civil Consulting Group Pty Ltd,,,
)

!F12::				;Alt + F12
;SetKeyDelay, 400
#SingleInstance force
SplitPath, A_ScriptFullPath, ofname, ofdir, ofext, ofnamene, odrv	; Ensures a consistent starting directory.
; Replaced Loop, Read, ... by Loop, Parse, ...
; loop, read, %ofdir%\Template.csv
Loop, Parse, CSV, `n
	{
		If (A_Index > 1)	;To skip the title row
		{
         ; Replaced A_LoopReadLine by A_LoopField
         ; Loop, Parse, A_LoopReadLine, CSV
			Loop, parse, A_LoopField, CSV
			{
				If (A_LoopField = "") ; if "cell" is empty
					Send {tab}
				Else
					{
						Send %A_LoopField%
						Send {tab}
					}
			}
		Send {Enter}
		}
	}
return
asghar5676
Posts: 18
Joined: 22 Aug 2017, 01:24

Re: Inconsistent result when reading from CSV file

21 May 2019, 18:27

Albireo wrote:
21 May 2019, 03:50
I do not have time right now to analyze what goes wrong in your program.
Made a little different solution
- In the beginning of the program you need #Persistent
- instead of "Send", a file (Result.txt) is created with the result in

Code: Select all

;SetKeyDelay, 400
#SingleInstance force
#Persistent

SplitPath, A_ScriptFullPath, ofname, ofdir, ofext, ofnamene, odrv	; Ensures a consistent starting directory.

ResultFile = %A_ScriptDir%\Result.txt
If FileExist(Resultfile)
	FileDelete %ResultFile%

Return

!F12::	; Alt + F12
	MsgBox the Convert is begin!
	Loop, read, %ofdir%\Template.csv
	{	If (A_Index > 1)	;To skip the title row
		{	Loop, parse, A_LoopReadLine, CSV
			{	; MsgBox % A_LoopField "`n"StrLen(A_LoopField)
				If ( A_LoopField = "" ) ; if "cell" is empty
				{	FileAppend `t, %ResultFile%
					; Send {tab}
				}
				else
				{	FileAppend %A_LoopField% `t, %ResultFile%
						; Send %A_LoopField%
						; Send {tab}
				}
			}
		FileAppend %A_LoopField% `n, %ResultFile%
		; Send {Enter}
		}
	}
	MsgBox the Convert is ready!
Return

ESC::
	MsgBox The program is ended
	ExitApp
Return
Can these changes produce a desired result? (I had solved this problem differently)

Thanks for your kind help. :thumbup:

Adding #Persistent didn't really help. And not sure why SEND cause some silly results whereas FileAppend methodology doesn't!
Anyhow, the result that your code is generating in a TXT file seems better but it produces an extra TAB before & after column H. Also I have three blank column at the end of my CSV file but the code is currently leaving 4 TABs at the end of each line in TEXT File.

See screenshot below:
1.JPG
1.JPG (47.53 KiB) Viewed 771 times

Any idea how to get rid of those extra TABs?
gregster
Posts: 3273
Joined: 30 Sep 2013, 06:48

Re: Inconsistent result when reading from CSV file

22 May 2019, 02:55

By sending keys, you have to rely on the receiving program. If it cannot process a certain amount of keys in a certain amount of time in the way you expect (for whatever reason), things can go sideways pretty fast. Writing to a text file is much easier and reliable.

About your extra tabs: They seem consistent with your source csv file

Code: Select all

DD-D80.01,A,Title for Doc DD-D80.01-A.pdf,Drawing,For Tender,Civil,,A1,,16/05/2019,Civil Consulting Group Pty Ltd,,,
DD-D80.02,A,Title for Doc DD-D80.02-A.pdf,Drawing,For Tender,Civil,,A2,,17/05/2019,Civil Consulting Group Pty Ltd,,,
There are actually empty fields before and after the A1/A2 column: ,,A1,, .

And you stated "I also want to replace the blank cells with TAB." So, this is what happens here, as far as I can see.
At the end of each non-empty cell, you also send a tab. So there is a tab after Civil and a tab for the empty cell after it. The same with the four tabs at the end of each line. There is one tab belonging to Civil Consulting Group Pty Ltd and then three tabs for three commas (= three empty cells), leading to four tabs in a row.

So what is the intended output? Blank cells should create a tab or not? Or, only sometimes? ;)

btw, yeah, #persistent is not relevant here.
Kobaltauge
Posts: 190
Joined: 09 Mar 2019, 01:52
GitHub: MassimoCastell
Location: Germany
Contact:

Re: Inconsistent result when reading from CSV file

22 May 2019, 10:56

This is an interesting problem. I couldn't believe it and I checked it, too.
This effect is not created by Autohotkey, it's a Notepad++ problem. After creating message boxes and inserting sleeps, the effect still existed. Then I saw this:
notepad.png
notepad.png (13.06 KiB) Viewed 729 times
Because in the first line (everytime without any errors) there is a "A1", Notepad++ suggest expanding to it in the second line when sending the "A". The next tab accept the suggestion. So the send are out of synch.

I tested it in the normal Editor in Windows and everything works as expected.
csv.png
csv.png (78.17 KiB) Viewed 729 times
gregster
Posts: 3273
Joined: 30 Sep 2013, 06:48

Re: Inconsistent result when reading from CSV file

22 May 2019, 11:10

Yeah, that's what I meant. Perhaps sending an additional key like an Esc before the Tab could help to cancel the tooltip (or whatever works in Notepad++). But of course, if you can write directly to a file, this will be more reliable...
Sam_
Posts: 106
Joined: 20 Mar 2014, 20:24

Re: Inconsistent result when reading from CSV file

22 May 2019, 14:22

asghar5676 wrote:
20 May 2019, 21:12
Hi all,

I have a simple CSV file called Template.CSV (attached) with couple of columns and rows.
I would like to read the content of it and SEND it through. I also want to replace the blank cells with TAB. Here is my code:

Code: Select all

!F12::				;Alt + F12
;SetKeyDelay, 400
#SingleInstance force
SplitPath, A_ScriptFullPath, ofname, ofdir, ofext, ofnamene, odrv	; Ensures a consistent starting directory.
loop, read, %ofdir%\Template.csv
	{
		If (A_Index > 1)	;To skip the title row
		{
		
			Loop, parse, A_LoopReadLine, CSV
			{
				If (A_LoopField = "") ; if "cell" is empty
					Send {tab}
				Else
					{
						Send %A_LoopField%
						Send {tab}
					}
			}
		Send {Enter}
		}
	}
return	
However, I get wierd results. See below:

1.JPG

As you can see, AutoHotKey is adding zeros and ones after Reveision letters from row number 2 onwards! The values in column no.2 of my CSV file is "A" but as you can see from screenshot above, the "A1" and "A0" values have been sent for this column.

The code also skips some blank cells but not all :headwall:

Can someone help plz?
I realize it's not what you explicitly asked for, but if all you really want to do is drop the 1st line, convert comma-delimited data to tab-delimited data, and send the results to the program that currently has focus, this might be a better (more efficient and less error prone) approach:

Code: Select all

SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.
!F12::	; Alt + F12
	{
	file:=FileOpen(A_ScriptDir "\Template.csv","r-d")	; Open the file for reading
	TitleRow:=file.ReadLine()	; Read the 1st line (which you don't want to use)
	EverythingElse:=file.Read()	; Read the remainder of the file's contents as text
	file.Close()	; Close the file handle

	Clipboard:=StrReplace(EverythingElse,",",A_Tab)	; Replace commas with tabs and put it on the clipboard
	Sleep, 100	; Small wait to ensure contents of clipboard is ready to paste
	Send, ^v	; Paste the contents of the clipboard to the active window's focused control
	}
Return
Kobaltauge
Posts: 190
Joined: 09 Mar 2019, 01:52
GitHub: MassimoCastell
Location: Germany
Contact:

Re: Inconsistent result when reading from CSV file

23 May 2019, 00:04

My first thought was too, why not write directly into a file? But probably @asghar5676 don't need a file, he wants to automate a program like SAP GUI. Therefore, he needs the send of the keystrokes.
asghar5676
Posts: 18
Joined: 22 Aug 2017, 01:24

Re: Inconsistent result when reading from CSV file

23 May 2019, 19:20

Thanks folks for your kind assitance. Apology for my late reply. I was dumped with other works and forgot to check the forum.

@gregster
I figure that out after posting my reply. Maybe I should change my approach and not using CSV from the first place. Please read the last paragraph to see what is my intension. Maybe you could come up with a smarter idea.

@Kobaltauge
Well done mate. Hats down to you for solving the mystery. A smart pickup ;)

@Sam_
I fill like an idiot now! How come I didn't think of that approach !? Very neat code and does the job I was trying to do in post no.1
However, as I mentioned above, please reade the last paragraph of this reply. Perhaps you guys could come up with more efficient way of what I am trying to achieve here.

-----------------------
Alright. Here is thing and what I am trying do achieve. From document control perpective, I need to upload couple of documents to a stupid web portal (Aconex is the actual name of the platform). Unfortunately there is no batch editing available which means I have to copy different pices of information from my spreadsheet and manually paste them into web portal. This include, Document Number, Revision, Title,Type,Status, etc. (pretty much all the columns you saw in that CSV file in post no.1)
Honestly it's a very fraustrating and daunting excercise.

I was hoping to write a code which could read the information from excel sheet (in this case CSV format which now I don't think it's very good idea due those unwanted Tab strokes), then activate Chrome browser window and SEND the information across. And so on. The blank columns in my CSV file was to skip the fields which do not need any information (can be skipped)

FYI, I have saved a screenshot of what Aconex portal looks like in case if you are not familiar with it.
1.JPG
1.JPG (55.04 KiB) Viewed 679 times
So do you folks think I am in the right track or I am chasing my tail? :geek:
Kobaltauge
Posts: 190
Joined: 09 Mar 2019, 01:52
GitHub: MassimoCastell
Location: Germany
Contact:

Re: Inconsistent result when reading from CSV file

24 May 2019, 00:05

so my guess, that you don't need a file at the end was right. :)
I can't help you with a code, but I can recommend you the YouTube Channel of @Joe Glines and his homepage the-automator.com. You can find a whole series how to get data directly out of excel via COM.
And there are a few videos how to fill in web forms, too.

Return to “Ask For Help”

Who is online

Users browsing this forum: Bing [Bot], blue83, dani0854, flyingDman, Google [Bot], Gully, JoeWinograd and 55 guests