powershell -- how do I create and populate a DataTable from a CSV file?

Question

I want to try to do a bulk insert to a sql table.

I found an article at bulk-copy-data-sql-server-powershell

that discusses using a DataTable as a source. I've never worked with DataTables before.

I've saved the data as a CSV that has the same column names as the destination table.

So it looks like I need to create the DataTable first:

$a = New-Object System.Data.DataTable
$a | Get-Member

But when I run Get-Member it tells me "You must specify an object for the Get-Member cmdlet." I thought the New-Object statement would have created an object.

Then I need to populate the object. I was hoping something like

Get-Content filename.csv could be piped into the DataTable.

Do I need to define columns for the DataTable first?

Then how can I populate the DataTable from the CSV?

Thanks in advance for any assistance.

Christian Bahnsen

Answer 1

Hi @Christian Bahnsen -- .mil account ,

you have to define the specify the Data Table columns before entering data to the Data Table.

Maybe this helps:

# Define the DataTable Columns  
$table = New-Object system.Data.DataTable 'TestDataTable'  
$newcol = New-Object system.Data.DataColumn FirstName,([string]); $table.columns.add($newcol)  
$newcol = New-Object system.Data.DataColumn LastName,([string]); $table.columns.add($newcol)  
$newcol = New-Object system.Data.DataColumn ID,([int]); $table.columns.add($newcol)  
  
# Add a DataTable row  
$row = $table.NewRow()  
$row.FirstName= ("Bill")  
$row.LastName= ("Gates")  
$row.ID= [int](123456)  
$table.Rows.Add($row)   
  
# Get data from csv  
$file = "./Junk/user.csv"   
$users = Import-CSV $file -delimiter ";"   
$users | ForEach-Object {  
    $row = $table.NewRow()  
    $row.FirstName= ($_.FirstName)  
    $row.LastName= ($_.LastName)  
    $row.ID= [int]($_.ID)  
    $table.Rows.Add($row)  
    }  
  
# Get the data out  
$table  

And here the CSV file content:

FirstName;LastName;ID  
Steve;Ballmer;12346  
Lucky;Luke;12347  
Peter;Pan;12348  

----------

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten