 |
AutoHotkey Community
Let's help each other out
|
| View previous topic :: View next topic |
| Author |
Message |
truevis
Joined: 08 Sep 2008
Posts: 10
|
 Posted: Fri Sep 12, 2008 1:41 am Post subject: Generic CSV Editor |
 |
|
I've based it on some other code I found around (THX!) here and worked on it for one day. It also shows some basic file open/save/save-as logic.
I'd like to:
- Have the cells editable directly
- Use the Format4CSV function to make it more robust.
- Add "Insert Row"
- Add "Duplicate Row"
- Make the number of columns flexible
Comments and improvements, please.
| Code: | #NoTrayIcon
GroupAdd, thisScript, %programName%
; Allow the user to maximize or drag-resize the window:
Gui +Resize
FileModified = 0
Menu, FileMenu, Add, &Open Ctrl+O, MenuFileOpen ; See remarks below about Ctrl+O.
Menu, FileMenu, Add, &Save Ctrl+S, SavePosting
Menu, FileMenu, Add, Save &As, SaveAsPosting
Menu, FileMenu, Add, E&xit, GuiClose
Menu, HelpMenu, Add, &About, MenuHandler
Menu, MyMenuBar, Add, &File, :FileMenu ; Attach the two sub-menus that were created above.
Menu, MyMenuBar, Add, &Help, :HelpMenu
Gui, Menu, MyMenuBar
Gui, Add, Button, x5, Add &Row at Bottom
Gui, Add, Button, x+5 gDELETE , Delete Row
Gui, Add, Text,x+15 y10 C0000FF, Double-Click Rows to Edit. (No quotes or commas`, please.)
Gui, Add, ListView,grid r5 x5 w900 -Multi +hscroll +vscroll altsubmit vMyListView gMyListView, ID|Col1|Col2|Col3|Col4|Col5|Col6|Col7|Col8|Col9|Col10|Col11|
;Gui, Add, Button, xm Section,Add Row
Gui, Show, ,CSV Editor
If %1%
{
Filename = %1%
GoSub,ReadData
GoSub,FILLLIST
Gui, Show, ,CSV: %Filename%
Gui, Show, AutoSize
}
;~ End of initail code
ReadData:
Loop,Read,%Filename%
{
Line%A_Index% := A_LoopReadLine
Line0 = %A_Index%
}
return
GuiSize: ; Allows the ListView to grow or shrink in response user's resizing of window.
if A_EventInfo = 1 ; The window has been minimized. No action needed.
return
; Otherwise, the window has been resized or maximized. Resize the ListView to match.
GuiControl, Move, MyListView, % "W" . (A_GuiWidth - 5) . " H" . (A_GuiHeight - 5)
;LV_ModifyCol()
return
#IfWinActive, ahk_group thisScript
^o:: ; The Ctrl+O hotkey.
MenuFileOpen:
Gui +OwnDialogs ; Force the user to dismiss the FileSelectFile dialog before returning to the main window.
If FileModified = 1
{
MsgBox, 3,, File has been modified. Do you want to save?
IfMsgBox, Yes
{
If Filename
GoSub, SavePosting
Else
GoSub, SaveAsPosting
}
IfMsgBox, Cancel
Return
}
FileSelectFile, Filename
If %Filename%
{
FileModified = 0
Gosub, ReadData
GoSub,FILLLIST
Gui, Show, ,CSV: %Filename%
Gui, Show, AutoSize
}
return
MenuHandler:
MsgBox, By Eric Boehlke`nhttp://truevis.com`nSeptember, 2008`nThanks to the AutoHotkey Community http://www.autohotkey.com/forum/
Return
HelpMenu:
Return
FILLLIST:
LV_Delete()
Loop, %Line0%
{
next_line := line%a_index%
loop, 11
{
array%a_index% =
}
loop, parse, next_line, CSV
{
array%a_index% = %a_loopfield%
array0 = %a_index%
}
LV_Add("",A_Index,Array1,Array2,Array3,Array4,Array5,Array6,Array7,Array8,Array9,Array10,Array11)
}
LV_ModifyCol(1,"Integer AutoHdr")
LV_ModifyCol(2,"AutoHdr")
LV_ModifyCol(3,"AutoHdr")
LV_ModifyCol(4,"AutoHdr")
LV_ModifyCol(5,"AutoHdr")
LV_ModifyCol(6,"AutoHdr")
LV_ModifyCol(7,"AutoHdr")
LV_ModifyCol(8,"AutoHdr")
LV_ModifyCol(9,"AutoHdr")
LV_ModifyCol(10,"AutoHdr")
LV_ModifyCol(11,"AutoHdr")
LV_ModifyCol(12,"AutoHdr")
return
MyListView:
if A_GuiEvent = DoubleClick
{
RowNumber := LV_GetNext(0)
if not RowNumber
return
Loop, 12
LV_GetText(A%A_Index%, RowNumber,A_Index)
editadd = edit
GoSub,SMGUI
}
return
SMGUI:
Gui,2:Add,Text, xm Section w80, ID
Gui,2:Add,Edit, ys-4 w420 vB1 readonly, %A1%
Gui,2:Add,Text, xm Section w80 , Col1
Gui,2:Add,Edit, ys-4 w420 vB2, %A2%
Gui,2:Add,Text, xm Section w80 , Col2
Gui,2:Add,Edit, ys-4 w420 vB3, %A3%
Gui,2:Add,Text, xm Section w80 , Col3
Gui,2:Add,Edit, ys-4 w420 vB4, %A4%
Gui,2:Add,Text, xm Section w80 , Col4
Gui,2:Add,Edit, ys-4 w420 vB5, %A5%
Gui,2:Add,Text, xm Section w80 , Col5
Gui,2:Add,Edit, ys-4 w420 vB6, %A6%
Gui,2:Add,Text, xm Section w80 , Col6
Gui,2:Add,Edit, ys-4 w420 vB7, %A7%
Gui,2:Add,Text, xm Section w80 , Col7
Gui,2:Add,Edit, ys-4 w420 vB8, %A8%
Gui,2:Add,Text, xm Section w80 , Col8
Gui,2:Add,Edit, ys-4 w420 vB9, %A9%
Gui,2:Add,Text, xm Section w80 , Col9
Gui,2:Add,Edit, ys-4 w420 vB10, %A10%
Gui,2:Add,Text, xm Section w80 , Col10
Gui,2:Add,Edit, ys-4 w420 vB11, %A11%
Gui,2:Add,Text, xm Section w80 , Col11
Gui,2:Add,Edit, ys-4 w420 vB12, %A12%
Gui,2:Add, Button, xm, OK
Gui,2:Add, Button, x+5, Cancel
Gui,2:Show, , Correct the values for this record
return
2ButtonOK:
FileModified = 1
Gui,2:Submit
Gui,2:Destroy
Gui,1:Default
if editadd = edit
{
Line%A1% := B2 . "," . B3 . "," . B4 . "," . B5 . "," . B6 . "," . B7 . "," . B8 . "," . B9 . "," . B10 . "," . B11 . "," . B12
Line%A1% := RegExReplace(Line%A1%,"[`,]+`,$") ; remove excess commas at end
}
Else
{
Line0++
Line%Line0% := B2 . "," . B3 . "," . B4 . "," . B5 . "," . B6 . "," . B7 . "," . B8 . "," . B9 . "," . B10 . "," . B11 . "," . B12
Line%Line0% := RegExReplace(Line%Line0%,"[`,]+`,$")
;~ This doesn't work right -- trying to increase the GUI's hieght a little.
;~ WinGetPos, X, Y, Width, Height , A ; "A" to get the active window's pos.
;~ Gui, Show, % "H" . (Height + 1)
Gui, Show, AutoSize
}
GoSub,FILLLIST
return
2GuiClose:
2GuiEscape:
2ButtonCancel:
Gui,2:Destroy
Gui,1:Default
;MsgBox, Editing Cancelled
return
DELETE:
FileModified = 1
RowNumber := LV_GetNext(0)
if not RowNumber
Return
Loop, 12
LV_GetText(A%A_Index%, RowNumber,A_Index)
Msgbox,4,, Are you sure you want to delete the row?
Ifmsgbox, No
return
Loop, % Line0 - A12 + 1
{
ID := A_Index + A12 - 1
IDNext := ID + 1
Line%ID% := Line%IDNext%
}
Line0--
GoSub,FILLLIST
return
ButtonAddRowatBottom:
A1=New Bottom Row
A2=
A3=
A4=
A5=
A6=
A7=
A8=
A9=
A10=
A11=
A12=
editadd=add
GoSub,SMGUI
return
#IfWinActive, ahk_group thisScript
^s::
SavePosting:
FileCopy, %Filename%, %Filename%.BAK ,1
FileRecycle, %Filename%
Gosub, GetContent
FileAppend, %FileContent%, %Filename%
FileModified = 0
Return
SaveAsPosting:
FileSelectFile, PostPathSave, S24, %Filename%, Save As
If PostPathSave
{
FileDelete, %PostPathSave%
Gosub, GetContent
FileAppend, %FileContent%, %PostPathSave%
Gui, Show, ,CSV: %PostPathSave%
Filename = %PostPathSave%
FileModified = 0
}
Return
GetContent:
FileContent =
Loop, %Line0%
FileContent := FileContent . Line%A_Index% . "`n"
Return
;~ Need to incorporate this function to make the script more robust:
;~ ;author Rhys.
;~ ;[Function] Convert String for CSV - Format4CSV() beta 1
;~ ;can be found at http://www.autohotkey.com/forum/viewtopic.php?t=27233
;~ Format4CSV(F4C_String) {
;~ Reformat:=False ;Assume String is OK
;~
;~ IfInString F4C_String,`n ;Check for linefeeds
;~ Reformat:=True ;String must be bracketed by double quotes
;~ IfInString F4C_String,`r ;Check for linefeeds
;~ Reformat:=True
;~ IfInString F4C_String,`, ;Check for commas
;~ Reformat:=True
;~
;~ IfInString F4C_String, `" ;Check for double quotes
;~ {
;~ Reformat:=True
;~ StringReplace, F4C_String, F4C_String, `",`"`", All ;The original double quotes need to be double double quotes
;~ }
;~
;~ If (Reformat)
;~ F4C_String=`"%F4C_String%`" ;If needed, bracket the string in double quotes
;~ return F4C_String
;~ }
GuiClose:
If FileModified = 1
{
MsgBox, 3,, File has been modified. Do you want to save?
IfMsgBox, Yes
{
If Filename
GoSub, SavePosting
Else
GoSub, SaveAsPosting
}
IfMsgBox, Cancel
Return
}
ExitApp |
(Updated per scottmattes' suggestion below.)
Last edited by truevis on Tue Oct 21, 2008 4:54 pm; edited 2 times in total |
|
| Back to top |
|
 |
HugoV
Joined: 27 May 2007
Posts: 650
|
| Posted: Fri Sep 12, 2008 12:40 pm Post subject: Re: Generic CSV Editor |
|
|
| truevis wrote: | | - Have the cells editable directly |
Look at http://www.autohotkey.com/forum/post-165703.html and/or
http://www.autohotkey.com/forum/topic19929.html
| truevis wrote: | | - Make the number of columns flexible |
You could:
- start with an empty listview e.g. no rows, no columns and introduce
a new file option which ask how many rows/columns
- open a new file and parse it to determine the number of columns
_________________
When parsing a CSV file use Loop, parse, Inputvar, CSV!
Last edited by HugoV on Sun Sep 21, 2008 2:15 pm; edited 1 time in total |
|
| Back to top |
|
|
garry
Joined: 19 Apr 2005
Posts: 1186
Location: switzerland
|
| Posted: Fri Sep 12, 2008 1:52 pm Post subject: |
|
|
truevis, works fine
I woul'd like to have a listview example with most used possibilities
has the important things to manage csv (add,edit,delete)
usually I use often also:
- search
- delete multiple marked lines
truevis, your example reads csv files with delimiter comma
I tried to use another delimiter ( `; )
made these changes , it seems to work
| Code: |
;---- DL > DELIMITER in this example=`;
;------------------------------------
DL=;
....
StringSplit, Array, Line%A_Index%,%DL%
....
Line%A1% := B2 . DL . B3 . DL . B4 . DL . B5 . DL . B6 . DL . B7 . DL . B8 . DL . B9 . DL . B10 . DL . B11 . DL . B12
Line%A1% := RegExReplace(Line%A1%,"[%DL%]+`,$") ;am not sure about regex
....
Line%Line0% := B2 . DL . B3 . DL . B4 . DL . B5 . DL . B6 . DL . B7 . DL . B8 . DL . B9 . DL . B10 . DL . B11 . DL . B12
Line%Line0% := RegExReplace(Line%Line0%,"[%DL%]+`,$")
....
|
------------------------------------------
some ideas , optimize your good script
example about insert (maybe not needed because can sort by columns)
http://de.autohotkey.com/forum/topic159.html&highlight=mathe
old example to start columns
http://www.autohotkey.com/forum/topic4526.html&highlight=adress |
|
| Back to top |
|
|
scottmattes
Joined: 21 May 2007
Posts: 98
Location: USA
|
| Posted: Fri Sep 12, 2008 5:29 pm Post subject: problem report, comma inside of dbl quotes |
|
|
I think that this is great, but when I opened a csv that looks like this
| Code: |
date,in,out,lunch,worked,Quitting time should be,Put in Timesheet,Over 8 hours per day,Under 8 hours per day,Left to Work this Week,Notes
"Mon, 2008-09-01",8:00,16:00,0:00,8:00,16:00,8.00,,,,Holiday: Labor Day
|
the Mon and 2008-09-01 were in separate columns. When I open the same file in Excel they are as expected, in one column.
_________________
-------------
Scott Mattes
My small, but growing, collection of scripts. |
|
| Back to top |
|
|
HugoV
Joined: 27 May 2007
Posts: 650
|
| Posted: Fri Sep 12, 2008 7:21 pm Post subject: |
|
|
Look at my signature 
Here a stringsplit on a , is used, e.g. it will fail.
DerRaphael has a nice function too in the Ask for Help
to parse a delimited file (not just comma)
_________________
When parsing a CSV file use Loop, parse, Inputvar, CSV! |
|
| Back to top |
|
|
scottmattes
Joined: 21 May 2007
Posts: 98
Location: USA
|
| Posted: Fri Sep 12, 2008 7:57 pm Post subject: |
|
|
I offer the following as a starting point:
| Code: |
loop, %line0%
{
next_line := line%a_index%
loop, 11
{
array%a_index% =
}
loop, parse, next_line, CSV
{
array%a_index% = %a_loopfield%
array0 = %a_index%
}
LV_Add("",A_Index,Array1,Array2,Array3,Array4,Array5,Array6,Array7,Array8,Array9,Array10,Array11)
}
|
Put the above two loops in place of the existing one loop of FILLLIST.
the next thing is to not limit the rows to 11 columns.[/code]
_________________
-------------
Scott Mattes
My small, but growing, collection of scripts. |
|
| Back to top |
|
|
kiropes
Joined: 21 Mar 2007
Posts: 35
Location: Italy
|
| Posted: Sat Sep 13, 2008 6:40 am Post subject: |
|
|
if a row is modified, it works
if instead a row is deleted, it doesn't work |
|
| Back to top |
|
|
|
|
You can post new topics in this forum
You can reply to topics in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
FAQ
Search
Memberlist
Register
Profile
Log in to check your private messages
Log in
