ItemArray error problem

[hedgracer]

I have the following code:

using System.Data;
using System.Data.SqlClient;
using System;
using System.Collections;
using System.IO;




public class clsSqlConnectionConnectionString
{
static SqlConnection con;

public void Main ( )
{
con = new SqlConnection ( "Server=RCGSQLQA1;Initial Catalog=ACTG;Integrated Security=SSPI" );
DataTable dt = new DataTable ( );
string line = null;
int i = 0;

using ( StreamReader sr = File.OpenText ( @"c:\moneyf120100629.csv" ) )
{
while ( ( line = sr.ReadLine ( ) ) != null )
{
line = line.Replace ( "\"", "" );
string [ ] data = line.Split ( ',' );

if ( data.Length > 0 )
{
if ( i == 0 )
{
foreach ( var item in data )
{

dt.Columns.Add ( new DataColumn ( ) );
}
i++;
}
DataRow row = dt.NewRow ( );
row.ItemArray = data;
dt.Rows.Add ( row );
}
}
}
SqlBulkCopy bc = new SqlBulkCopy ( con.ConnectionString, SqlBulkCopyOptions.TableLock );
bc.DestinationTableName = "Moneyline_One";
bc.BatchSize = dt.Rows.Count;
con.Open ( );
bc.WriteToServer ( dt );
bc.Close ( );
con.Close ( );
}
}

The problem is with the row.ItemArray = data; line. I get the following error message:

Input array is longer than number of columns in table.

This code works just fine with 20000 rows by 189 columns. When I go to 30000 rows by 189 columns I get the error. How do I correct this? Any help is appreciated. Thanks.

 

[jmeckley]

line.Split ( ',' );

is most likely the problem. if the value of field contains a comma the array is not the same size as the columns in the table.

check out thread732-1609580. there may be some useful information on get data to/from a file and database. while it doesn't directly relate to your issue. the concepts of ETL apply. I also reference file helpers which solves the problem you have.


Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259