Importing From External Data (Xls) With Currency Values Requires Odd D

[arkid77]

Hi

I've found an odd problem. I'm using sqlyog 8.6 and importing from an xlsx file that has currency values (british pound) with a standard decimal points value set to “2”.

I have a table im trying to import my excel file to which ive setup as DECIMAL, with a len of 5,2.

When I run the “Import external data wizard” and specify this excel file and then point it to my existing destination table, then click the MAP button this page of properties wont let me map the excel columns to my pre-defined columns in the table.

It wont show the columns ive defined because they dont meet the criteria it needs. It insists I define new columns and that they have a len property of “19,4”. Ive confirmed this is what its doing because as soon as I edit my underlying table to have a len of 19,4, then when i re-run the import wizard it DOES show me these columns as being possible for mapping.

Why on earth is it insisting I have this 19,4 len, its definitely not what I want.

Can someone assist?

[peterlaursen]

Could you please share a small Excel file and a SHOW CREATE TABLE output that demonstrates this problem?

[arkid77]

'peterlaursen' wrote:

Could you please share a small Excel file and a SHOW CREATE TABLE output that demonstrates this problem?

Hi Peter

As requested…

CREATE TABLE `heathrow` (

`id` int(255) NOT NULL AUTO_INCREMENT,

`postcode` varchar(5) DEFAULT NULL,

`price` decimal(5,2) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=139 DEFAULT CHARSET=utf8

[peterlaursen]

thanks .. we will check tomorrow!

[vishal.pr]

@arkid77

The mapping section will always show the column length and other properties of the column given in the source no matter whether you have selected to import into an existing table. Importing into existing table will never modify the target table structure even though mapping section shows otherwise, these values will have effect only if you drop and recreate the table.

FYI: Import into existing table is controlled by the DDL/DML Optionsin the Advanced Options.

Regards,

Vishal P.R

[arkid77]

'vishal.pr' wrote:

@arkid77

The mapping section will always show the column length and other properties of the column given in the source no matter whether you have selected to import into an existing table. Importing into existing table will never modify the target table structure even though mapping section shows otherwise, these values will have effect only if you drop and recreate the table.

FYI: Import into existing table is controlled by the DDL/DML Optionsin the Advanced Options.

Regards,

Vishal P.R

Hi Vishal

I would suggest that this interface be improved if possible as its quite confusing currently.

The most confusing part is that the drop down lists in the map page didnt even include the columns in my underlying database due to them not matching the eact same len value as the source. So even if it works if you manually type them, the fact you have to do that is very unfriendly and confusing. This to me seems to be the most key thing that could be improved.

Thanks

[vishal.pr]

@arkid77

Quote:

The most confusing part is that the drop down lists in the map page didnt even include the columns in my underlying database due to them not matching the exact same len value as the source.

This was a bug in SQLyog prior to version 9.1 and has no relation to length of the table, in fact SQLyog never considered the length or other properties of a column to list it in the target column field. This bug was fixed in SQLyog 9.1, the release notes says

Import External Data could fail to display all columns of target table when importing from a source containing lesser columns than target.

Please refer to

http://www.webyog.com/blog/2011/06/09/mysql-data-search-data-at-your-finger-tips/

But as you said there may be room for improvement in the current interface if the user selected import into existing table and we already started discussing this. I will update you the status once the decision is made.

Regards,

Vishal P.R

[Author]

Posts