CODEWITHSUNDEEP
sql-serverpowershellcsv
Prashant Deore
Prashant Deore

Reputation: 57

Load CSV by matching table column to SQL using PowerShell

My requirement is to load system generated CSV file into an SQL table. Below I'm providing the CSV file, table query and my code.

Below are the CSV files abc.csv and xyz.csv which column comes changing by column every time like in the second file as you can see last two column shuffled.

id,Name,name,Shift,Day,Night
1,ert,sdf,08/21/2017 Day,2,-
2,wer,asdf,08/21/2017 Day,-,1
3,rty,adsf,08/21/2017 Day,1,-
4,yui,adsf,08/21/2017 Day,-,-
5,qwe,asdf,08/21/2017 Day,-,-
6,ret,asdf,08/21/2017 Day,-,-
7,chh,asdf,08/21/2017 Day,-,-
8,sdf,cxvv,08/21/2017 Day,1,-

id,Name,name,Shift,Night,Day
1,ert,sdf,08/21/2017 Day,-,2
2,wer,asdf,08/21/2017 Day,1,-
3,rty,adsf,08/21/2017 Day,-,1
4,yui,adsf,08/21/2017 Day,-,-
5,qwe,asdf,08/21/2017 Day,-,-
6,ret,asdf,08/21/2017 Day,-,-
7,chh,asdf,08/21/2017 Day,-,-
8,sdf,cxvv,08/21/2017 Day,-,1

Below is the create table query to load this data in SQL:

CREATE DATABASE MES;
USE MES;
CREATE TABLE MESdata
(
    Id              int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    Employee_id     int NOT NULL,
    First_Name      varchar(20) NOT NULL,
    Last_Name       varchar(20) NOT NULL,
    Shift           varchar(20) NOT NULL,
    Day             int NULL,
    Night           int NULL
);

Below is my attempt in PowerShell using Out-datatable.ps1 and write-datatable.ps1:

. C:\ReadCSV\Out-datatable.ps1;
. C:\ReadCSV\write-datatable.ps1;

if (Test-Path Variable:\my_import) {
    Remove-Variable my_import
}

$csvfile    = 'C:\ReadCSV\abc.csv'
$header     = Get-Content 'C:\ReadCSV\abc.csv' | select -First 1
$sqlTable   = 'MESdata'
$DataSource = 'HOST\INSTANCE'
$DataBase   = 'MES'
$my_import  = Import-Csv -UseCulture -Header $header $csvfile |
              select @{Label="Employee_id";Expression={$_."id"}},
                     @{Label="First_Name";Expression={$_."Name"}},
                     @{Label="Last_Name";Expression={$_."name"}},
                     @{Label="Shift";Expression={$_."Shift"}},
                     @{Label="Day";Expression={$_."Day"}},
                     @{Label="Night";Expression={$_."Night"}} |
              Out-DataTable    
Write-DataTable -ServerInstance $DataSource -Database $DataBase -TableName $sqlTable -Data $my_import
#$ConnectionString ='Data Source={0}; Database={1}; Trusted_Connection=True;' -f $DataSource,$DataBase
#$bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($ConnectionString)
#$bulkCopy.DestinationTableName = $sqlTable
#$bulkCopy.WriteToServer($my_import)

I got this first error:

Exception calling "WriteToServer" with "1" argument(s): "Column 'Employee_id'
does not allow DBNull.Value."
At C:\ReadCSV\stackoverflow.ps1:25 char:1
+ $bulkCopy.WriteToServer($my_import)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

What should I do? Do I need to change the datatype of table fields but I don't want like that. But if I replace all not null to null then the script executed without error but with all NULL data.

Upvotes: 1

Views: 1422

Answers (1)

Mark Wragg
Mark Wragg

Reputation: 23355

With a well formed CSV file you don't need to manually specify the -Header line, PowerShell will figure this out automatically. However your file has a duplicate column name (PowerShell is case sensitive so Name is duplicate of name) and you will get an error like:

The member "Name" is already present

To work around this you'll either need to rename one of these columns first to make them unique (and then drop the -Header part of your code), or alternatively you could use the -Header property to manually specify the full header line using whatever property names you want for each column. You do this using an array of strings e.g:

$my_import = Import-CSV –useCulture -Header 'Employee_id','First_Name','Last_Name','Shift','Day','Night' $csvfile | Out-datatable

By doing this you also then do not need to do the Select part of your code to rename the column names, as they will be set correctly to start with.

Upvotes: 2

Related Questions