Loading large JSON file into a Data Table to Bulk Copy into SQL Server Table

• sadie

  Old Hand

  Points: 340

  More actions

  March 5, 2021 at 11:05 pm

  #3854053

  We have a JSON file with 10M records to bulk copy load into a SQL Server table. The Powershell code is below. The issue we are having is the first line seems to hang up, take a long time to execute and freeze the machine. Is there a more efficient way to convert a large JSON file into a data table?

  $results = Get-Content $tempfile | ConvertFrom-Json

  $dt2 = $results | select-object $FieldAttribute

  $dataTable = ConvertTo-DataTable -InputObject $dt2

• frederico_fonseca

  SSCoach

  Points: 16215

  More actions

  March 5, 2021 at 11:49 pm

  #3854055

  try this function https://github.com/RamblingCookieMonster/PowerShell/blob/master/ConvertTo-FlatObject.ps1

   

  most likely you may be able to do

  [xml]$file =get-content $tempfile

  $datatable = convertto-datatable -inputobject (ConvertTo-FlatObject -InputObject $file)

   

• sadie

  Old Hand

  Points: 340

  More actions

  March 5, 2021 at 11:51 pm

  #3854056

  I will try it, thank you.

• sadie

  Old Hand

  Points: 340

  More actions

  March 8, 2021 at 7:25 pm

  #3855076

  Unfortunately, it still appears to freeze the machine and exits powershell without an error after a couple hours.

• frederico_fonseca

  SSCoach

  Points: 16215

  More actions

  March 9, 2021 at 8:06 am

  #3855323

  hum. should not be that slow but possible if the records are big - what's the file size?

   

  is it possible to share the file (even if on private) for me to try it out?

  as well as the ps1 script you are using.