I was trying to import bulk excel file using the PowerShell coding

Question

I was trying to import bulk excel file using the PowerShell scripts coding, my destination is SQL Server, In my spreadsheets there are different data and each columns have has datatypes, how can I declare the data type in PowerShell script, based on the column from the excel sheet including null cells.

Answer 1

Hi Pragash_P,

Greetings! Thank you for posting to Microsoft Community.

You can use below cmd to change the data type.

[date type]$VariableName=$VariableName

For Example:

=================================

[string]$AAA="123"

[int]$AAA=$AAA

$AAA.GetType()
=================================

Best Regards,

Snow Lu

Answer 2

Hi Snow,

Here with I'm attaching my sample data and the SQL script with the Excel file, using this SQL query I'm sending the data to the DB using PowerShell script. kindly advise on this, it will be much appreciated.

SQL Script:

USE [DB_Data]

GO

/****** Object: Table [dbo].[HF_Data] Script Date: 01/23/2023 16:36:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[HF_Data](

[Ticket ID] [nvarchar](255) NULL, 

[Date] [datetime] NULL, 

[Created At] [datetime] NULL, 

[City of Operations] [nvarchar](255) NULL, 

[MFG Date - PC] [nvarchar](255) NULL, 

[Exp Date - PC] [nvarchar](255) NULL, 

[Amount] [float] NULL, 

[Classification] [nvarchar](255) NULL, 

[Closed Date] [datetime] NULL, 

) ON [PRIMARY]

GO

Sample Data:

Answer 3

For your reference:

$FolderPath = '.';

$CsvFile = 'Sample.csv'; 

$XlsxFile = 'Sample.xlsx'; 

$Server = '.\SQLServer2K16'; 

$Database = 'Name'; 

$ServerConnection = "Data Source='$Server';Integrated Security=true;Initial Catalog=$Database"; 

$Table = 'dbo.Sample' 

function Import-Excel ($FolderPath, $XlsxFile, $Server, $Database, $Table) { 

  # Create an Excel workbook… 

    $Excel = New-Object –ComObject Excel.Application; 

    $Workbook = $Excel.WorkBooks.Open((Join-Path –Path (Convert-Path –Path $FolderPath) –ChildPath $XlsxFile)); 

    $WorkSheet = $Workbook.WorkSheets.Item(1);  

    $StartRow = 2; # …ignore headers… 

    # Insert into a System.Data.DataTable… 

    $DataTable = New-Object –TypeName System.Data.DataTable; 

    $null = $DataTable.Columns.Add('TicketID', 'System.String'); 

    $null = $DataTable.Columns.Add('Date', 'System.DateTime'); 

    $null = $DataTable.Columns.Add('CreatedAt', 'System.DateTime'); 

    $null = $DataTable.Columns.Add('CityofOperations', 'System.String'); 

    $null = $DataTable.Columns.Add('Job', 'System.String'); 

    $null = $DataTable.Columns.Add('MFGDatePC', 'System.String'); 

    $null = $DataTable.Columns.Add('ExpDatePC', 'System.String'); 

    $null = $DataTable.Columns.Add('Amount', 'System.Single'); 

    $null = $DataTable.Columns.Add('Classification', 'System.String'); 

    $null = $DataTable.Columns.Add('ClosedDate', 'System.DateTime'); 

    # Load the DataTable… 

    do { 

        $TicketID = $WorkSheet.Cells.Item($StartRow, 1).Value(); 

        $Date = $WorkSheet.Cells.Item($StartRow, 2).Value(); 

        $CreatedAt = $WorkSheet.Cells.Item($StartRow, 3).Value(); 

        $CityofOperations = $WorkSheet.Cells.Item($StartRow, 4).Value(); 

        $MFGDatePC = $WorkSheet.Cells.Item($StartRow, 6).Value(); 

        $ExpDatePC = $WorkSheet.Cells.Item($StartRow, 7).Value(); 

        $Amount = $WorkSheet.Cells.Item($StartRow, 8).Value(); 

        $Classification = $WorkSheet.Cells.Item($StartRow, 9).Value(); 

        $ClosedDate = $WorkSheet.Cells.Item($StartRow, 10).Value(); 

        $Row = $DataTable.NewRow(); 

        $Row.TicketID = ${Ticket ID} 

        $Row.Date = $Date 

        $Row.CreatedAt = ${Created At} 

        $Row.CityofOperations = ${City of Operations} 

        $Row.MFGDatePC = ${MFG Date - PC} 

        $Row.ExpDatePC = ${Exp Date - PC} 

        $Row.Amount = $Amount 

        $Row.Classification = $Classification 

        $Row.ClosedDate = ${Closed Date} 

        $DataTable.Rows.Add($Row); 

        $StartRow++;     

    } while ($WorkSheet.Cells.Item($StartRow, 1).Value() -ne $null); #…until a gap in values… 

    $Excel.Quit(); # …then exit… 

    # Bulk load it… 

    $BulkCopy = New-Object –TypeName Data.SqlClient.SqlBulkCopy –ArgumentList $ServerConnection; 

    $BulkCopy.DestinationTableName = $Table; 

    $BulkCopy.WriteToServer($DataTable); 

};

Answer 4

Hi Snow,

I tried using the code you have shared, but it is not inserting values in it. Could you help me out in this! . I have attached the screenshot.