Import CSV using PowerShell: date conversion-CodePudding

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

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

CodePudding user response：

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.....