Page 1 of 1 1
Topic Options
#210133 - 2015-03-17 10:24 AM Make script which reads txt-file delimited by tab, to make new file sorted by date-field
Curious Offline
Getting the hang of it

Registered: 2004-02-16
Posts: 61
I have a txt-file, containing lots of data from our HRM-system. I want to process this file (it is delimited with tabulators) to make a new file, only containing – lets say the second, third, fourth, twelvth and the twentifirst column. I want the file delimited by ‘,’ or ‘;’.

I also would like the new file to be sorted by the date-field from the twentifirst column,( formatted like dd.mm.yyyy) newest lines first. The file must only contain the lines with data from the current or last couple of weeks (maybe the last month).

Anybody got any good pointers to do this. Can it be done?


Edited by Curious (2015-03-17 10:42 AM)
_________________________
I was placed on earth to do a job. Right now I have so much to do, I will never die..

Top
#210134 - 2015-03-17 11:33 AM Re: Make script which reads txt-file delimited by tab, to make new file sorted by date-field [Re: Curious]
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
Hi there,

first hint: readCSV() - reads a csv and returns array
_________________________



Top
#210137 - 2015-03-17 12:58 PM Re: Make script which reads txt-file delimited by tab, to make new file sorted by date-field [Re: Jochen]
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
Month calculation are the easiest. To calculate week the logic is not as easy why you could look into something like serialdate()
_________________________
!

download KiXnet

Top
#210141 - 2015-03-17 02:35 PM Re: Make script which reads txt-file delimited by tab, to make new file sorted by date-field [Re: Jochen]
Curious Offline
Getting the hang of it

Registered: 2004-02-16
Posts: 61
Can readcsv be used when delimiter is tabulator? If that's so, will one then use the chr-value for tabulator to identify it?
_________________________
I was placed on earth to do a job. Right now I have so much to do, I will never die..

Top
#210142 - 2015-03-17 02:37 PM Re: Make script which reads txt-file delimited by tab, to make new file sorted by date-field [Re: Lonkero]
Curious Offline
Getting the hang of it

Registered: 2004-02-16
Posts: 61
I also think month-calc is the easiest. Anyway, I've got a routine that can manage this. But my initial problem is to read only the columns of interest to insert this info into a new file for further processing.
_________________________
I was placed on earth to do a job. Right now I have so much to do, I will never die..

Top
#210145 - 2015-03-17 03:54 PM Re: Make script which reads txt-file delimited by tab, to make new file sorted by date-field [Re: Curious]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
I do tons of data manipulation using the following tools/UDFs
 Code:
; Requires FileIO, CSV, and TimeDiff UDFs

; Pseudo-code - untested - CONFIRM ALL COMMAND SYNTAX!!

Dim $aOut[6]		; need 7 fields to output - change as desired

$ = Open(2, '.\dstfile.csv', 5)

;Load the source data into an array
$aSrcData = FileIO('.\srcfile.txt', 'R')

; Read each line, convert it to an array
For Each $Line in $aSrcData

  ; break line into array based on tabs
  $aLine = Split($Line, Chr(9))

  ; Re-init the output array
  ReDim $aOut[6]

  ; Load the 7 desired output fields from the input line array
  ; Change the number of fields and field IDs as needed  
  $aOut[0] = $aLine[21] ; date field
  $aOut[1] = $aLine[3]
  $aOut[2] = $aLine[4]
  $aOut[3] = $aLine[7]
  $aOut[4] = $aLine[8]
  $aOut[5] = $aLine[9]
  $aOut[6] = $aLine[12]

  ; Now is the time to do any data manipulation to your data...

  ; reformat the date value to use the standard format YYYY/MM/DD
  $aTmp = Split($aOut[0], '.')
  $aOut[0] = $aTmp[2] + '/' + $aTmp[1] + '/' + $aTmp[0]

  ; Get age of record in Days
  $Age = TimeDiff($aOut[0], , 'D')

  ; Process this line if it is less than 30 days old. $Days will contain a decimal value
  ; containing the age as whole and fractional days. (ie 3 days 8 hours will be 3.333)
  If $Age <= 30
    ; Now that you have the OUT array for the current line, output it as CSV
    $Rc = WriteLine(2, CSV($aOut))
  EndIf
Next
The code uses FileIO() to load the source data into an array. From there, each line is processed, being split on TABs into a working array ($aLine). Each field of this array represents a numbered column - just remember that these columns begin with zero! Transfer the column numbers you want into your Out ($aOut) array, which will be easier to work with than trying to manipulate all the source columns. It also allows you to use CSV() to convert the array directly to a CSV format record.

TimeDiff returns the difference in seconds between two dates. The second date defaults to "now" so is not specified, and the format parameter requests the value be returned in Days. From there, it's easy to select records based on age.

The CSV() UDF takes an array and converts it to a CSV record that is written to an output file. Quick & dirty, you can open this directly in Excel and use all kinds of sorting and filtering operations. I often use Kix to gather, manipulate, and create CSV files and use Excel to sort/filter, just because it's often easier than writing the code. ;\)

Once the output file is created, you can reload it using FileIO and use one of the available UDFs to sort the array if you wish to do everything within Kix. Then use FileIO(file, 'W', Array) to write the sorted array back to the file. It's a little more processing to load/sort/save but much easier to code than trying to maintain the output entirely in an array to sort before being written to a file.

There's ONE CRITICAL requirement - your source data MUST have a consistent number of columns, and tabs can only be used as delimiters!

Glenn
_________________________
Actually I am a Rocket Scientist! \:D

Top
#210148 - 2015-03-18 03:49 AM Re: Make script which reads txt-file delimited by tab, to make new file sorted by date-field [Re: Curious]
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
Readcsv() if you Read the accompanied header will clearly explain how to use it. Including how to define the delimeter.
_________________________
!

download KiXnet

Top
#210160 - 2015-03-24 03:44 PM Re: Make script which reads txt-file delimited by tab, to make new file sorted by date-field [Re: Glenn Barnas]
Curious Offline
Getting the hang of it

Registered: 2004-02-16
Posts: 61
Ive built my script on the basis of your suggestion Glenn.

My sourcefile contains a field like the AD-field samaccounname. Is there a way I can do a check in ad against the value that now is in the variable $aOut[3]?

I get an error when I try something like

 Code:
  ;Get data from AD
;Open ADSystemInfo object
$objSysInfo = CreateObject("ADSystemInfo")
;Get the username from AD.
$strUser 	= $objSysInfo.$aOut[3]
;Get the users deatils using LDAP.
$objUser	= GetObject("LDAP://" + $strUser)
;Get the users SAMAccountName
$strSam 		= $objUser.sAMAccountName



I only want the lines with samaccountnames that isn't already in AD to be written to my new files.
_________________________
I was placed on earth to do a job. Right now I have so much to do, I will never die..

Top
#210161 - 2015-03-24 05:00 PM Re: Make script which reads txt-file delimited by tab, to make new file sorted by date-field [Re: Curious]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
You can't reference it that way.. you'll probably need to use the EXECUTE statement, which sometimes required a bit of VooDoo to work just right. ;\) I usually make a call using fnLDAPQuery, which allows you to pass your variable to the query string. That function requires a bit of effort to extract your data, but it's still easier than most other methods.

Glenn
_________________________
Actually I am a Rocket Scientist! \:D

Top
#210164 - 2015-03-24 07:43 PM Re: Make script which reads txt-file delimited by tab, to make new file sorted by date-field [Re: Glenn Barnas]
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
you could use nametranslate or translatename or whatever...

here:
http://www.kixtart.org/forums/ubbthreads.php?ubb=showflat&Number=8300
_________________________
!

download KiXnet

Top
Page 1 of 1 1


Moderator:  Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart 
Hop to:
Shout Box

Who's Online
1 registered (Allen) and 466 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

Generated in 0.061 seconds in which 0.022 seconds were spent on a total of 13 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org