Jump to content

Split data in text file into text files based on dates


Recommended Posts

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

Link to post
Share on other sites

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 by pixelsearch
added output example, when date = 15th July 2022
Link to post
Share on other sites

@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

 

Link to post
Share on other sites

@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 :)

Link to post
Share on other sites
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 by mikell
tiny correction - there may be a date in the filepath ...
Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...