1. Sort the column for which you wish to check for unique values
2. Create a new column - as a suggestion to the right of the sorted column
3. Type in the following formula in the new column on the same row as the first value in the sorted column:

=EXACT(A1;A2)

where A1 is the value on the same row in the sorted column and A2 is the value on the next row of the sorted column.
4. Copy this cells value to the rest of the cells in the new column for each row that have a corresponding value in the sorted column.

If there can only be two equal values the result will be a truly unique list of values which have duplicates. If a value can occur several times the result will contain the duplicates but they will occur several times.

I am not an expert at all on Excel so if you have better tips please let me know in the comments.

It happens to me quite often that I start running an advanced query against two larger databases with comparisons on one or several fields which are not indexed. The problem with this is that it takes so much longer. And in these cases it is good to know that you can easily kill the process and start over - something that might not be given if you're new to it.

So if you are working with the MySQL Query Browser and just have started a query it is often not enough to press the Stop button. Either it decides to run until the end anyway or the server might actually crash. A good way to kill it is through the MySQL Administrator.

So log in to the MySQL Administrator and click on 'Server Connections'. Mark the process that is running and that you would like to kill and then press the button in the right bottom corner 'Kill thread'.Now you can start over again. But remember to either index the field(s) first or fix/correnct the query.



You index a field by first clicking the tab 'Indices' (in the MySQL Query Browser) and pressing the plus sign below. Add a name for the index. Now drag a field from the field list to the Index columns box. Apply changes!

Select the 10 first records from a table

SELECT * FROM table_name LIMIT 0,10

Count number of rows in a table

select count(*) from table_name

Select records from table_name where field1 is unique for each result record

select distinct field1 from table_name

Select and order result by field ascendent

select field1, field2, field4, field12 from table_name order by field2 ASC

Select fields on a condition where 'someCommonString' exists somewhere in field1

select field1, field2, field4, field12 from table_name where field1 LIKE '%someCommonString%'

Insert data into a table

INSERT INTO table_name (field1, field2, field3) VALUES ('a_string this is','tata','testString')

Inner join two tables on a field with a condition and insert the result into another table

INSERT INTO table_name_3 (xfield2, xfield3) SELECT table_name_1.field2, table_name_2.field3 FROM table_name_1 INNER JOIN table_name_2 ON table_name_1.field1 = table_name_2.field1 WHERE table_name_1.field4 = "foobar"

Create a table

CREATE TABLE `table_name` ( `id` int(10) NOT NULL auto_increment, `field1` varchar(45) NOT NULL, `field2` varchar(200) NOT NULL, `field3` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) /* You can also add the following row directly after the above to specify the table engine and the charset and also where to start the autoincrement of the unique identifier. So for a new table I would choose AUTO_INCREMENT=1. The table engine can also be InnoDB.*/ ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Delete a table if it exists

DROP TABLE IF EXISTS db.table_name

Rename a table

RENAME TABLE table_name TO table_name_new

Delete all information in a table or on condition

DELETE FROM table_name DELETE FROM table_name WHERE field1 LIKE '%someString%'

Update values in a table

UPDATE table_name SET field1='asd", field2='2314egf' WHERE field3 = "foobar"

Load the contents of a file into a SQL-database

LOAD DATA INFILE 'data_infile.csv' INTO TABLE dbName.table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' (field1, field2, field3) /* In the example above the input file is a comma-separated file where each field that contains more than one word is separated with apostrophes. The fields are separated from each other with comma and a new record is separated from the previous with a new line. Table 'table_name' contains at least three fields named 'field1', 'field2' and 'field3'. The default location for the file is in the SQL-server's data directory for the specific database. */

Write contents from a table to a file on disc.

SELECT field1, field2, field3 INTO OUTFILE 'result.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM table_name; /* In the example above the fields 'field1', 'field2' and 'field3' from table 'table_name' are written to the file 'results.csv' where each field is separated from the other with comma and where each field if it contains more than one word is encapsulated with apostrophes. Each new record are separated from the previous one with a new line. The default location for the file is in the SQL-server's data directory for the specific database. */

I needed to compare Lotus Notes data with other data residing outside of Lotus Notes. Except for the comparison each matching record also had to be updated. The amount of data was rather big. The non-notes data consisted of 1,15 million records and the Lotus Notes data of 76000 records. I needed a union of the data on a specific id that both sources had.

I came up with two approaches.

#1 Read the data from excel files or an ODBC-connection and compare each of these rows with each of the Lotus Notes rows until there is a match. This algorithm can of course be tuned and and simplified to make it go faster. Nevertheless I would say that Lotus Notes is not the master in working with large amounts of data and this operation would probably take days. And although it would work and I would get both the comparison and the update at the same time it has to many cons:
- It would be too time-consuming
- It would block my entire Lotus Notes client.
- It would make it difficult and time-consuming to run several different comparisons

#2 Get both of the different sources into a relational database and do the comparison there. Then either take the data directly from there to where you want it or export the matches and import into Lotus Notes again to perform the updates (using approach #1). This is a two-stage process but the pros for this is that:
- Working with data is easy in a relational database
- It is fast
- Due to the above bullets I can make several different processing to reach the best result.
- I can still work with my Lotus Notes client.

So I used approach #2.
I imported the data from Lotus Notes and the other source into a MySQL-database that resides locally on my computer. For the import I tried out different tools like Navicat for MySQL and SQLyog Enterprise. Both are good tools but to be able to use the import feature you would have to do it either in the trial period or to buy the product.

Instead it is both cheaper and pretty easy to use the built-in functionality of MySQL. You can run the queries below in the MySQL Query Browser.

Both of the data sources in my example were currently in csv-format. It is for example easy to get data out of Lotus Notes in csv-format using the built-in export functionality on a view. So now we use the 'LOAD DATA INFILE' command for both of the csv-sources to get the data into the relational database. For this example assume we create two tables named 'table_name_1' and 'table_name_2' and read data into them.

LOAD DATA INFILE 'file_to_import.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' (field1, field2, field3)

This command reads all records from the file 'file_to_import.csv' to MySQL table 'table_name' to the specificed fields 'field1', 'field2' and 'field3'. We are also specifying that column values in the csv-file are separated by commas (,) and if a column value consists of more than one word it will be incapsulated by apostrophes (").

So we do this for both data sources and these will both be in one MySQL table each - 'table_name_1' and 'table_name_2', Now we come to the actual data processing. We want to compare/join data between the two tables and also being able to handle the result in some way. This is done using MySQL's INNER JOIN command:

INSERT INTO result_table (result_field1, result_field2, result_field3) table_name_1.field1, table_name_2.field1, table_name_1.field3 FROM table_name_1 INNER JOIN table_name_2 ON table_name_1.someId = table_name_2.someId WHERE table_name_1.someId != ""

We are inserting the results of an inner joined select query into a results table 'result_table'. This table will contain all the matches. Processing this query with 1,15 million record in one table and 76000 records in the other table took about 5 hours on my local machine. However if you index the fields in both of the tables that you need to compare to each other, the processing is much faster than that.

Now we want to get the data out of the database to get it into Lotus Notes and update the data there. Use the 'OUTFILE' command for this.

SELECT result_field1, result_field2, result_field3 INTO OUTFILE 'results.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '" LINES TERMINATED BY '\n' FROM result_table'

This command writes the specified fields into the file 'results.csv'. Each field is separated with a comma (,) and any fields containing more than one word will be enclosed by apostrophes ("). When all fields for a record has been written to the file it will be ended with a newline character (\n).

In both cases when we read and write to files the files are by default on a windows system located in the mysql data directory for the specific database that you're working on. So in this example if the database's name is 'db3' the default path would be 'C:\Program Files\MySQL\MySQL Server 5.0\data\db3\'.

As another sidenote. Both getting the data into MySQL from a file and writing it from MySQL to a file is a very fast process.

Now that we have this file we can do several things with it. Assuming we want to use it to update Lotus Notes data we can create a rather simple Lotus Notes agent that reads a csv- or excelfile and compares and updates for each record. I will provide this agent in a future blog post.