Help Needed in Reading lines

Hi,

I am trying to read the file from text file and inserting into sqlserver table. I am facing trick issue, The code is reading the file but inserts into null value.

table structure:

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

sample code which loops through

$datatable = New-Object "System.Data.DataTable"
 $datatable.Columns.Add("Name",[string])
 $datatable.Columns.Add("Age", [int])
  # 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() 
        } 
    }  

sample data

Name1|10
Name2|20
Name3|30
Script started...
Property 'EnableStreaming' cannot be found
At E:\Sample\PowerShell\Test.PS1:4
+ $bulkcopy. <<<< EnableStreaming = 1
    + CategoryInfo          : InvalidOperat
    + FullyQualifiedErrorId : PropertyAssig



AllowDBNull        : True
AutoIncrement      : False
AutoIncrementSeed  : 0
AutoIncrementStep  : 1
Caption            : Name
ColumnName         : Name
Prefix             :
DataType           : System.String
DateTimeMode       : UnspecifiedLocal
DefaultValue       :
Expression         :
ExtendedProperties : {}
MaxLength          : -1
Namespace          :
Ordinal            : 0
ReadOnly           : False
Table              : {}
Unique             : False
ColumnMapping      : Element
Site               :
Container          :
DesignMode         : False

AllowDBNull        : True
AutoIncrement      : False
AutoIncrementSeed  : 0
AutoIncrementStep  : 1
Caption            : Age
ColumnName         : Age
Prefix             :
DataType           : System.Int32
DateTimeMode       : UnspecifiedLocal
DefaultValue       :
Expression         :
ExtendedProperties : {}
MaxLength          : -1
Namespace          :
Ordinal            : 1
ReadOnly           : False
Table              : {}
Unique             : False
ColumnMapping      : Element
Site               :
Container          :
DesignMode         : False

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

September 5th, 2015 1:43am

While you can use PowerShell to read from a text file and import into a SQL Server table, it is not recommended to do so if you already know the format of the files. SQL Server Integration Services is a recommended tool for doing so.

But in case you still want to use PowerShell, refer to this article for a different approach

https://www.mssqltips.com/sqlservertip/3208/automating-flat-file-sql-server-imports-with-powershell/

Free Windows Admin Tool Kit Click here and download it now
September 5th, 2015 2:13am

Your code does not match the error message.

Here is a better way to bulk load a table.

$csv=Import-Csv somefile.txt -Delimiter '|' -Header Name, Age

Now we have an object.

$dt=Out-DataTable $csv

Now we have a bulk loadable data table.

See this for some assistance: https://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29ddH

Here is a sample driver:

$csvfile='.\MyCsv.csv'
$sqlTable='MyDataTable'
$DataSource='MyServer\MyInstance'
$DataBase='db1'

$ConnectionString ='Data Source={0}; Database={`}; Trusted_Connection=True;' -f $DataSource,$DataBase
$csv=Import-Csv $csvfile.txt -Delimiter '|' -Header Name, Age $csvDataTable = $csv | Out-DataTable $bulkCopy = new-object Data.SqlClient.SqlBulkCopy($ConnectionString) $bulkCopy.DestinationTableName=$sqlTable $bulkCopy.WriteToServer($csvDataTable)

You can add options to the bulkcopy or you can custom map the columns.  The default is to auto-map by name.

September 5th, 2015 3:31am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics