[Maahi Sawarkar]

Hello Friends..

I have one problem. when i importing csv file into sql server 2005, its imported data properly but one row less of the total record. Below is my Code.

SqlConnection con = new SqlConnection(@"Data Source=server-1;Initial Catalog=CAF_Retrieval;user id=sa; password=sa;");
                string filepath = textInput.Text;
                StreamReader sr = new StreamReader(filepath);
                string line = sr.ReadLine();
                string[] value = line.Split(',');
                DataTable dt = new DataTable();
                DataRow row;

                foreach (string dc in value)
                {
                    dt.Columns.Add(new DataColumn(dc));
                }

                while (!sr.EndOfStream)
                {
                    value = sr.ReadLine().Split(',');
                    if (value.Length == dt.Columns.Count)
                    {
                        row = dt.NewRow();
                        row.ItemArray = value;
                        dt.Rows.Add(row);
                    }
                }

                SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
                bc.DestinationTableName = "Upload_Data";
                bc.BatchSize = dt.Rows.Count;
                con.Open();
                bc.WriteToServer(dt);
                bc.Close();
                con.Close();

Also i want remove the duplicates records when i m importing the next csv. or i want to collect that duplicates at one folder. Also i want to show the progressbar while loading the data and its time, percentage and message to data imported successfully.

Can anybody give me the idea or code to implement this?

[mavarazo]

I would parse the Importfile into a Dictionary, where the key are your conditions for a duplicated entry.
Then you can with !Dictionary.Contains(key) add all the entries that arent duplicates.

For the percentage you need to know how many lines or characters (depends on your reading) are in a csv, then you can easely calculate the percentage.

[mavarazo]

What is not ok with my answer?

[zdavis]

mavarazo,

I thought your answer was great. It is exactly what i would have done.

[Curtis Rutland]

I'm curious how one account managed to vote three times.

[Bengie25]

If instead of doing a bulk insert, you write your own code to read the file, then you can keep track of the position you're in your filestream. This would give you a fairly accurate "progress". eg, every 100 rows committed, check your position in the filestream and update the progress bar based on position compared to file size.

Also, if you load into a temp table, you can SELECT DISTINCT out of it and into your destination table. SQL is excellent at this kind of stuff vs trying to write your own code.

This post has been edited by Bengie25: 29 November 2010 - 09:16 AM