0

I have a bunch of folders with thousands of files from which i generate a .csv file containing the filenames only. These filenames contain a timestamp in the following format: 20180523040545689.

I want to convert these timestamps on the .csv file to a UTC format as follows: 2018-05-23 T 04:05:45.689 TZ +01:00

Here is the current .csv format i have:

20180523040545689, 458796ZUx, WE25, 79peo

Here's the output i want to obtain:

2018-05-23 T 04:05:45.689 TZ +01:00, 458796ZUx, WE25, 79peo

Is there a way to accomplish that in a quick and simple way using a Powershell script?

UPDATE:

I have been testing the script provided by @vonPryz and it gives me the following error:

Exception calling "ParseExact" with "3" argument(s): "String was not recognized as a valid DateTime."

I am pretty sure that it is a problem with cells formatting but i couldn't resolve it yet.

Wade Blackmore
  • 1
  • 4

1 Answers1

1

Start by parsing the timestamp with ParseExact() method to get a DateTime object. As the format seems to be a custom one, you need to tell the parsing method which parts are which. A format string is used for that. To get around time zone issues, use InvariantCulture. Like so,

$dt = [datetime]::parseExact('20180523040545689', 'yyyyMMddHHmmssfff', [cultureinfo]::invariantCulture)

Now that there's a DateTime object, call ToString(). The built-in o format is close, but not exactly. Like so,

$dt.ToString('o')
2018-05-23T04:05:45.6890000

If that's not good enough, take a look at standard formats for better one or use the custom format reference mentioned in the parsing part.

Edit

As per the comment thread, here's a sample solution

# Import the data
$data = Import-Csv C:\Temp\data.csv
# Peek the contents, for illustration purposes only
$data

col0              col1      col2 col3
----              ----      ---- ----
20180523040545689 458796ZUx WE25 76peo
20180524040545689 558796ZUx WE25 77peo
20180525040545689 658796ZUx WE25 78peo
20180526040545689 758796ZUx WE25 79peo

# Create a shorthand for invariant culture. Not strictly necessary, but makes things easier
$ci = [cultureinfo]::invariantCulture

# Loop through the data and change col0 contents.
# Tune the ToString() to provide suitable output
$data | % { $_.col0 = [datetime]::parseExact($_.col0, 'yyyyMMddHHmmssfff', $ci).ToString('o') }

# Show the modified content.
$data

col0                        col1      col2 col3
----                        ----      ---- ----
2018-05-23T04:05:45.6890000 458796ZUx WE25 76peo
2018-05-24T04:05:45.6890000 558796ZUx WE25 77peo
2018-05-25T04:05:45.6890000 658796ZUx WE25 78peo
2018-05-26T04:05:45.6890000 758796ZUx WE25 79peo
vonPryz
  • 22,996
  • 7
  • 54
  • 65
  • @WadeBlackmore I'm sorry, I don't quite understand what you mean. Could you elaborate a bit? – vonPryz Aug 07 '18 at 08:51
  • sorry, i just wanted to ask if you tried the script and if it worked for you, because it didn't work for me at the moment abd as i am a pretty noob in powershell, i still can't figure out how to put this together – Wade Blackmore Aug 07 '18 at 08:57
  • @WadeBlackmore Sure, I did test it. What happens when you try it? Have you got a piece of code that you are trying to run? If that's the case, edit the question and add relevant parts and any error messages, inappropriate results and such on the question. Please don't post those as comments, those are hard to read. – vonPryz Aug 07 '18 at 09:01
  • i didn't figure out how to run the script through the .csv file containing the filenames i have. i wonder if i have to call the full path of the .csv file or if i should just save the script in the same folder as the .csv file ? as i mentioned it before i am a real noob to Powershell scripting. It would be great if you can edit your answer expliciting the full script – Wade Blackmore Aug 07 '18 at 09:23
  • i have been trying the same thing but it didn't work for me, and i think i am formatting the .csv cells in the wrong way, i have updated the question – Wade Blackmore Aug 07 '18 at 11:49
  • i fixed the problem now and it works perfect, thank you so much, can you just tell me how i can export the output to a .csv file ? – Wade Blackmore Aug 08 '18 at 06:57