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

Question

question

ChristianBahnsenmilaccount-1323 asked Mar 26, '21 AndreasBaumgarten edited Feb 16, '22

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

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

windows-server-powershell

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2021-03-26T21:11:54.437Z

AndreasBaumgarten answered Mar 26, '21 AndreasBaumgarten edited Feb 16, '22

Hi @ChristianBahnsenmilaccount-1323 ,

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

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BenjaminLeonard-3341 · Feb 16 at 01:31 AM

what a fantastic explanation, thank you!

0 ·

question