Import Excel Data into MySQL in 5 Easy Steps

This is probably nothing new to many, but I spent quite some time to figure it out so I thought I'd post my notes on it.

To import data from Excel (or any other program that can produce a text file) is very simple using the LOAD DATA command from the MySQL Command prompt.

1. Save your Excel data as a csv file (In Excel 2007 using Save As)


2. Check the saved file using a text editor such as Notepad to see what it actually looks like, i.e. what delimiter was used etc.


3. Start the MySQL Command Prompt (I'm lazy so I usually do this from the MySQL Query Browser - Tools - MySQL Command Line Client to avoid having to enter username and password etc.)


4. Enter this command:
   LOAD DATA LOCAL INFILE 'C:\\temp\\yourfile.csv' INTO TABLE database.table FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' (field1, field2);


5. Done!

Very quick and simple once you know it :)


Some notes from my own import - may not apply to you if you run a different language version, MySQL version, Excel version etc...

• TERMINATED BY - this is why I included step 2. I thought a csv would default to comma separated but at least in my case semicolon was the deafult


• ENCLOSED BY - my data was not enclosed by anything so I left this as empty string ''


• LINES TERMINATED BY - at first I tried with only '\n' but had to add the '\r' to get rid of a carriage return character being imported into the database


• Also make sure that if you do not import into the primary key field/column that it has auto increment on, otherwhise only the first row will be imported

Partial Restore of Mysqldump Backup

Just recently I started making backups of my MySQL databases using mysqldump.
I have a small batch/command file running on my Windows based MySQL Server, scheduled to every night backup all of my databases to a file named backup_all_dbs.sql:

"C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqldump" --all-databases -u username --password="password" > c:\backup\%DATE%\Backup_MySQL\backup_all_dbs.sql

A few weeks ago I finally upgraded my MySQL server from 4.1 to 5.0. For once I did everything by the book which included backing up all my databases using the backup tool in MySQL Administrator, installing the new server version and then restoring all databases.

Today however I just realized that somehow two tables in one of the databases did not get backed up and hence not restored. Fortunately I still had the nightly backup from the day before upgrading.

So I thought I'd just do a selective restore from the mysqldump file using MySQL Administrator (part if the MySQL GUI Tools) Turns out MySQL Administrator does not read files created with mysqldump.
And worse: a quick Google reveals that mysqldumps cannot be partially restored :(

My next couple of hours I spent installing and uninstalling various text editors that could open my 500+MB backup file from mysqldump. I finally found one that could, even though it took a while... (using a Windows Vista machine with 2GB RAM) - NoteTab Light 5.5 (freeware)

Using this I opened the backup file created with mysqldump and sure enough it was a text file containing SQL statements (too bad MySQL Administrator could not open it, hopefully it will in a future version).
I simply deleted the SQL statements for all databases except the one I wanted to partially restore. Then I restored it on my development machine using this command:

"C:\Program Files\MySQL\MySQL Server 5.0\bin\mysql.exe" -uusername -p dbname < c:\backup\2007-12-13\backup_all_dbs.sql

Important note: If you have backed up full databases (not just tables) then it seems the dbname parameter doesn't matter, it still restores to the original database name - lucky me I did it on my development machine!

After the restore I did a partial backup using MySQL Administrator. Moved that backup file to my production server and did a restore using MySQL Administrator on that machine. Voìla!


So, to sum up:
1) Open your dump file in a large file text editor and delete everything except what you want to restore.
2) On a non-production machine (recommended) do a restore.
3) Make a partial backup using MySQL Administrator from your non-production machine
4) Restore the partial backup using MySQL Administrator on your production server.

Job done, finally I can get back to work!

Of course you can skip step 2 and 3 (and/or just restore the specific tables you want) if you like living dangerously ;)

Happy New Year!

Unable to convert MySQL date/time value to System.DateTime

After switching from MySQL ODBC driver to the .NET connector I have been getting this error: Unable to convert MySQL date/time value to System.DateTime.

This is when a DATETIME or DATE field contains an invalid value. It turns out that 0000-00-00 which I have used as default value in a DATE column is considered an invalid date. Instead NULL should be used when date/time is not set.

The one solution I found is to add Allow Zero Datetime=True to the connection string, this allows for 0000-00-00 00:00:00 in a DATETIME column. Just add it at the end of your connection string so it looks something like this:
<add key="connectionString" value="Database=your_db;Data Source=localhost;User Id=root;Password=password;Allow Zero Datetime=True;"/>

This however did not seem to work 100% for my code.
In VB.NET I was using this piece of code to check if the valid_until field (of type DATE) is set or not:
If ("" & myDataReader("valid_until")) = "" Then ...

I was concatenating to an empty string istead of using .ToString() because this (if I remember it correctly) would catch both NULL and 0000-00-00 values (both would evaluate to empty string).
Now instead I got this error message: Conversion from type 'MySqlDateTime' to type 'String' is not valid.

With ODBC, 0000-00-00 used to evaluate to empty string, but using the .NET connector with the connection string work around it returns the actual value, so I have to check for the zeroes instead. So, the solution to my problem is of course to use the proper .ToString function and check for both "0000-00-00" and empty string returned for null value (or even better - use the proper IsDBNull function).

Another way besides using the connection string workaround is of course to fix the data.

• First check the data definitions in your databases to make sure it does not default to 0000-00-00 (for data type DATE) or 0000-00-00 00:00:00 (for data type DATETIME). I'm still running MySQL server version 4 but I think that on version 5, using 0000-00-00 as default value is no longer valid.


• Then update the data. An SQL statement to update a column of type DATE would typically look like this for a column named valid_until:
  UPDATE your_table SET valid_until=null WHERE valid_until LIKE '0000-00-00';
  

.NET and MySQL in Five Easy Steps

I've been using the MySQL ODBC driver for a long time now in my ASP.NET projects (as posted about here). But today when I was about to start a new project on my new Vista machine I thought I'd check if anything new had emerged the last years when it comes to MySQL drivers for .NET - and not too surprisingly there is now (probably been there for a loooong time) an ADO.NET driver called ADO.NET Driver for MySQL Connector/NET.

So this post is on how to get it going, it's very straight forward.
This is tried on Vista with Visual Studio 2005 SP1 and the Visual Studio 2005 Service Pack 1 Update for Windows Vista using the ADO.NET Driver for MySQL (Connector/NET) version 5.0.6.

1) Download and install (using the included installer) the ADO.NET Driver for MySQL Connector/NET.

2) Create or open a project in Visual Studio 2005. I'm using a VB.NET console project in my example.

3) Go to Project + Add Reference...
On the .NET tab find and hilite MySQL.Data and then click Ok.
(It seems this step can be skipped sometimes...)
[Update: In an ASP.NET project go to Website + Add Reference]

4) Go to Project + YourProject Properties... + References Tab
Under Imported Namespaces check MySql.Data.MySqlClient and MySql.Data.Types and save.
[Update: In an ASP.NET project open web.config and add as namespace under configuration - system.web - pages - namespaces]

5) Write your code and run it. Here is an example to get you started:

Dim myConnectionString As String = "Database=TestDB;Data Source=localhost;User Id=TestID;Password=TestPwd"
Dim myConnection As New MySqlConnection(myConnectionString)
Dim myCommand As New MySqlCommand("SELECT * FROM testtable", myConnection)
Dim myDataReader As MySqlDataReader
myConnection.Open()
myDataReader = myCommand.ExecuteReader
While myDataReader.Read
Console.WriteLine(myDataReader("testcolumn"))
End While
myDataReader.Close()
myConnection.Close()

More examples can be found in the manual from MySQL.

Note: If your accessing your MySQL server on another machine, i.e. not as localhost, you need to make sure the firewall on the server allows access on TCP port 3306. If the server is using Windows firewall just simply go into Control Panel - Windows Firewall - Exceptions Tab click Add Port. (Just make sure you remember to disable the port again after you are done testing!)

How to access MySQL from .NET (ASP.NET using VB.NET)

[Update: Go to this new post instead on how to use MySQL ADO.NET drivers]

This is a three step description of what you need to do to access a MySQL server from VB.NET.

I used Microsoft Windows Server 2003 with Microsoft Visual Studio 2003 Professional and MySQL Server 4.1.7.

It is possible to use ODBC, OLEDB and native .NET providers to access MySQL from .NET. However the ODBC driver (MyODBC) is the only official driver (ie from MySQL) provided for free. The drawback is that ODBC gives the worst performance of the three. More info on this can be found at dev.mysql.com/tech-resources/articles/dotnet.

Ok, so this is how I did it:

1) Download and install the MyODBC driver. Latest version can be found at dev.mysql.com/downloads/connector/odbc. Current version, used in this example, is 3.51.

2) Start a new or use an existing ASP.NET (using VB.NET) project in Visual Studio. You need to import the System.Data.Odbc namespace to the project. From the "Project" menu select "Properties". Under "Common Properties" and "Imports" type "System.Data.Odbc" in the "Namespace" box and click "Add Import".

3) Below is some example code to get started. More examples can be found at dev.mysql.com/doc/mysql/en/odbc-net-op-vb-cp.html.

Dim MyConString As String = _
"DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=localhost;" & _
"DATABASE=testdb;" & _
"UID=root;" & _
"PASSWORD=rootpassword;" & _
"OPTION=3"

Dim MyConnection As New OdbcConnection(MyConString)
MyConnection.Open()

Dim MyCommand As New OdbcCommand
MyCommand.Connection = MyConnection

Happy programming!

(This article is previously published and has been moved to this blog)