add extra text in CSV line

[euras]

there is an .CSV file where allready are some lines filled. I will read these lines and do search by some keywords. If the code finds the right line, I want to add extra text in that CSV file in column 10. The text will be "OK". Is it possible to do that in CSV file?

Code: Select all

Loop, read, C:\Users\Desktop\statistic.csv
	{
    stringsplit, output, A_loopReadLine, `;

    if (output1 = CaseID){
    		myOutputVar .= "|" A_LoopReadLine
		}
		}
		loop parse, myOutputVar, `|
		{
		finalVar := A_LoopField ;gets the last instance of keyword
		}
		StringSplit finalVarOutput, finalVar, `;
		if (finalVarOutput1 != CaseID){
		return
		}
		;do something else here

[euras]

up

[BoBo]

What about to upload (an extract) of your csv file to support your supporters?

[euras]

I don't know how it can help, because it's a simple request, but here you go.

[euras]

so can anyone say is it even possible to add extra text in an existend row in CSV file or not?

[BoBo]

Code: Select all

#CommentFlag //
#SingleInstance, Force // allow to run only a single instance of the script

ID := 333 ";" // the string you are searching for
StatusColumn := 10 // number of the column where you want to set the "OK" status.

Loop, Read, % A_WorkingDir "\statistics.csv" // your sourcefile
{
	If InStr(A_LoopReadLine, ID) // check if a line contains your search string
	{
		Line := Line "`n" ParseLine(A_LoopReadLine,StatusColumn) // if detected parse that line to set the status info at the set column 
		} Else {
		Line := Line "`n" A_LoopReadLine // concatenate the output
		}
	ToolTip % "Searching for " ID Line // display the output ...
	Sleep 1200 // for 1.2 seconds
	Tooltip // get rid of the message
	}
Return


ParseLine(Line, No){ // function to parse every field/column of the detected line
Loop, parse, Line, `; // the semicolon is the field separating delimiter here. Check the command reference for other CSV related specs
{
	Field := Field A_LoopField ";" // field left untouched if not the status field/column
	If (A_Index = No) // status field/column detected
		Field := Field "OK" // set status
	}
	Return Field
}


!a::ExitApp // hotkey to get out of here while trapped in a loop while testing
!x::Run % A_WorkingDir "\MyTest.ahk" // hotkey to restart script while testing

[euras]

Code: Select all

#CommentFlag //
#SingleInstance, Force // allow to run only a single instance of the script

ID := 333 ";" // the string you are searching for
StatusColumn := 10 // number of the column where you want to set the "OK" status.

Loop, Read, % A_WorkingDir "\statistics.csv" // your sourcefile
{
	If InStr(A_LoopReadLine, ID) // check if a line contains your search string
	{
		Line := Line "`n" ParseLine(A_LoopReadLine,StatusColumn) // if detected parse that line to set the status info at the set column 
		} Else {
		Line := Line "`n" A_LoopReadLine // concatenate the output
		}
	ToolTip % "Searching for " ID Line // display the output ...
	Sleep 1200 // for 1.2 seconds
	Tooltip // get rid of the message
	}
Return


ParseLine(Line, No){ // function to parse every field/column of the detected line
Loop, parse, Line, `; // the semicolon is the field separating delimiter here. Check the command reference for other CSV related specs
{
	Field := Field A_LoopField ";" // field left untouched if not the status field/column
	If (A_Index = No) // status field/column detected
		Field := Field "OK" // set status
	}
	Return Field
}


!a::ExitApp // hotkey to get out of here while trapped in a loop while testing
!x::Run % A_WorkingDir "\MyTest.ahk" // hotkey to restart script while testing

thank you for your code BoBo, but I tested it and it doesn't add the text in a CSV file at all... did you tried that code? Does it works for you?

[BoBo]

thank you for your code BoBo, but I tested it and it doesn't add the text in a CSV file at all... did you tried that code? Does it works for you?

Of course not! That's the "do something else here"-part of your request!!

Currently it displays the result/file content in a ToolTip.
You simply have to use the exact same variable within the FileAppend command. That's it.

[garry]

a basic listview script
modify row with rightclick , maybe click button 'PAYED' which sends YES to column-3

Code: Select all

;-- example Listview modify with rightclick ---
;-- ahk basic XP

modified=20121212
#NoEnv                     
SendMode Input               
SetWorkingDir %A_ScriptDir%
SetBatchLines -1
name1=Test
filename1=%name1%-%modified%          Edit=RightClick
;--- create a test-file ----
F1=%a_scriptdir%\LV_testbasic.txt

ifnotexist,%f1%
{
e4x=
(Ltrim Join`r`n
Jeff,20,
Garry,19,
)
Fileappend,%e4x%`r`n,%f1%
}
;-----------------------------

T1=110                  ;-- width column-1
T2=60                   ;-- width column-2
T3=110                  ;-- width column-3
tot:=(T1+T2+T3+40)      ;-- width listview
xpos=110
gsw:=(xpos+T1+T2+T3+50) ;-- width GUI

Gui,1:Font,S13 CDefault,Lucida Console
Gui,1:Add, ListView,backgroundteal csilver grid r10 x%xpos% y0 w%tot% +hscroll altsubmit gLW2 vLV_1, Name|Age|PAYED
LV_ModifyCol(1,T1)                            ;-- size
LV_ModifyCol(2,T2)
LV_ModifyCol(3,T3)

Gui,1:Show,x0 y0 w%gsw% h260,%filename1%
gosub,lb
return
;=================================================================================================================
guiclose:
exitapp

LB:
Gui,1:default
LV_Delete()
loop,read,%F1%
  {
  LR:=A_LoopReadLine
  if LR=
     continue
  LR=%LR%                                    ;-- remove leading space
  stringsplit,C,LR,`,                        ;-- split with delimiter
  LV_Add("",C1,C2,C3)
  }

LV_ModifyCol(1, "Sort")                      ;-- sort column-1
LV_ModifyCol(2,"integer")
LV_Modify(LV_GetCount(), "Vis")              ;-- scrolls to last line
return
;==============================================================

;============================================================
LW2:
Gui, Submit,nohide
Gui,1:ListView, LV_1
;=================== MODIFY ============

if A_GuiEvent = RightClick
  {
  LV_GetText(C1,A_EventInfo,1)
  LV_GetText(C2,A_EventInfo,2)
  LV_GetText(C3,A_EventInfo,3)

  RN:=LV_GetNext("C")
  RF:=LV_GetNext("F")
  GC:=LV_GetCount()

  ewd:=(gsw-100)

  GuiControl,3:Focus,Name
  Gui,3:Font,  S10 CDefault , FixedSys

  Gui,3:Add,Text,  x1  y5 w80 h20, Name
  Gui,3:Add,Edit,  x80 y5 w%ewd% h20 vA31 limit15, %C1%

  Gui,3:Add,Text,  x1  y30 w80  h20, Age
  Gui,3:Add,Edit,  x80 y30 w%ewd% h20 vA32 number limit2, %C2%

  Gui,3:Add,Text,  x1  y60 w80  h20, PAYED
  Gui,3:Add,Edit,  x80 y60 w%ewd% h20 vA33, %C3%

  Gui,3:Add, Button, x10  y90 w40 h25 gOk1, OK
  Gui,3:Add, Button, x150 y90 w90 h25 gPayed, PAYED
  Gui,3:Show, x2 y385 w%gsw% h120, MODIFY
  return
;---------------------------------------
  3GuiClose:
  3GuiEscape:
  Gui, 3:Destroy
  return

  Payed:
  GuiControl,3:Text,A33,YES
  ok1:
  Gui,3:submit
  FileRead, FileContent, %F1%
  FileDelete, %F1%
  StringReplace, FileContent, FileContent, %C1%`,%C2%`,%C3% , %A31%`,%A32%`,%A33%
  FileAppend, %FileContent%, %F1%
  GoSub, LB
  Goto,3guiclose
  }
return
;=====================================================

[euras]

thank you for your code BoBo, but I tested it and it doesn't add the text in a CSV file at all... did you tried that code? Does it works for you?

Of course not! That's the "do something else here"-part of your request!!

Currently it displays the result/file content in a ToolTip.
You simply have to use the exact same variable within the FileAppend command. That's it.

But FileAppend puts a New row each time when used, not a text in an existend row..

[euras]

it adds a Whole New line With the OK at the end, but not just the "OK" in the existend line.

Code: Select all

Loop, read, C:\Users\Desktop\statistic.csv
	{
    stringsplit, output, A_loopReadLine, `; ;replace the comma with whatever your csv is delimited by - usually and most commonly a comma..

    if (output1 = CaseID){ 
    		myOutputVar .= "|" A_LoopReadLine ;creates a list of all the rows where val is found
		}
		}
		loop parse, myOutputVar, `|
		{
		finalVar := A_LoopField ;gets the last instance of keyword
		}
		StringSplit finalVarOutput, finalVar, `; 
		FILEAPPEND,%finalVar%`;"OK", C:\Users\Desktop\statistic.csv ; it adds a Whole New line With the OK at the end, but not just the "OK" in the existend line.

[jmeneses]

Your script

Code: Select all

CaseID := "111"
Loop, read, statistics.csv
	{      
	oIndex := A_index
      stringsplit, output, A_loopReadLine, `; 
      if (output1 = CaseID){ 
         finalVar := oIndex
	}
}
Loop, read, statistics.csv
	{      
      output := A_loopReadLine
	If (finalVar = A_index)
          output .= "OK;"
	Fileappend,% output "`n", statistic2.csv
}

With ComObject

Code: Select all

CaseID := "111"
xlValues   = -4163
xlByRows   = 1
xlwhole    = 1
xlPrevious = 2
Return
	
#IfWinActive ahk_class XLMAIN
f1::  
oExcel := ComObjActive("Excel.Application")  ; Excel must be running for this example
oExcel.Visible := true
oSheet := oExcel.ActiveSheet                                                                 
m := ComObjMissing()	

lastRow := oExcel.Sheets(1).UsedRange.rows.count              
oRange := oSheet.Range("A1:A" . lastRow)
If (F :=   oRange.Find(What:=CaseID, m, LookIn:=xlValues, _
	.  LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, _
	.  SearchFormat:=False)) {
      f.offset.address.select
      f.EntireColumn.Offset(0, 1).Insert
      f.offset(0,1).Value := "OKKKKK"
     }

Return

[BoBo]

With ComObject

Code: Select all

CaseID := "111"
xlValues   = -4163
xlByRows   = 1
xlwhole    = 1
xlPrevious = 2
Return
	
#IfWinActive ahk_class XLMAIN

f1::  
oExcel := ComObjActive("Excel.Application")  ; Excel must be running for this example
oExcel.Visible := true
oSheet := oExcel.ActiveSheet                                                                 
m := ComObjMissing()	

lastRow := oExcel.Sheets(1).UsedRange.rows.count              
oRange := oSheet.Range("A1:A" . lastRow)
If (F :=   oRange.Find(What:=CaseID, m, LookIn:=xlValues, _
	.  LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, _
	.  SearchFormat:=False)) {
      f.offset.address.select
      f.EntireColumn.Offset(0, 1).Insert
      f.offset(0,1).Value := "OKKKKK"
     }

Return

Thanks for that more sophisticated sample. Much appreciated
But are there any specific AHK requirements > version/release ??
I'm asking bc it fails for me with an "illegal character"-error msg ...

[jmeneses]

Hi bobo

AHK UniCode Version 1.1.25.01
OS WIN32_NT / WIN_7
Office 2010

[euras]

Hi bobo

AHK UniCode Version 1.1.25.01
OS WIN32_NT / WIN_7
Office 2010

sadly I can not use COM objectives because it denies the file from geting New information in that period when you have open it. I need another solution....

[BoBo]

... because it denies the file from getting New information in that period when you have open it

Well, that's it. If "sharing violations" are something you've to deal with in general, you've to describe your environment more precisely.

[jmeneses]

You have tried this !!

Your script

Code: Select all

CaseID := "111"
Loop, read, statistics.csv
	{      
	oIndex := A_index
      stringsplit, output, A_loopReadLine, `; 
      if (output1 = CaseID){ 
         finalVar := oIndex
	}
}
Loop, read, statistics.csv
	{      
      output := A_loopReadLine
	If (finalVar = A_index)
          output .= "OK;"
	Fileappend,% output "`n", statistic2.csv
}

[euras]

You have tried this !!

Yes, I tried this one, but this one creates a New .csv file With the "ok" instead of add the text in an existend line in an existend file. I can not Delete that file and owervrite it again or create a New one file. I need to work exactly With that one .csv file and make changes in that one file...

[Xeo786]

You have tried this !!

Yes, I tried this one, but this one creates a New .csv file With the "ok" instead of add the text in an existend line in an existend file. I can not Delete that file and owervrite it again or create a New one file. I need to work exactly With that one .csv file and make changes in that one file...

you can replace statistics.csv with statistic2.csv in the end.

Code: Select all

FileCopy, C:\statistic2.csv, C:\statistics.csv, 1
if ErrorLevel
            MsgBox File copy Failed