read text file to sql server

shshva

9y1.2kWindows Forms

I have this kind of textfile with 100000 rows:

xx xxxx xxx xxxxx xxxxx xxxx xxxxxx xxxxx x xxx xxx xxxxx xxx-xxxx

xx xxxx xxx xxxxx xxxxx xxxx xxxxxx xxxxx x xxx xxx xxxxx xxx-xxxx xx

xx xxxx xxx xxxxx xxxxx xxxx xxxxxx xxxxx x xxx xxx xxxxx xxx-xxxx xxx xxx xx

i have table in sql

date time receive sent message

xx xxxx xxx xxxxx xxxxx xxxx xxxxxx xxxxx x xxx xxx xxxxx xxx-xxxx

I want to save first 4 data each rows into assigned columns the rest values into ONE column.

am stuck at save remaining data into one column

foreach (var item in data)

{

dt.Columns.Add(new DataColumn());

}

i++;

DataRow row = dt.NewRow();

row.ItemArray = data;

dt.Rows.Add(row);

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

shshva9y ago

Error :
ArgumentException was unhandled
Source array was not long enough. Check srcIndex and length, and the array's lower bounds.
Here is my code :
string rearr = string.Join(" ", data.Skip(5)).ToString();
DataRow row = dt.NewRow();
/

string[] first = new string[5];
string last = string.Join(" ", rearr).ToString();
Array.Copy(data, first, first.Length);
//Array.Copy(data, first.Length, last, 0, last.Length);
Array.Copy(data, 0, first, first.Length , last.Length );

string[] finaldata = new string[first.Length + last.Length];
Array.Copy(first, finaldata, first.Length);
Array.Copy(last, 0, finaldata, first.Length, last.Length);
row.ItemArray = finaldata;
dt.Rows.Add(row);

Error :
ArgumentException was unhandled
Source array was not long enough. Check srcIndex and length, and the array's lower bounds.

0
Manas Mohapatra9y ago
Are you adding properly data into DataTable. Means, let's say you have 5 columns in your DataTable and you need to insert 5 values each row of table. Please cross verify in your code by debugging.
0
Jay Stewart9y ago
http://www.filehelpers.net/

This has helped me so many times in the past and its almost all done for you.
0
shshva9y ago
. Am stuck last column last doesnt insert rest of the array

string[] data = line.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
string join = string.Join(" ", data);

foreach (var item in data)
{
dt.Columns.Add(new DataColumn());
}

i++;
string restarray = string.Join(" ", data.Skip(5));
DataRow row = dt.NewRow();
var result = string.Join(" ", restarray);

row.ItemArray = data;
dt.Rows.Add(row);
}

am stuck at save in database the joined string into last column

0
Manas Mohapatra9y ago
First you need to build two datasets from your text file as per yor requirement. Just read one by one line from text and assign respective value to DataSets. Once your datasets are ready you need to use SqlBulkCopy class to insert your data. Here is sample code:
DataSet ds = GetData();
SqlConnection con = new SqlConnection(@"data source=.\sqlserver; user id=sa; password=123; database=test;");
SqlBulkCopy bulk = new SqlBulkCopy(con);
bulk.DestinationTableName = "tblStudents";
foreach (DataColumn col in ds.Tables[0].Columns)
    bulk.ColumnMappings.Add(col.ColumnName, col.ColumnName);
con.Open();
bulk.WriteToServer(ds.Tables[0]);
con.Close();
https://www.aspsnippets.com/Articles/SqlBulkCopy-Bulk-Copy-data-from-DataTable-DataSet-to-SQL-Server-Table-using-C-and-VBNet.aspx
http://dotnetmentors.com/c-sharp/bulk-upload-into-sql-server-using-sqlbulkcopy-and-c-sharp.aspx
0