0

enter code hereI have two files, one is txt and second is CSV. I would like to import txt file into specific column "Start" in csv file, same action will be repeat for last column, but first data need to be converted into unix time.

File1 - unix date

1586890800
1586930400
1586930400
1586959200
1586959200
1586941200
1586959200
1586941200
1586442240

CSV file:

ID (Link);CI;Status;Start;Scheduled End Date Time
126925;TESTA;Scheduled;14.04.2020 19:00;14.04.2020 20:00
127197;TESTB;Scheduled;15.04.2020 06:00;15.04.2020 14:00
158063;TESTAAA;Scheduled;15.04.2020 06:00;15.04.2020 10:00
163314;TESTBAAA;Scheduled;15.04.2020 14:00;22.04.2020 23:00
167023;TESTAdsadasd;Scheduled;15.04.2020 14:00;22.04.2020 23:00
177425;TESTBasdasd;Scheduled;15.04.2020 09:00;15.04.2020 12:00
178658;sadfsdfsd;Scheduled;15.04.2020 14:00;16.04.2020 23:00
179558;TESTBsdfsdfsdf;Scheduled;15.04.2020 09:00;20.04.2020 05:00
179558;TESTCEST;Scheduled;09.04.2020 14:24;20.04.2020 05:00

So far i have something like this:

$Start = Get-Content "C:\Users\start.txt"
Import-Csv "C:\Users\table.csv"  -Delimiter ';' | 
   ForEach-Object { $_.Start = $Start; $_ } | 
  Export-Csv "C:\Users\table.csv" -Delimiter ';' -NoTypeInformation

Result: ID (Link);CI;Status;Start;Scheduled End Date Time

126925;TESTA;Scheduled;1586890800;14.04.2020 20:00
127197;TESTB;Scheduled;1586930400;15.04.2020 14:00
158063;TESTAAA;Scheduled;1586930400;15.04.2020 10:00
163314;TESTBAAA;Scheduled;1586959200;22.04.2020 23:00
167023;TESTAdsadasd;Scheduled;1586959200;22.04.2020 23:00
177425;TESTBasdasd;Scheduled;1586941200;15.04.2020 12:00
178658;sadfsdfsd;Scheduled;1586959200;16.04.2020 23:00
179558;TESTBsdfsdfsdf;Scheduled;1586941200;20.04.2020 05:00
179558;TESTCEST;Scheduled;1586442240;20.04.2020 05:00
majan
  • 125
  • 12
  • Do you actually just want to convert the DateTime values in your CSV file to Unix time? This might help you [In PowerShell, how do I convert DateTime to UNIX time?](https://stackoverflow.com/questions/4192971/in-powershell-how-do-i-convert-datetime-to-unix-time/) – Olaf Apr 21 '20 at 08:01
  • You should not save the output to another file you should save it where you need it - in the CSV file. – Olaf Apr 21 '20 at 08:05
  • @Olaf I have already converted date into Unix time, First part of script doing: 1. Take data from column Start from CSV as normal date 14.04.2020 19:00 2. Convert normal date 14.04.2020 19:00 and save converted date into txt file as 1586890800 3. Add converted date into column :) I am not programmer maybe there is a better why to do so :) If first I took date from this column and convert to another date format so everhting should match, right? Number of rows and so on? am I right? – majan Apr 21 '20 at 08:20

1 Answers1

0

According to the answer of Signal15 in this question In PowerShell, how do I convert DateTime to UNIX time? there's a slightly easy way to format a DateTime object in Unix format.

$CSV = Import-Csv C:\Users\table.csv  -Delimiter ';'
    $CSV | ForEach-Object {
    if ($_.Start){$_.Start = [Math]::Floor([decimal](Get-Date(Get-Date $_.Start).ToUniversalTime()-uformat "%s"))}
    if($_.'Scheduled End Date Time'){$_.'Scheduled End Date Time' = [Math]::Floor([decimal](Get-Date(Get-Date $_.'Scheduled End Date Time').ToUniversalTime()-uformat "%s"))}
}  
$CSV | 
Export-Csv C:\Users\newtable.csv -Delimiter ';' -NoTypeInformation
Olaf
  • 4,690
  • 2
  • 15
  • 23
  • I tired above two code, but for both I have empty files as results, no error during execution. – majan Apr 21 '20 at 08:46
  • Try the second version now, please. – Olaf Apr 21 '20 at 08:51
  • now it is working, what was wrong in previous one? I just added into calculation 7200 to change time zone in result. – majan Apr 21 '20 at 09:16
  • I cannot explain that myself. But I'm glad it's working now. ;-) – Olaf Apr 21 '20 at 09:18
  • is there any way to add validation, for example, if string is missing in column then skip to next row – majan Apr 21 '20 at 10:45
  • If it's not a terminating error it will continue with the next row anyway but of course you can use an if statement to include a condition and an according action. – Olaf Apr 21 '20 at 10:49
  • I added such validation because if string was missing then I get error as get-date function required input cannot be missing, `ForEach-Object { $_.'Scheduled Start Date Time' = If([string]::IsNullOrEmpty($_.'Scheduled Start Date Time')) { $_.'Scheduled Start Date Time' = "" } ElseIf(![string]::IsNullOrEmpty($_.'Scheduled Start Date Time')) { $CEST + [Math]::Floor([decimal](Get-Date(Get-Date $_.'Scheduled Start Date Time').ToUniversalTime()-uformat "%s")) }` – majan Apr 21 '20 at 11:13
  • ... changed the code to check if the cell was empty. – Olaf Apr 21 '20 at 11:53