SQL Server 2008 r2 row count increase

Question

Hi all once a table is created in sql server 2008 r2, is there anyway to increase the max row size, or size of table as I cant change through properties. I have a files that has more than 2800 lines but my table only allow 2502.

Thank you

Poppa Wallace · Answer

The problem is I have no control over the way the I receive the data, and then I have to rely on the user saving in the correct format, I just want to point and the user click and the update works. Thanks

theLizard · Answer

Hi Jay, There is no problem in dealing with your particular problem, you first need to save the Excel page as a TAB delimited text file or just plain CSV, it will produce something like this TAB delimited "Hello Field 1 with comma, how do we get rid of it for import" Field 1 Field 2 Field 3 "Hello Field 1 with comma, how do we get rid of it for import" Field 1 Field 2 Field 3 CSV "Hello Field 1 with comma, how do we get rid of it for import",Field 1,Field 2,Field 3 "Hello Field 1 with comma, how do we get rid of it for import",Field 1,Field 2,Field 3 Notice the double quotes and the comma in between the double quotes, this is what excel does to indicate that everything between the quotes is the value of one field, including the comma and it should be kept as is. You can solve this problem a number of ways very easily, dealing with single quotes is also easy peasy, no problem at all private void button1_Click(object sender, EventArgs e) { string line; List records = new List (); StreamReader sr = new StreamReader(@"c:	emp\fields.csv"); while((line = sr.ReadLine())!=null) { records.Add(makeSqlRecordString(line)); } //here you should be able to do a bulk insert using string sql = ""; for (int i = 0; i { sql += records[i] + ";" + Environment.NewLine; //you may want to limit the number of records to insert at 1 time //So use a counter var and execute insert statement when counter reaches //the number of records to insert, you could do this in a function //DoInsert(sql); } //or execute the sql statement here } private string makeSqlRecordString(string line) { char c = '"'; int numbFields = 4, count = 0; string value, sql; string[] fields = new string[numbFields]; int pos1 = -1, pos2 = -1; while ((pos1 = line.IndexOf(c)) > -1) { pos2 = line.IndexOf(c, pos1 + 1); value = line.Substring(pos1 + 1, pos2 - 1); line = line.Substring(pos2+2, (line.Length - pos2)-2); if (value.IndexOf("'") > 0) //if there is a single quote in the field, simple as. value = value.Replace("'", "''"); //sql inserts will not fail if this is done otherwise if it sees a single quote it assumes it is the end of a field and errors will be thrown fields[count] = value; count++; } if (line.Length > 0) { while (line.IndexOf(",") > 0) { value = line.Substring(0, line.IndexOf(",") - 1); line = line.Substring(line.IndexOf(",") + 1); if (value.IndexOf("'") > 0) value = value.Replace("'", "''"); fields[count] = value; count++; } } if (line.Length > 0) { if (line.IndexOf("'") > 0) line = line.Replace("'", "''"); fields[count] = line; } sql = "INSERT INTO table(field1, field2, field3, field 4) VALUES( '" + fields[0] + "','" + fields[1] + "','" + fields[2] + "','" + fields[3] + "')"; //you would need to make sure that single quotes are used for the appropriate field types, strings, dates but not in, floats or other numeric values return sql; } Forgot to mention that the while loop that deals with the double quotes is good for fields in sequential order, you can do the same for all fields with a little extra work but if you know which fields can contain single quotes and commas, they can be dealt with easy enough.

Poppa Wallace · Answer

Because the file opens as expected with Excel(2010) not sure if previous version handle the same, would I not be best maybe using something along the lines of below, would you recommend something along this method. public void Exel2Sql() { OdbcConnection connection; SqlBulkCopy bulkCopy; string ConnectionString = @"server=sujitkumar\sqlexpress;database=pubs;uid=sa;pwd=1234;"; string connstr = @"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=c:\contact.xls"; using (connection = new OdbcConnection(connstr)) { OdbcCommand command = new OdbcCommand("Select * FROM [Sheet1$]", connection); //you can change [Sheet1$] with your sheet name connection.Open(); // Create DbDataReader to Data Worksheet using (OdbcDataReader dr = command.ExecuteReader()) { // Bulk Copy to SQL Server using (bulkCopy = new SqlBulkCopy(ConnectionString)) { bulkCopy.DestinationTableName = "Names"; //"Names" is the sql table where you want to copy all data. bulkCopy.WriteToServer(dr); } dr.Close(); } } bulkCopy.Close(); connection.Close(); }

Vulpes · Answer

I've been having a closer look at the Excel file and the problem is much worse than we thought :( There are some lines (for example 396 and 397) which have embedded commas in Column K and there are some other lines for example 542) which have embedded quotes (as well as commas) in Column B. Dealing with all this is not going to be easy and will need a clear head. I'm going to have to go out soon and won't be back till late. I'll therefore try and return to it tomorrow.

Poppa Wallace · Answer

The file I uploaded is how we receive it, unfortunately I want to try avoid user intervention as much as possible. At present we have to push this through another app that converts it to XML then copy to my app,before we moved to SQL I just wanted to have a point and click approach to it. Originally we was opening this file, choosing save as csv then we had no problems, but again its a chore. Maybe if I could do this in the background with no user intervention to a temp file then bulk insert. This is how the file is always going to relieved and I would rather the method we are aiming for now be the default. I have returned them lines to before. If we was to read the last row and if its null insert a Y is that possible ? that would remove the black space at the end if the row after the , Jay

Vulpes · Answer

Yeah, I've counted the rows in question and there are 70 of them (out of a total of 2550) which is too many anyway. So, I'd put those lines back as they were. The problem I'm having now is that I can't save the Excel file to a .csv in exactly the same format as you and so the program doesn't work. The quotes are only added to the second column if it includes some embedded commas, whereas the program assumes everything has surrounding quotes! Do you think you could upload the .csv file itself?

Poppa Wallace · Answer

That still returns 2502 and now complains that the column table names dont match. Debugger :- "Part Number,Part Name,On Hand New,On Hand Fixed,SC5 - Storage Bin - EAME,Is ASL Item,Reorder Point,Stock Maximum,On Order,Back Order,Part Cost,PC11 - Repairable - EAME," that coma at the end is causing a issue again me thinks

Vulpes · Answer

It is but I wanted to be sure that the total was still 2502 after the StreamReader was closed as the debugger might not necessarily give correct figures in the meantime. Anyway, I've downloaded the Excel file now and I think I've figured out what the problem is - the small number of lines which have a value in the last column are being skipped. To (hopefully) fix it, identify these two lines: string[] value = line.TrimEnd(',').Replace("\"", "").Split(','); value = new string(chars).TrimEnd(',').Replace("\"", "").Split(','); and change them to: string[] value = line.Replace("\"", "").Split(','); value = new string(chars).Replace("\"", "").Split(',');

Poppa Wallace · Answer

2502 is the result from that, Isnt this happening further up the code as dt.Rows.Add(row); = 2502.

Vulpes · Answer

Just to make absolutely sure, I'd place this line immediately after sr.Close(): MessageBox.Show(dt.Rows.Count.ToString());

Poppa Wallace · Answer

I have added that and still the same result dt.Rows.Add(row); Rows is returning Count = 2502

Vulpes · Answer

Ah, I've just noticed that you're not closing the StreamReader after reading the rows from the file. So, there may still be some rows still in the buffer which haven't been flushed through. To fix it add the highlighted line: while (!sr.EndOfStream) { string dataLine = sr.ReadLine(); int index1 = dataLine.IndexOf(',') + 2; int index2 = dataLine.IndexOf('\"', index1); char[] chars = dataLine.ToCharArray(); for (int i = index1; i { if (chars[i] == ',') chars[i] = ';'; } value = new string(chars).TrimEnd(',').Replace("\"", "").Split(','); if (value.Length == dt.Columns.Count) { row = dt.NewRow(); row.ItemArray = value; dt.Rows.Add(row); } } sr.Close();

Poppa Wallace · Answer

Latest file I imported https://rapidshare.com/files/844989750/latest_dump.csv?bin=1 Latest Code private void button2_Click_1(object sender, EventArgs e) { { OpenFileDialog fdlg = new OpenFileDialog(); fdlg.Title = @"Site Assist Database Update"; fdlg.InitialDirectory = @"c:\"; fdlg.Filter = @"CSV Files (*.csv)|*.csv"; fdlg.FilterIndex = 2; fdlg.RestoreDirectory = true; if (fdlg.ShowDialog() == DialogResult.OK) { string filepath = fdlg.FileName; var con =new SqlConnection(@"Data Source=JAYS_LAPTOP;Initial Catalog=SLADB;Integrated Security=True"); var sr = new StreamReader(filepath); for (int i = 0; i string line = sr.ReadLine(); string[] value = line.TrimEnd(',').Replace("\"", "").Split(','); var dt = new DataTable(); DataRow row; foreach (string dc in value) { dt.Columns.Add(new DataColumn(dc)); } while (!sr.EndOfStream) { string dataLine = sr.ReadLine(); int index1 = dataLine.IndexOf(',') + 2; int index2 = dataLine.IndexOf('\"', index1); char[] chars = dataLine.ToCharArray(); for (int i = index1; i { if (chars[i] == ',') chars[i] = ';'; } value = new string(chars).TrimEnd(',').Replace("\"", "").Split(','); if (value.Length == dt.Columns.Count) { row = dt.NewRow(); row.ItemArray = value; dt.Rows.Add(row); } } // MessageBox.Show(value.Length.ToString()); // MessageBox.Show(dt.Columns.Count.ToString()); con.Open(); new SqlCommand("delete from PartsTB", con).ExecuteNonQuery(); var bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock) { BulkCopyTimeout = 60, DestinationTableName = "PartsTB", BatchSize = dt.Rows.Count }; bc.WriteToServer(dt); bc.Close(); con.Close(); MessageBox.Show(@"Database Updated " + dt.Rows.Count + @" Records Added"); } } } Thank you I really appreciate this Jay

Vulpes · Answer

I don't know what to make of it :-/ Can you post your latest code and also upload the file to the site you used yesterday. I'll see if I can figure out what the problem is.

Poppa Wallace · Answer

Is it possible that "EndOfStream is prematurely returning true becuase the StreamReader has actually read the whole file stream into a buffer, by the time you get near the end of the file"

Poppa Wallace · Answer

The bulk copy is not the problem, dt.Rows.Add(row); Rows is returning Count = 2502

Poppa Wallace · Answer

this is the said row Y712345 ASSY-IDLER ROLLER 12 0 S9 //this line on the row above is empty, but there are others also like this Yes 6 12 0 0 13.09 Â£ Its no different to others really. strange

Vulpes · Answer

Not to my knowledge. It can time-out (the default is 30 seconds) but I'd be surprised if that were the problem. Is there anything odd about the 2503rd row in the table?

Poppa Wallace · Answer

Maybe thats not the issue, is there a max for sqlbulk copy ?

SQL Server 2008 r2 row count increase

Nilanka Dharmadasa