Need to Import xls file into SQL?

[DaveF]

The problem is there are duplicate rows in the xls. Who can I test the
database against the xls for duplicates

 

[John]

The problem is there are duplicate rows in the xls. Who can I test the
database against the xls for duplicates

Hi Dave,

First off; this question is probably better suited for either an Excel or
SQLServer newsgroup. You should try there in the future.

But, I just did this Friday, so :

1. Open your xls in Excel and save it to CSV.

2. Import your CSV file into a new table. Don't import directly from an xls
to avoid this problem ... http://www.sqldts.com/default.aspx?6,222,254,0,1

3. Code your insert statement to use the DISTINCT keyword.

Good luck.

Regards,
John MacIntyre
http://www.johnmacintyre.ca
Specializing in; Database, Web-Applications, and Windows Software

 

[John Timney \(ASP.NET MVP\)]

Clean the data first by loading the data into an arraylist and removing
duplicates. Someone else posted this code in another group - but it should
do the job nicely.

public static ArrayList RemoveDuplicates(ArrayList list) {
ArrayList ret=new ArrayList();
foreach (object obj in list) {
if (!ret.Contains(obj)) ret.Add(obj);
}
return ret;
}

--
Regards

John Timney
ASP.NET MVP
Microsoft Regional Director

 

[Scott Allen]

The typical approach is to either filter out the messy data with some
custom code that does the import, or create a staging area. A staging
area is a table or set of tables where you can upload the data and
then cleanse it. Remove duplicates, trim strings, etc. A staging area
would not have the same referential integrity constraints as the
production area, so it will allow duplicates and give you a place to
clean the data up.