1

I want to write csv file to SQL Server. This is successfully working on a PC having windows 10 with Powershell 5. It is throwing error on a windows server 2012 and windows server 2016 with Povershell 5

Error Msg : Write-SqlTableData : The given value of type String from the data source cannot be converted to type date of the specified target column.

My Code is below :

 $DestServer = "servername01.fqdn,1200"
    $DestInstance = "instance01"
    $DestDatabase = "test"
    $ServerDataDir = "\\servername01\folder1"

$ServInstance = "$DestServer\$DestInstance"
$Data_Dir = "\\servername02.fqdn\c$\datafolder"
$Headers_Dir = $Data_Dir + "\Header files"
$ExclSourcename_Head_File = "$Headers_Dir\ExcelSource_Head.txt"
$ExclSourcename_Fields_File = "$Headers_Dir\ExcelSource_Fields.txt"

Out-DataTable.ps1

Clear-Host
write-host "Processing ExcelSource File" -F DarkBlue -BackgroundColor Cyan
$ExclSourcename_Head = (Get-content "$ExclSourcename_Head_File" -Delim ",").Replace(",","")
$ExclSourcename_Fields = @{}
$ExclSourcename_Fields = (Get-content "$ExclSourcename_Fields_File" -Delim ",").Replace(",","")
$ExclSourcename_File = "$Data_Dir\exclfile*.csv"

#    Find latest ExclSourcename file to be processed
$ExclSourcename_FileIN = (Get-ChildItem "$ExclSourcename_File" | Sort-Object LastWriteTime -Descending | Select-Object -first 1)
$ExclSourcename_Date = $ExclSourcename_FileIN.BaseName.Replace("BK_","")
$ExclSourcename_Formatted_Date = [datetime]::ParseExact($ExclSourcename_Date,"yyyyMMdd",$null).AddDays(-1).ToString("dd/MM/yyyy")
$ExclSourcename_Temp = "C:\temp\ExclSourcenameFile.txt"
(Get-Content -Path "$ExclSourcename_FileIN").Replace(",",".") | Out-File -FilePath "$ExclSourcename_Temp" -Force
##$ExclSourcename_Data = Import-csv -Path "$ExclSourcename_File" -Delim ";" -Header $ExclSourcename_Head
$ExclSourcename_Data = Import-csv -Path "$ExclSourcename_Temp" -Delim ";" -Header $ExclSourcename_Head
$ExclSourcename_Data = $ExclSourcename_Data[1..($ExclSourcename_Data.Count - 1)]
$ExclSourcename_Count = $ExclSourcename_Data.Count
$ExclSourcename_Data | Add-Member -MemberType NoteProperty -Name Distributor -Value "ExcelSource"
$ExclSourcename_Data | Add-Member -MemberType NoteProperty -Name "File Date" -Value $ExclSourcename_Formatted_Date
Write-Host "ExcelSource File imported - $ExclSourcename_Count records found ... " -NoNewline
Write-Host "Converting to Data Table"
$ExclSourcename_DataTable = $ExclSourcename_Data | Select-Object -Property $ExclSourcename_Fields | Out-DataTable
Write-Host "Uploading ExcelSource data to SQL ... " -ForegroundColor White -BackgroundColor Blue -NoNewline
Invoke-Sqlcmd -Database $DestDatabase -ServerInstance "$DestServer\$DestInstance" -Query "Truncate Table [EU_Staging].tblRBL_Data_ExclSourcename"
Write-SqlTableData -DatabaseName $DestDatabase -ServerInstance "$DestServer\$DestInstance" -SchemaName EU_Staging -TableName tblRBL_Data_ExclSourcename -InputData $ExclSourcename_DataTable -Timeout 900
Write-host "done" -ForegroundColor White -BackgroundColor Red
Write-Host "ExcelSource data loaded"
Write-Host "==========" -ForegroundColor DarkGreen -BackgroundColor Green
Remove-Item -Path "$ExclSourcename_Temp" -Force
Remove-Variable ExclSourcename_Data
Remove-Variable ExclSourcename_DataTable
TylerH
  • 20,799
  • 66
  • 75
  • 101
Andleeb Hasan
  • 85
  • 1
  • 5
  • Check if [this](https://stackoverflow.com/questions/50275994/unable-to-convert-string-to-date-type-using-import-csv) helps. – Vivek Kumar Singh Jun 10 '21 at 09:24
  • No, it worked for me with conversion on string column `ParseExact($_.'Order Date',"dd/MM/yyyy",$null).ToString("dd/MM/yyyy")` But my question was ..why I need conversion on windows server 2012/2016 but not on windows pc 7/10 – Andleeb Hasan Jun 10 '21 at 10:17
  • 1
    Why would you take an unambiguous yyyyMMdd formatted date string and convert it to an ambiguous dd/MM/yyyy formatted one? Why not send it as a DateTime object? Probably there's disagreement in Regional Settings between the two computers. – AlwaysLearning Jun 10 '21 at 12:01
  • Yes it's because of regional settings between two servers... – Andleeb Hasan Jun 10 '21 at 12:33

0 Answers0