Import CSV using PowerShell: date conversion

Question

I'm importing CSV files to the MS SQL, and in different CSV files I have different date types. My code:

## Input Variables
$csvPath = "F:\Test\table.csv"
$csvDelimiter = ";"
$serverName = "name"
$databaseName = "dbname"
$tableSchema = "dbo"
$tableName = "dates"
$encoding = "windows-1251"



Import-Csv -Path $csvPath -encoding $encoding -Delimiter $csvDelimiter | Write-SqlTableData -ServerInstance $serverName -DatabaseName $databaseName -SchemaName $tableSchema -TableName $tableName -Force

And, for example, dates in my CSVs are like:

12JAN2021:18:03:41.000000

The problem here is that month written by the letters

or

17.04.2021

The problem here is that SQL read this like "mm.dd.yyyy" but it's "dd.mm.yyyy"

How can I improve my code so it can automatically read date correctly and write it to my date or datetime field in the destination table?

Thanks so much!

update CSV example:

product;product_id;product_nm;dttm
220;text;some text;12JAN2021:18:03:41.000000
220;text;some text;1JAN2021:18:03:41.000000
564;text;some text;16JAN2021:18:03:41.000000

Answer 1

I don't believe there is an "automated way" of parsing your CSVs if they all have a different DateTime format. I believe you would need to inspect each file to see if the format is valid (cast [datetime] directly to the string) or if they have a format that needs to be parsed.

In example, both provided dates on your question, need to be parsed and can be parsed with [datetime]::ParseExact(..):

[datetime]::ParseExact(
    '12JAN2021:18:03:41.000000',
    'ddMMMyyyy:HH:mm:ss.ffffff',
    [cultureinfo]::InvariantCulture
).ToString('MM.dd.yyyy')

[datetime]::ParseExact(
    '17.04.2021',
    'dd.MM.yyyy',
    [cultureinfo]::InvariantCulture
).ToString('MM.dd.yyyy')

If you need help "updating" the DateTime column from your CSV you would need to provide more details on that.

---

In example, the CSV provided in the question can be updated as follows:

# Here you would use this instead:
# $csv = Import-Csv path/to/csv.csv -Delimiter ';'
$csv = @'
product;product_id;product_nm;dttm
220;text;some text;12JAN2021:18:03:41.000000
220;text;some text;1JAN2021:18:03:41.000000
564;text;some text;16JAN2021:18:03:41.000000
'@ | ConvertFrom-Csv -Delimiter ';'

foreach($line in $csv)
{
    $line.dttm = [datetime]::ParseExact(
        $line.dttm,
        'dMMMyyyy:HH:mm:ss.ffffff',
        [cultureinfo]::InvariantCulture
    ).ToString('MM.dd.yyyy')
}

Now if we inspect the CSV it would look like this:

PS /> $csv | Format-Table

product product_id product_nm dttm
------- ---------- ---------- ----
220     text       some text  01.12.2021
220     text       some text  01.01.2021
564     text       some text  01.16.2021

And all the process in pipeline would look as follows:

Import-Csv -Path $csvPath -Encoding $encoding -Delimiter $csvDelimiter |
ForEach-Object {
    $_.dttm = [datetime]::ParseExact(
        $_.dttm,
        'dMMMyyyy:HH:mm:ss.ffffff',
        [cultureinfo]::InvariantCulture
    ).ToString('MM.dd.yyyy')
    $_
} | Write-SqlTableData.....