How to remove dup entries

Hi All
In my application i have to get the data from .csv file. My requirement is that file may consists of duplicate entries
I ant to remove the dup entries and i want to place in the table.
Waiting for valuable replies
Thank u
Baba

Suppose the text file is:

1, MAK, A9411792711, 3400.25
2, Claire, A9411452711, 24000.33
3, Sam, A5611792711, 1200.34
2, Claire, A9411452711, 24000.33

And you want to import it into table T1

Here it is:

SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID('T1') IS NOT NULL
DROP TABLE T1;
IF OBJECT_ID('T2') IS NOT NULL
DROP TABLE T2;

CREATE TABLE T1(
id int primary key,
[Name] sysname,
code sysname,
num decimal(10,2)
)
GO

SELECT * INTO T2 FROM T1 WHERE 1 = 2;

BULK INSERT tempdb.dbo.T2 FROM 'd:\tmp\test.txt'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

INSERT INTO T1 SELECT DISTINCT * FROM T2;
GO

SELECT * FROM T1;

The output is:

id Name code num
-- -- --
1 MAK A9411792711 3400.25
2 Claire A9411452711 24000.33
3 Sam A5611792711 1200.34

|||

hi

what is your way to import the data. If it is a ETL process with a select-statement use

select distinct * from yourcsv

The other way is, to import in a temp-table. after this copy the data to the correct table with

insert table

select distinct * from temptable

To get all dup entries use

select * from table where [onecolumnofrow] in

(select [samecolumn] from table group by [samecolumn] having count(*) > 1)

If there is only ONE dup entri you can delete this with DELETE top 1 * with the same where-clause

Bye

Thorsten Ueberschaer

sql

How to read CSV File in SQL Server 2005 using OpenRowSet Function

Hi

i want to access a CSV file using OpenRowSet function in SQL Server 2005.

Anyone having any idea; would be of great help.

Regards,

Salman Shehbaz.

http://www.databasejournal.com/features/mssql/article.php/10894_3331881_2

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1467861&SiteID=1

|||

For openrowset function to work properly in SQL Server 2005, you need to check "Enable OPENROWSET and OPENDATASOURCE Support" using SQL Server Surface Area Configuration for Features.

Later you can use the following command to access csv or text files;

SELECT * FROM

OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=D:\mmc;', 'SELECT * from test.csv');

Regards,

Salman Shehbaz.

|||

we can also enable 'Adhoc Distributed Queries' option using the following code;

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

EXEC sp_configure 'Ad Hoc Distributed Queries', 1

GO

RECONFIGURE with override

GO

How to read CSV File in SQL Server 2005 using OpenRowSet Function

Hi

i want to access a CSV file using OpenRowSet function in SQL Server 2005.

Anyone having any idea; would be of great help.

Regards,

Salman Shehbaz.

here's a similar post, you might want to try the query posted here

how to read binary file

Need help reading a binary file see below for details...

I have uploaded a csv file into a sql table.

Now i want to extract the data and insert the data in the csv file into another sql table.

What commands can i use in sql to extract/ read the data ?

INSERT INTO {table1}({col1})

SELECT {col2}

FROM {table2}

|||

i'm not reading data from a table....i'm want to read data from a binary file that is stored in a table

|||

Retrieve the value into a string.

Use the string as the buffer of a memory stream.

Use the memory stream as the base stream of whatever type of stream you want to use to actually read the data.

Or

Retrieve the value from the database into a string, and parse it?

Or

Retrieve the value from the database, and save it as a temporay file, then open the temporary file and parse like normal.

|||

Here's how to do the 2nd approach (Which is probably easiest):

dim conn as new sqlconnection(configurationmanager.connectionstrings("ConnectionString").Connectionstring)

dim cmd as new sqlcommand("SELECT MyBlobField FROM MyTable WHEREID=@.ID",conn)

cmd.Parameters.Add("@.ID").Value= {your key here}

conn.open

dim csv as string

csv=cmd.executescalar

conn.close

for each row as string incsv.Split(NewString() {vbCrLf}, StringSplitOptions.RemoveEmptyEntries)

dim cols() as string=row.Split(","c)

' Do insert here by referencing cols(0) - cols(x) for each of the columns in the csv

next

Does that help? Obviously, that's a very simplistic CSV parser, and it doesn't handle embedded cr/lf's, nor does it handle unix/linux written files, nor does it handle quoted fields, or quoted fields with commas in them. You can do a similiar approach using regular expressions to handle more complex CSVs if you need, but I didn't want to over complicate a simple example.

|||

If you are going to go the first or third of my options, you may want to use this to do the actual parsing for you, as it handles most of the known gotchas in CSVs:

http://www.codeproject.com/cs/database/CsvReader.asp

|||

thanks for the info...the info you posted is very helpful but its not exactly what i'm looking for.....i believe i'm not making my problem clear.....

what i'm trying to find out is there a way to read the binary (csv file) in a store procedure and extract the data and insert it into a table.

the csv file contains 2 column of data key and value.

I like to insert each row into a sql table call "TempA" which has colums key and value. I want to do this in a store procedure?

Any ideas?

again thanks for your help

|||

Hi,

As your .csv file is saved as binary data in a database table, I suggest you to pull the .csv file from the datafield first (by BinaryReader) and read the csv file, create the datatable, call your store procedure to insert the table into your database. Here's the sample code for you to transfer your csv data to datatable.

int intColCount = 0;
bool blnFlag = true;
DataTable mydt = new DataTable("myTableName");

DataColumn mydc;
DataRow mydr;

string strpath = ""; //cvs file path

string strline;
string [] aryline;

System.IO.StreamReader mysr = new System.IO.StreamReader(strpath);

while((strline = mysr.ReadLine()) != null)
{
aryline = strline.Split(new char[]{','});

if (blnFlag)
{
blnFlag = false;
intColCount = aryline.Length;
for (int i = 0; i < aryline.Length; i++)
{
mydc = new DataColumn(aryline[i]);
mydt.Columns.Add(mydc);
}
}

mydr = mydt.NewRow();
for (int i = 0; i < intColCount; i++)
{
mydr[i] = aryline[i];
}
mydt.Rows.Add(mydr);
}

Thanks.