Excel 2007 Data Import Into Mysql

[Piker]

A couple of points I've noticed trying to import data from Excel 2007 into a mySQL (5.0) table. Firstly, SQLYog doesn't appear to recognise named ranges in Excel, so data imports include large numbers of columns not needed in the import (and the un-ticking of the unwanted columns is tedious). Secondly, when the data type changes within an excel column (i.e. from text to number formatted as text) I get NULLS in the resultant mySQL table. In this case, it's a very important omission. Thirdly, since I had already created the mySQL table beforehand, there was no auto-mapping of column names – I had to go through each column and map the name correctly – also quite tedious. Have I missed something?

It may well be just as quick (for an exercise like this one) to import the Excel data into Access, then use the import process, in order to get accurate results. I haven't tried that yet. It seems there is no simple way to migrate a large and diverse number of Excel sheets into mySQL tables accurately, without all fields ending up as VARCHAR(255) 😉

[Aparna]

Hi,

I would want you to confirm the following things:

Quote:

Firstly, SQLYog doesn't appear to recognize named ranges in Excel, so data imports include large numbers of columns not needed in the import (and the un-ticking of the unwanted columns is tedious).

Do you want SQlyog to filter columns based on the Named ranges so that the user can select them with ease while importing???

Quote:

Secondly, when the data type changes within an excel column (i.e. from text to number formatted as text) I get NULLS in the resultant mySQL table. In this case, it's a very important omission.

Could you please tell us the version of SQLyog you are using?? Lots of issues in Import External Data with the older versions were fixed in the newly released version. And can you elaborate more on this and try and share the excel sheet that is causing issues if possible. We tried with various cases at our end but we are not able to figure out what is going wrong in your case 🙁

Quote:

Thirdly, since I had already created the mySQL table beforehand, there was no auto-mapping of column names – I had to go through each column and map the name correctly – also quite tedious. Have I missed something?

As i understand in this case do you want the auto mapping to be enabled when the table structure is already created in MYSQL???

Regards,

Aparna

[peterlaursen]

“It seems there is no simple way to migrate a large and diverse number of Excel sheets into mySQL tables accurately, without all fields ending up as VARCHAR(255)”.

Well – a spreadsheet is not a database.  A database has strict type-checking for all values of a column.  A spreadsheet has not. To import all kinds of data to a MySQL datatype it can only be to a string type. If you try to import a DATE to an INTEGER column for instance it will truncate in one way or the other.

So in my understanding not all spreadsheets are fit for migration to a database.  The spreadsheet must have bee created with some kind of data discipline in mind.  I also doubt that Access can handle this particular Excel-file.

But if you can attach a small example here that illustrates the problem we will comment on it.

[Author]

Posts