Help Needed in Reading lines

[born2achieve]

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:

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

Script to do the insert with bulk,

#################################################### 
# 
# 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

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

 

[GokAy]

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:

$data[B]t[/B]able = New-Object "System.Data.DataTable"

then 2 lines later

$data[B]T[/B]able = 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.

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

 

[Mellon Head]

Are you using Microsoft SQL Server?

If so, you can use the "Import and Export Data (32-bit)" (or 64-bit, depending on your installation) apps to load the data into your database. They will read from a CSV file, or another database, are menu driven, and are reasonably easy to use. Much easier than Powershell IMHO.

EDIT:

I'm not too familiar with Powershell, but I see a couple of things which may be causing some issues.


I don't see where the $data variable is defined as an array. You're using it as an array, but it looks like it's just a plain string.

Don't get me wrong, I'm no expert. I'm a C# programmer with a passing knowledge of SQL, but I have lots to learn.

 

[born2achieve]

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

 

[Mellon Head]

I'm not sure I can think of a faster way than what you're currently doing. I think it would definitely be slower in C#. You might be able to get it a little faster by using highly optimized C++, but that's a whole other ballgame.