Windows 7 Forums
Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Help Needed in Reading lines

05 Sep 2015   #1
born2achieve

windows7 64 bit
 
 
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


My System SpecsSystem Spec
.
05 Sep 2015   #2
GokAy

Windows 7 Ultimate x64 SP1
 
 

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]
My System SpecsSystem Spec
05 Sep 2015   #3
Mellon Head

Win 7 Pro x64/Win 10 Pro x64 dual boot
 
 

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.
My System SpecsSystem Spec
.

05 Sep 2015   #4
born2achieve

windows7 64 bit
 
 

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
My System SpecsSystem Spec
05 Sep 2015   #5
Mellon Head

Win 7 Pro x64/Win 10 Pro x64 dual boot
 
 

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.
My System SpecsSystem Spec
Reply

 Help Needed in Reading lines




Thread Tools




Similar help and support threads
Thread Forum
Annoying lines!
Hi, I am trying to remove these annoying lines in Computer, but I canít find them in Windows Style Builder. Hereís a screenshot. In the theme Chameleon 2, the lines do not appear, so itís definitely doable. Rand Marks
Customization
gtx 460 horizontal lines
hi folks I recently upgraded my old 9600 GT for something with a little more oomph - a GTX460. my card has been performing swimmingly until recently - ive noticed strange, wavy horizontal lines/flickering on screen in dark areas - particularly in games. this is usually accompanied by...
Graphic Cards
Flickering Lines
My old GForce GT9600 512 MB was doing fine. Everything was fine, and no flickering lines on screen. Though, the other socket (left side) has an issue, and there were flickering lines, but I don't plug it. Oh, I am using DVI monitor, and the brand is AOC. I wanted to buy something newer and...
Graphic Cards
WPF 3D Lines / WPF 3D CAD System
I am porting my VC++/MFC CAD system to C#/WPF platform (www.iNet1000.com) Is there any native 3D line support in WPF3D? It seems this is a very fundamental thing in any graphic application. Makoto
Software


Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

© Designer Media Ltd

All times are GMT -5. The time now is 13:13.
Twitter Facebook Google+ Seven Forums iOS App Seven Forums Android App