read text file to sql server

Question

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);

shshva · Answer

Error : ArgumentException was unhandled Source array was not long enough. Check srcIndex and length, and the array's lower bounds. Here is my code : s tring 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.

Manas Mohapatra · Answer

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.

Jay Stewart · Answer

http://www.filehelpers.net/ This has helped me so many times in the past and its almost all done for you.

shshva · Answer

. 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

Manas Mohapatra · Answer

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