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