CSV file editor

[pixelsearch]

@Inpho : I hope one of the ListView gurus will be able to solve your problem.
Meanwhile, there's a great thread from LarsJ, dedicated to Virtual listviews, at this adress, maybe it will help you.

Ok, time for me to post a 2nd script (always at the beginning of the thread) including the Edit part. It should be online before the end of the day

[LarsJ]

Inpho, A virtual listview can only be used for many rows, not many columns. If you need many columns, look at this example.

[pixelsearch]

Hi everybody
New version 901k (Dec 16, 2019)

Full explanations and download links at the end of 1st post.

[pixelsearch]

Hi everybody
New version 901m (Dec 18, 2019)

1 functionality added : import can also be done by drag & drop (thanks mikell for the idea)
Download link at the end of 1st post

[t0nZ]

Very useful microtool ! I work a lot with various types of CSV and ok you have libreoffice or exel but this is much more quicker !

I have often pipe "|" separated files, so I modified:

GUICtrlCreateGroup(" Import options ", 144, 10, 161, 89)
    Local $idDelimiter = GUICtrlCreateCombo(" |  pipe delimited", 154, 32, 141, 25, BitOR($CBS_DROPDOWNLIST,$CBS_AUTOHSCROLL)) ; not $CBS_DROPDOWN that makes it editable
    GUICtrlSetData($idDelimiter, " ;  semicolon|      tab| ,  comma delimited")
    Local $idHeaders1stRow = GUICtrlCreateCheckbox("1st row = Headers", 154, 66, 133, 21)
    GUICtrlSetState(-1, $GUI_CHECKED)
    GUICtrlCreateGroup("", -99, -99, 1, 1)

But as in this very old post I am unable to put the "|" entry in a place other than the first.

Any idea ?

[jugador]

-deleted-

Edited December 20, 2019 by jugador

[pixelsearch]

@t0nZ: glad you liked the script

Pipe was also used as a delimiter in the first versions of the script (along with comma, semicolon, tab). I took it away because natively it would have interfered with :

1) GUICtrlSetData 2nd parameter as you mentioned (default separator is pipe)
2) Listview headers (default separator is pipe)
3) Listview items/subitems (default separator is pipe)
4) Content of the imported file : for example a csv file (pipe delimited) containing a pipe in one cell
5) Pipe is used in RegExp (during the script)

Some of these cases are already solved in the actual script, I need to check them again.
 

9 hours ago, t0nZ said:

I am unable to put the "|" entry in a place other than the first.

This concerns case 1) and the first versions of the script should do it :

; actual version (without pipe as delimiter)
Local $idDelimiter = GUICtrlCreateCombo(" ,  comma delimited", 154, 32, 141, 25, _
    BitOR($CBS_DROPDOWNLIST,$CBS_AUTOHSCROLL))
GUICtrlSetData($idDelimiter, " ;  semicolon|      tab")
    
; first versions (with pipe as delimiter)
Local $idDelimiter = GUICtrlCreateCombo(" ,  comma delimited", 154, 32, 141, 25, _
    BitOR($CBS_DROPDOWNLIST,$CBS_AUTOHSCROLL))
Local $sDataSeparator = Opt("GUIDataSeparatorChar", "*") ; changing "|" (default) to "*"
GUICtrlSetData($idDelimiter, " ;  semicolon*      tab* | pipe")
Opt("GUIDataSeparatorChar", $sDataSeparator) ; reset original Data Separator to "|"

But it shouldn't be enough to export without problem, depending on the input file. I'll rework it soon to include again pipe as delimiter and have it worked in all cases.

Edited December 20, 2019 by pixelsearch

[t0nZ]

Fantastic @pixelsearch , I will be glad to test your next version.

In the meanwhile I was thinkin', for me at least, a quick & dirty solution, like load a CSV with any type of separator, convert it to, let me say, "standard" comma separated, and reconvert to the original source separator when saving....

 

 

[pixelsearch]

Hi everybody,
New version 901n (Dec 20, 2019)

1 functionality added : pipe | delimiter allowed (t0nZ suggestion)

Download link at the end of 1st post

Edited December 20, 2019 by pixelsearch

[pixelsearch]

Hi everybody
New version 901p (Dec 25, 2019)

1 functionality added : now you can drag headers to reorder columns (useful if you need for example col 0 near col 5 while editing) . The exported CSV file will be saved according to the new columns order.

Download link at the end of 1st post

Edited December 30, 2019 by pixelsearch

[pixelsearch]

Hi everybody

New version 901r (Dec 29, 2019) 
1 functionality added : Numeric sort on any column (right click on column header)

Download link at the end of 1st post

Edited December 30, 2019 by pixelsearch

[pixelsearch]

Hi everybody

New version 901s (Dec 30, 2019)
1 functionality added : String sort (right click on column header)

Numeric sort is alright in most cases, but sometimes we also need a String sort like shown in the following picture. Both ways of sorting (numeric and string) are found in this new release.

Download link at the end of 1st post

Edited December 30, 2019 by pixelsearch

[ptrex]

@pixelsearch 

Nice tool !

Missing still possibility for skipping more than 1 headers rows ?

Rgds,

ptrex

[pixelsearch]

@ptrex : do you mean multi-line headers ?
I think LarsJ wrote a script allowing this feature but it's not really easy to script and it's not short code either.

The useful features I'll probably add will be related to the context menu that appears when you right click a column header (the actual "sort" context menu). They should be easy to script... but not before 2020

A very happy year end to all of you 

Edited January 3, 2020 by pixelsearch

[jugador]

@pixelsearch

was in need of "Edit Listview"
so modified your code slight bit using flag & it work
Sample code

expandcollapse popup

#include <GUIConstantsEx.au3>
#include <GuiEdit.au3>
#include <GuiListView.au3>
#include <WindowsConstants.au3>

; #############
Global $g_hEdit, $g_sSubItemText_Old = ""
Global $g_iItem = -1, $g_iSubItem = -1, $g_iItemEdit = -1, $g_iSubItemEdit = -1
Global $hGUI = GUICreate("Wandering through ListView (901f)", 460, 500)
Global $idListView = GUICtrlCreateListView _
    ("      Col 0       |    Col 1|    Col 2|    Col 3", 15, 60, 430, 400)
Global $hListView = GuiCtrlGetHandle($idListView)

For $iRow = 0 To 99
    $sRow = StringFormat("%2s", $iRow)
    GUICtrlCreateListViewItem( _
        "Row " & $sRow & " / Col 0 |" & _
        "Row " & $sRow & " / Col 1 |" & _
        "Row " & $sRow & " / Col 2 |" & _
        "Row " & $sRow & " / Col 3", $idListView)
Next

Global $g_iColumnCount = _GUICtrlListView_GetColumnCount($idListView) -1
; #############

; #############
Local $Id_ButtonA = GUICtrlCreateButton("Edit", 10, 5, 40, 22)

Local $k_Flag = 0

Local $idDummy_Enter = GUICtrlCreateDummy()
Local $idDummy_Esc = GUICtrlCreateDummy()

Local $aAccelKeys[2][2] = [["{ENTER}", $idDummy_Enter], ["{ESC}", $idDummy_Esc]]
GUISetAccelerators($aAccelKeys)
; #############


GUIRegisterMsg($WM_NOTIFY, "WM_NOTIFY")
GUISetState(@SW_SHOW)

While 1
    Switch GUIGetMsg()
        Case $GUI_EVENT_CLOSE
            GUIDelete($hGUI)
            Exit
        
        Case $Id_ButtonA
            If $k_Flag = 0 Then
                $k_Flag = 1
                Start_Edit()
            EndIf
        Case $idDummy_Esc
            If $k_Flag = 1 Then
                $k_Flag = 0
                If $g_iItemEdit > -1 Then End_Edit(0)
            EndIf
        Case $idDummy_Enter
            If $k_Flag = 1 Then
                $k_Flag = 0
                If $g_iItemEdit > -1 Then End_Edit(1)
            EndIf
    EndSwitch
WEnd

;=====
Func WM_NOTIFY($hWnd, $iMsg, $wParam, $lParam)
    #forceref $hWnd, $iMsg, $wParam

    Local $tNMHDR, $hWndFrom, $iIDFrom, $iCode
    $tNMHDR = DllStructCreate($tagNMHDR, $lParam)
    $hWndFrom = DllStructGetData($tNMHDR, "hWndFrom")
    $iCode = DllStructGetData($tNMHDR, "Code")

    Static $bMouseDown = False, $bNotXP = Not (@OSVersion = "WIN_XP")

    Switch $hWndFrom
        Case $hListView
            Switch $iCode

                Case $NM_CUSTOMDRAW
                    Local $tCustDraw = DllStructCreate($tagNMLVCUSTOMDRAW, $lParam)
                    Local $iDrawStage = DllStructGetData($tCustDraw, "dwDrawStage")
                    If $iDrawStage = $CDDS_PREPAINT Then Return $CDRF_NOTIFYITEMDRAW
                    If $iDrawStage = $CDDS_ITEMPREPAINT Then Return $CDRF_NOTIFYSUBITEMDRAW
                    Local $iItem = DllStructGetData($tCustDraw, "dwItemSpec")
                    Local $iSubItem = DllStructGetData($tCustDraw, "iSubItem")
                    Local $iColor = 0xFF000000 ; this is $CLR_DEFAULT in ColorConstants.au3
                    If $iItem = $g_iItem And $iSubItem = $g_iSubItem Then
                        $iColor = 0xFFFFC0 ; light blue for 1 subitem (BGR)
                    EndIf
                    DllStructSetData($tCustDraw, "clrTextBk", $iColor)
                    Return $CDRF_NEWFONT

                Case $LVN_KEYDOWN
                    If $bMouseDown Or $g_iItem = -1 Then Return 1 ; don't process
                    Local $tInfo = DllStructCreate($tagNMLVKEYDOWN, $lParam)
                    Local $iVK = DllStructGetData($tInfo, "VKey")
                    Switch $iVK
                        Case $VK_RIGHT
                            If $g_iSubItem < $g_iColumnCount Then
                                $g_iSubItem += 1
                                If $bNotXP Then _GUICtrlListView_RedrawItems($hListview, $g_iItem, $g_iItem)
                            EndIf
                        Case $VK_LEFT
                            If $g_iSubItem > 0 Then
                                $g_iSubItem -= 1
                                If $bNotXP Then _GUICtrlListView_RedrawItems($hListview, $g_iItem, $g_iItem)
                            EndIf
                        Case $VK_SPACE ; spacebar would select the whole row
                            Return 1
                    EndSwitch

                Case $NM_RELEASEDCAPTURE
                    $bMouseDown = True
                    Local $iItemSave = $g_iItem
                    Local $aHit = _GUICtrlListView_SubItemHitTest($hListView)
                    $g_iItem = $aHit[0]
                    $g_iSubItem = $aHit[1]
                    If $g_iItem = -1 And $iItemSave > -1 Then
                        _GUICtrlListView_RedrawItems($hListview, $iItemSave, $iItemSave)
                    EndIf

                Case $LVN_ITEMCHANGED
                    Local $tInfo = DllStructCreate($tagNMLISTVIEW, $lParam)
                    Local $iNewState = DllStructGetData($tInfo, "NewState")
                    Switch $iNewState
                        Case BitOr($LVIS_FOCUSED, $LVIS_SELECTED)
                            $g_iItem = DllStructGetData($tInfo, "Item")
                            _GUICtrlListView_SetItemSelected($hListview, $g_iItem, False)
                    EndSwitch

                Case $NM_CLICK, $NM_RCLICK
                    $bMouseDown = False

            EndSwitch
    EndSwitch
    Return $GUI_RUNDEFMSG
EndFunc   ;==>WM_NOTIFY

;=====
Func Start_Edit()
    If $g_iSubItem <> 0 Then
        $g_aRect = _GUICtrlListView_GetSubItemRect($hListView, $g_iItem, $g_iSubItem)
    Else ; column 0 needs _GUICtrlListView_GetItemRect() in case it has been dragged elsewhere (LarsJ)
        $g_aRect = _GUICtrlListView_GetItemRect($hListView, $g_iItem, 2) ; 2 = bounding rectangle of the item text
    EndIf

    $g_iItemEdit = $g_iItem
    $g_iSubItemEdit = $g_iSubItem
    $g_sSubItemText_Old = _GUICtrlListView_GetItemText($hListView, $g_iItem, $g_iSubItem)

    $g_hEdit = _GUICtrlEdit_Create($hListView, $g_sSubItemText_Old, $g_aRect[0], $g_aRect[1], $g_aRect[2]-$g_aRect[0], $g_aRect[3]-$g_aRect[1], BitOR($WS_CHILD, $WS_VISIBLE, $ES_AUTOHSCROLL, $ES_LEFT))

    _GUICtrlEdit_SetSel($g_hEdit, 0, -1) ; select all text
    _WinAPI_SetFocus($g_hEdit)
EndFunc   ;==>Start_Edit

;=====
Func End_Edit($iUpdate_Text)
    Local $sText = (($iUpdate_Text) ? (_GUICtrlEdit_GetText($g_hEdit)) : ($g_sSubItemText_Old))
    
    _GUICtrlListView_SetItemText($hListView, $g_iItemEdit, $sText, $g_iSubItemEdit)
    _WinAPI_DestroyWindow($g_hEdit)
    If $g_iItemEdit <> $g_iItem Or $g_iSubItemEdit <> $g_iSubItem Then
        _GUICtrlListView_RedrawItems($hListView, $g_iItemEdit, $g_iItemEdit)
    EndIf
    $g_iItemEdit = -1 ; -1 means no pending edition (+++)
EndFunc   ;==>End_Edit

 

[pixelsearch]

@jugador : that's great !
It will please mikell too, because his first wish was that people could reuse, for their personal need, the script core (901f, found at the very beginning of 1st post). That's exactly what you did

[pixelsearch]

Hi everybody

New Version 901t (Jan 3, 2020)

3 functionalities added : Rename Header , Insert Column , Delete Column  (right click on column header to display its context menu)

Here is an example after we right click the "type" header (please note how >>> type <<< appears at 1st line in the context menu, so the user is 100% sure of the involved column)

2nd line "Export now ?" is recommended in case you gonna Sort or Delete this column (a warning will appear if you choose to delete)

Microsoft made it a bit difficult to insert natively a column before column 0, as discussed here.
If you really want to do this, just insert a column after column 0, then drag its header at the very left to switch both leftmost columns.

Download link at the end of 1st post

[Musashi]

Just as an information for people who may have some difficulties with the english language :
I have published a thread in the german forum with a translation of the main instructions (in accordance with the author !).
https://autoit.de/thread/86733-csv-file-editor/

The download link etc. remains of course in this main thread of @pixelsearch .

To the moderators : I hope that it is allowed in this case.

[ptrex]

Added 2 modifications to make it more universal

line 44 added a SPACE as delimiter

GUICtrlSetData($idDelimiter, " ;  semicolon*      tab* | pipe*   space") ; ptrex

line 675 added more file filter options

"CSV files (*.csv))|Text (*.txt))|Log (*.log)|All (*.*)", _ ; ptrex

 

To skip X number of header rows you need to create variable that holds the number ...

And modify line 801

 

For $i = $iFirstItem + 4 To $iRows - 1 ; ptrex + 4 to skip 6 rows (0 to 4 + 1 Header Row)

And line 997

For $i = 0 + 4 To $iBound -1    ; ptrex replaced 0 to  5 to skip 6 Rows

 

Edited January 4, 2020 by ptrex

[pixelsearch]

@ptrex : I understand now what you meant by "skipping more than 1 headers rows ", it means the same functionality as Excel while importing :

I'll think about it, thanks for the idea(s)

Edited January 4, 2020 by pixelsearch