6 Replies - 8892 Views - Last Post: 29 November 2010 - 09:14 AM Rate Topic: -----

#1 Maahi Sawarkar  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 29-October 10

How to remove duplicates records while importing csv file in to sql se

Posted 27 November 2010 - 01:18 AM

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?

Is This A Good Question/Topic? 0
  • +

#3 mavarazo  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 36
  • View blog
  • Posts: 181
  • Joined: 25-October 10

Re: How to remove duplicates records while importing csv file in to sql se

Posted 28 November 2010 - 11:50 PM

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.
Was This Post Helpful? 0
  • +
  • -

#4 mavarazo  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 36
  • View blog
  • Posts: 181
  • Joined: 25-October 10

Re: How to remove duplicates records while importing csv file in to sql se

Posted 29 November 2010 - 07:30 AM

What is not ok with my answer?
Was This Post Helpful? 0
  • +
  • -

#5 zdavis  Icon User is offline

  • New D.I.C Head

Reputation: 6
  • View blog
  • Posts: 37
  • Joined: 12-February 10

Re: How to remove duplicates records while importing csv file in to sql se

Posted 29 November 2010 - 07:41 AM

mavarazo,

I thought your answer was great. It is exactly what i would have done.
Was This Post Helpful? 0
  • +
  • -

#6 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4431
  • View blog
  • Posts: 7,702
  • Joined: 08-June 10

Re: How to remove duplicates records while importing csv file in to sql se

Posted 29 November 2010 - 08:01 AM

I'm curious how one account managed to vote three times.
Was This Post Helpful? 0
  • +
  • -

#7 Guest_notrosh*


Reputation:

Re: How to remove duplicates records while importing csv file in to sql se

Posted 29 November 2010 - 08:07 AM

I voted once....
Was This Post Helpful? 0

#8 Bengie25  Icon User is offline

  • D.I.C Head

Reputation: 17
  • View blog
  • Posts: 53
  • Joined: 07-July 10

Re: How to remove duplicates records while importing csv file in to sql se

Posted 29 November 2010 - 09:14 AM

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

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1