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 Search this Thread
Search this Thread:

Advanced Search




Similar help and support threads
Thread Forum
Horizontal Lines!
What causes this and how can I fix it? Changing settings in ATI's Catalyst Control Center did nothing. It seems to only affect some shows sometimes, but it will be for the full 30 minutes, etc. P.S. The image has to be viewed at full size to see the lines. ...
Media Center
How to cut the lines of a pdf file
Hi to all, I have a PDF file (FreeBSD Handbook). I want to print it in paper (A4 size) and read it because reading it from screen hurts my eyes. My problem is that the lines are very tiny and long. If I print the pages in A4 size the lines/words will not be readable. If you look at the picture...
General Discussion
on screen lines are often one on another in W7
Hello First excuse my errors in english I m french Thanks So in my W 7 on some program (not all) lines are one upon another,and the text is difficult to read as the letters are half their hight.It seams as the line is cut in 2 horizontaly,and often one upon another.My screen is an Viewsonic...
General Discussion
Lines on Moniter
I recently got a new laptop hard drive loaded with windows 7 but theres a problem with my graphics since the change of the harddrive something keeps happening to my laptop where i keep getting wierd lines on my moniter it was happening before but its happening more frequently since the change could...
BSOD Help and Support
Aero Lines?
Sorry if this is a double post. I read somewhere previously on how to remove these, they're barely noticeable but I can't seem to find the post that said the registry to edit to remove them, any help would be great full. http://dl.dropbox.com/u/3055511/7%20forums%20stuff/lines.png
Customization


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 04:12.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App