Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am trying to import a CSV file into a database with PowerShell but so far I have no luck.

This is my code (based on this source):

Start-job   -Name 'ImportingData' `
        -InitializationScript {Ipmo DataTableConnect -Force -DisableNameChecking} `
        -ScriptBlock {$DataImport = Import-Csv -Header ("heading01","heading02","heading03") -Path $path
                $DataTable = Out-DataTable -InputObject $DataImport
                Write-DataTable -ServerInstance SERVER -Database DB_NAME -TableName TB_NAME -Data $DataTable
                }

Where $path = C:\Users\Administrator\Desktop\TestFolder\testfile.txt DataTableConnect is a module which includes this script and .org/2119 script.

I am not really sure where my issue is, all individual parts seem to work just fine the only thing that I think might be the problem is that $DataTable returns a bunch of system.data.datarow's. However other stack-exchange Q&A say this is expected.

Thanks, any help is greatly appreciated.


EDIT01: I am not getting any errors or nothing. I wrote the code in a .ps1 file and then run it through PowerShell 2.

I just noticed that if I run it without the job, (thus the code looking like this):

Ipmo DataTableConnect -Force -DisableNameChecking
$DataImport = Import-Csv -Header ("heading01","heading02","heading03") -Path $path
$DataTable = Out-DataTable -InputObject $DataImport
Write-DataTable -ServerInstance SERVER -Database DB_NAME -TableName TB_NAME -Data $DataTable

I get quite a bit off errors:

Write-DataTable : Write-DataTable :System.Management.Automation.MethodInvocationException: Exception calling "WriteToServer" with "1" argument(s): "The given value of type String from the data source cannot be converted to type date of the specified target column." ---> System.InvalidOperationException: The given value of type String from the data source cannot be converted to type date of the specified target column. ---> System.FormatException: Failed to convert parameter value from a String to a DateTime. ---> System.FormatException: String was not recognized as a valid DateTime.

All the columns in the text file are valid and corresponding to the data type of the sql column. Is it giving me this error because it is importing everything as text? If so, how would I avoid this?


EDIT02: I changes the data_types of all the columns to text (or varchar s) and indeed it does import correctly now. It thus seems it does import the columns/data as (plain) text.

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.