Split data in text file into text files based on dates

mucitbey · August 23, 2022

Hello Autoit lovers, I have a txt file and I want to separate the data in this file into new txt files according to the dates in the fourth column. txt filenames with this date. With the program I am currently preparing, I am choosing a date first and making it read. Then I save this data as txt with another program. Can I do this more practically? Thanks in advance for your help and support.

#include <Date.au3>
#include <File.au3>
#include <Array.au3>
#include <GuiMenu.au3> ;;;
#include <GuiListView.au3>
#include <GUIConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <GuiDateTimePicker.au3>
#include <ListViewConstants.au3>

Global $aInFile
_FileReadToArray("Data.txt", $aInFile, $FRTA_NOCOUNT)
Global $iLines = UBound($aInFile)

Global $bFile
_FileReadToArray("Users.txt", $bFile, $FRTA_NOCOUNT, "=")

GUICreate("_mucitbey_", 430, 500, -1, -1)
$lv = GUICtrlCreateListView("#|Card ID|[]|Date|Clock|User", 10, 10, 410, 480)

For $i = 0 To $iLines - 1
    $aTemp = StringSplit($aInFile[$i], ", ", $STR_NOCOUNT)
    $user = GetUser($aTemp[1])
    GUICtrlCreateListViewItem($aTemp[0] &"|"& $aTemp[1] &"|"& $aTemp[2] &"|"& $aTemp[3] &"|"& $aTemp[4] &"|"& $user, $lv)
If $aTemp[4] > "08:10:00" And $aTemp[4] < "16:50:00" Then GUICtrlSetBkColor(-1, 0xC0FFFF)
Next

GUISetState(@SW_SHOW)

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            GetText()
            Exit
    EndSwitch
WEnd

Func GetUser($ID)
    Local $iIndex = _ArraySearch($bFile, $ID)
    If $iIndex <> -1 Then
        Return $bFile[$iIndex][1]
    Else
        Return "Undefined ID"
    EndIf
EndFunc

Func GetText()
    $aExport = _GUICtrlListView_CreateArray($lv, Default)
    _GUICtrlListView_SaveTxtEx($aExport, @ScriptDir & '\'&$aTemp[3]&'.txt')
EndFunc

Func _GUICtrlListView_CreateArray($hListView, $sDelimeter = '|')
    Local $iColumnCount = _GUICtrlListView_GetColumnCount($hListView), $iDim = 0, $iItemCount = _GUICtrlListView_GetItemCount($hListView)
    If $iColumnCount < 3 Then
        $iDim = 3 - $iColumnCount
    EndIf
    If $sDelimeter = Default Then
        $sDelimeter = '|'
    EndIf

    Local $aColumns = 0, $aReturn[$iItemCount + 1][$iColumnCount + $iDim] = [[$iItemCount, $iColumnCount, '']]
    For $i = 0 To $iColumnCount - 1
        $aColumns = _GUICtrlListView_GetColumn($hListView, $i)
        $aReturn[0][2] &= $aColumns[5] & $sDelimeter
    Next
    $aReturn[0][2] = StringTrimRight($aReturn[0][2], StringLen($sDelimeter))

    For $i = 0 To $iItemCount - 1
        For $j = 0 To $iColumnCount - 1
            $aReturn[$i + 1][$j] = _GUICtrlListView_GetItemText($hListView, $i, $j)
        Next
    Next
    Return SetError(Number($aReturn[0][0] = 0), 0, $aReturn)
EndFunc

Func _GUICtrlListView_SaveTxtEx(ByRef Const $aArray, $sFilePath, $sDelimiter = '|')
    If $sDelimiter = Default Then
        $sDelimiter = '|'
    EndIf
    Local Const $iColumnCount = $aArray[0][1] - 1
    Local $sReturn = ''
    For $i = 1 To $aArray[0][0]
        For $j = 0 To $iColumnCount
            $sReturn &= $aArray[$i][$j]
            If $j < $iColumnCount Then
                $sReturn &= $sDelimiter
            EndIf
        Next
        $sReturn &= @CRLF
    Next
    Local $hFileOpen = FileOpen($sFilePath, $FO_OVERWRITE)
    If $hFileOpen = -1 Then
        Return SetError(1, 0, False)
    EndIf
    FileWrite($hFileOpen, $sReturn)
    FileClose($hFileOpen)
    Return True
EndFunc

I think I've taken the process too long, please help.

:bye:

Data.txt

pixelsearch · August 23, 2022

Hi mucitbey
The following script seems to do it :

#include <Array.au3>
#include <File.au3>
#include <GUIConstantsEx.au3>

Global $aInFile
_FileReadToArray("Data.txt", $aInFile, $FRTA_NOCOUNT)
Global $iLines = UBound($aInFile)

Global $bFile
_FileReadToArray("Users.txt", $bFile, $FRTA_NOCOUNT, "=")

GUICreate("_mucitbey_", 430, 500, -1, -1)
$lv = GUICtrlCreateListView("#|Card ID|[]|Date|Clock|User", 10, 10, 410, 480)

For $i = 0 To $iLines - 1
    $aTemp = StringSplit($aInFile[$i], ", ", $STR_NOCOUNT)
    $user = GetUser($aTemp[1])
    $sLine = $aTemp[0] &"|"& $aTemp[1] &"|"& $aTemp[2] &"|"& $aTemp[3] &"|"& $aTemp[4] &"|"& $user
    GUICtrlCreateListViewItem($sLine, $lv)
    If $aTemp[4] > "08:10:00" And $aTemp[4] < "16:50:00" Then GUICtrlSetBkColor(-1, 0xC0FFFF)

    If $i = 0 Then ; only once (1st line => 1st date => open 1st date txt file)
        Local $sDate = $aTemp[3], $sFilePath = @ScriptDir & '\' & $sDate & '.txt'
        Local $hFileOpen = FileOpen($sFilePath, $FO_OVERWRITE)
        If $hFileOpen = -1 Then Exit MsgBox(0, "FileOpen error", "Date = " & $sDate)
    EndIf
    If $sDate <> $aTemp[3] Then ; If date changed => close old date txt file, then open new date txt file
        FileClose($hFileOpen)
        $sDate = $aTemp[3]
        $sFilePath = @ScriptDir & '\' & $sDate & '.txt'
        $hFileOpen = FileOpen($sFilePath, $FO_OVERWRITE)
        If $hFileOpen = -1 Then Exit MsgBox(0, "FileOpen error", "Date = " & $sDate)
    EndIf
    FileWriteLine($hFileOpen, $sLine)
Next

If IsDeclared("hFileOpen") Then FileClose($hFileOpen)

GUISetState(@SW_SHOW)

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
        GUIDelete()
        Exit
    EndSwitch
WEnd

Func GetUser($ID)
    Local $iIndex = _ArraySearch($bFile, $ID)
    If $iIndex <> -1 Then
        Return $bFile[$iIndex][1]
    Else
        Return "Undefined ID"
    EndIf
EndFunc

It generated 75 text files on my computer (named "30.05.2022.txt" to "12.08.2022.txt") each file containing the corresponding records of the day.

Edit: a possible Users.txt (partial) :

00000069=User 69
00000021=Name of user 21
00000062=I am user 62
00000065=Here is 65

Partial listview :

mucitbey.png.701115f26bb5f6037887c06a5d6b89f7.png

15th July 2022 in "Data.txt" (4 lines)

001,00000034,00,15.07.2022 08:04:20
001,00000054,00,15.07.2022 08:57:42
001,00000055,00,15.07.2022 19:56:59
001,00000034,00,15.07.2022 19:57:01

File : "15.07.2022.txt" (4 lines)

001|00000034|00|15.07.2022|08:04:20|Undefined ID
001|00000054|00|15.07.2022|08:57:42|Undefined ID
001|00000055|00|15.07.2022|19:56:59|Undefined ID
001|00000034|00|15.07.2022|19:57:01|Undefined ID

Edited August 23, 2022 by pixelsearch
added output example, when date = 15th July 2022

mucitbey · August 24, 2022

Sometimes our minds can't come up with ideas. Right here, broad-minded and intelligent friends like you come to your aid. Thanks once again #pixelsearch

:frantics: :bye:

Edited August 24, 2022 by mucitbey

Zedna · August 24, 2022

@pixelsearch

nice, here is your script little optimized, not needed "If $i = 0 Then ..."

#include <Array.au3>
#include <File.au3>
#include <GUIConstantsEx.au3>

Global $aInFile
_FileReadToArray("Data.txt", $aInFile, $FRTA_NOCOUNT)
Global $iLines = UBound($aInFile)

Global $bFile
_FileReadToArray("Users.txt", $bFile, $FRTA_NOCOUNT, "=")

GUICreate("_mucitbey_", 430, 500, -1, -1)
$lv = GUICtrlCreateListView("#|Card ID|[]|Date|Clock|User", 10, 10, 410, 480)

$sDate = ''
$hFileOpen = 0

For $i = 0 To $iLines - 1
    $aTemp = StringSplit($aInFile[$i], ", ", $STR_NOCOUNT)
    $user = GetUser($aTemp[1])
    $sLine = $aTemp[0] &"|"& $aTemp[1] &"|"& $aTemp[2] &"|"& $aTemp[3] &"|"& $aTemp[4] &"|"& $user
    GUICtrlCreateListViewItem($sLine, $lv)
    If $aTemp[4] > "08:10:00" And $aTemp[4] < "16:50:00" Then GUICtrlSetBkColor(-1, 0xC0FFFF)

    If $sDate <> $aTemp[3] Then ; If date changed => close old date txt file, then open new date txt file
        If $hFileOpen <> 0 Then FileClose($hFileOpen)
        $sDate = $aTemp[3]
        $sFilePath = @ScriptDir & '\' & $sDate & '.txt'
        $hFileOpen = FileOpen($sFilePath, $FO_OVERWRITE)
        If $hFileOpen = -1 Then Exit MsgBox(0, "FileOpen error", "Date = " & $sDate)
    EndIf
    FileWriteLine($hFileOpen, $sLine)
Next

If $hFileOpen <> 0 Then FileClose($hFileOpen)

GUISetState(@SW_SHOW)

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
        GUIDelete()
        Exit
    EndSwitch
WEnd

Func GetUser($ID)
    Local $iIndex = _ArraySearch($bFile, $ID)
    If $iIndex <> -1 Then
        Return $bFile[$iIndex][1]
    Else
        Return "Undefined ID"
    EndIf
EndFunc

pixelsearch · August 25, 2022

@mucitbey I just read your new thread where you're asking this :

2 hours ago, mucitbey said:

The file name is 25.08.2022.txt. Converting the file names in this form to 20220825.txt.

In case you want to apply this request to Zedna's script just above, you could do it by commenting this line...

; $sFilePath = @ScriptDir & '\' & $sDate & '.txt'

...and replace it with this code :

$sDate2 = (StringLen($sDate) = 10) ? $sDate : ('0' & $sDate) ; '1.08.2022' => '01.08.2022'

$sFilePath = @ScriptDir & '\' & _
    StringRight($sDate2, 4) & StringMid($sDate2, 4, 2) & StringLeft($sDate2, 2) & _
    '.txt' ; "20220801.txt" or "20220825.txt"

As date day isn't always 2 characters long in 'Data.txt' (date can be '1.08.2022' or '25.08.2022') then we have to force it to always be 2 characters long in the output file name.

A RegEx code should be possible too, I'll leave it to RegEx experts

mikell · August 25, 2022

4 hours ago, pixelsearch said:

As date day isn't always 2 characters long in 'Data.txt'

Hum hum

$file1 = @scriptdir & "\01.08.2022.txt"
$file2 = @scriptdir & "\1.08.2022.txt"
$file3 = @scriptdir & "\1.8.2022.txt"
$file4 = @scriptdir & "\1/8/2022.txt"

$res1 = Execute("'" & StringRegExpReplace($file1, '(\d?\d)\D(\d?\d)\D(\d{4})(\.txt)$', "$3' & StringFormat('%02i', '$2') & StringFormat('%02i', '$1') & '$4' & '") & "'")
Msgbox(0,"1", $res1)
$res2 = Execute("'" & StringRegExpReplace($file2, '(\d?\d)\D(\d?\d)\D(\d{4})(\.txt)$', "$3' & StringFormat('%02i', '$2') & StringFormat('%02i', '$1') & '$4' & '") & "'")
Msgbox(0,"2", $res2)
$res3 = Execute("'" & StringRegExpReplace($file3, '(\d?\d)\D(\d?\d)\D(\d{4})(\.txt)$', "$3' & StringFormat('%02i', '$2') & StringFormat('%02i', '$1') & '$4' & '") & "'")
Msgbox(0,"3", $res3)
$res4 = Execute("'" & StringRegExpReplace($file4, '(\d?\d)\D(\d?\d)\D(\d{4})(\.txt)$', "$3' & StringFormat('%02i', '$2') & StringFormat('%02i', '$1') & '$4' & '") & "'")
Msgbox(0,"4", $res4)

Edited August 25, 2022 by mikell
tiny correction - there may be a date in the filepath ...

pixelsearch · August 25, 2022

2078939702_FishmongertheSoulfulcat.jpeg.fe7ba96551abd945663dddf9b8d2a83a.jpeg

A Fishmonger feeding a Soulful cat with a Redfish (RegExFish species) :thumbsup:

Sign In

Split data in text file into text files based on dates

Recommended Posts

mucitbey

Link to comment

Share on other sites

pixelsearch

Link to comment

Share on other sites

mucitbey

Link to comment

Share on other sites

Zedna

Link to comment

Share on other sites

pixelsearch

Link to comment

Share on other sites

mikell

Link to comment

Share on other sites

pixelsearch

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta