Help Needed in Reading lines

Hello Friends,

I am trying to import my text file data into sqlserver using powershell. I am trying with heavy file which is 10gig records. In order to start with testing, i tried with just three records.

Sample Table:

Code:

create table PowerTest(Id int primary key identity(1,1),Name varchar(100),Age int)

Script to do the insert with bulk,

Code:

#################################################### 
# 
# PowerShell CSV to SQL Import Script 
# 
#################################################### 
 
# Database variables 
$sqlserver = "142.160.280.52" 
$database = "Sample" 
$table = "SampleTest" 
$UserName = 'sa'
$Password = 'Test'
 
# CSV variables;  
$csvfile = "E:\PowerShell\Data.txt" 
$csvdelimiter = "|" 
$firstrowcolumnnames = $false 
 
 
Write-Output "Script started..." 
$elapsed = [System.Diagnostics.Stopwatch]::StartNew() 
 
# 100k worked fastest and kept memory usage to a minimum 
$batchsize = 100000 
 
# Build the sqlbulkcopy connection, and set the timeout to infinite 
$connectionstring = "Server=$sqlserver;Database=$database;User ID=$UserName;Password=$Password;Trusted_Connection=false;"
# $connectionstring = "Data Source=$sqlserver;Integrated Security=true;Initial Catalog=$database;" 
$bulkcopy = new-object ("Data.SqlClient.Sqlbulkcopy") $connectionstring 
$bulkcopy.DestinationTableName = $table 
$bulkcopy.bulkcopyTimeout = 0 
$bulkcopy.batchsize = $batchsize 
$bulkcopy.EnableStreaming = 1 
  
# Create the datatable, and autogenerate the columns. 
$datatable = New-Object "System.Data.DataTable" 
 
# Open the text file from disk 
$reader = new-object System.IO.StreamReader($csvfile) 
$line = $reader.ReadLine() 

 $dataTable = New-Object System.Data.DataTable            

$dataTable.Columns.Add("Name")  | Out-Null
$dataTable.Columns.Add("Age", [int]) | Out-Null 
  # Read in the data, line by line 
    while (($line = $reader.ReadLine()) -ne $null)  {
       
			 $data = $line.Split("|")
			 $row = $datatable.NewRow()
			 $row.Item("Name") = $data[0]
			 $row.Item("Age") = [int]$data[1]
			 $datatable.Rows.Add($row) 
 
 
        # Once you reach your batch size, write to the db,  
        # then clear the datatable from memory 
        $i++; if (($i % $batchsize) -eq 0) { 
        $bulkcopy.WriteToServer($datatable) 
        Write-Output "$i rows have been inserted in $($elapsed.Elapsed.ToString())."; 
        $datatable.Clear() 
        } 
    }  
 
# Close the CSV file 
$reader.Close() 
 
    # Add in all the remaining rows since the last clear 
    if($datatable.Rows.Count -gt 0) { 
        $bulkcopy.WriteToServer($datatable) 
        $datatable.Clear() 
    } 
 
# Sometimes the Garbage Collector takes too long. 
[System.GC]::Collect()

Sample data

Code:

Name1|10
Name2|20
Name3|30

It's putting age values in Name column and makes age column is null. not sure why it skips the name column. Any suggestions please

I have pretty much no experience with SQL and not a programmer (done some VBA long time ago). So until someone experienced replies, think on these while not taking me too seriously.

Questions:

Code:

$datatable = New-Object "System.Data.DataTable"

then 2 lines later

Code:

$dataTable = New-Object System.Data.DataTable

Are these correct? One with "" the other not, with T of table capitalized and small

Also, your table is (Id int primary key identity(1,1),Name varchar(100),Age int). Would it require Id to be entered manually or would it be automatic? You start entering columns with Name, perhaps an Id column is needed at the start.

Code:

$row = $datatable.NewRow()
--Id column here?--
             $row.Item("Name") = $data[0]
             $row.Item("Age") = [int]$data[1]

than you guys and am able to achieve by myself.

Hi Mellon, i am also c# programmer. I am doing experiment on how powershell reading works vs c# file reading. fastest way to achieve. for one million records to dump it in sqlserver i am able to achieve in 4 minutes. Please let me know if you know is there any fastest way other than this. I don't need to use SSIS/bulkInsert. I am experimenting with front end technilogies