Decimals Added To Varchar #'s After Import

[ahillenb]

When I import an Excel spreadsheet, if any of the fields are a number, SQLyog adds a .0 to the end of it. It doesn't matter whether I set the format of the cell's in excel to number or general, or if I set the import settings of the columns to varchar. The numbers are still coming in with .0 for varchar.

Please advise,

Thanks

[ashwin]

Hi,

Could you please provide us the dummy excel file where it is reproducible?

Actually, It is the driver that interprets datatypes from the source. There is a setting in the driver- Rows to scan. Change from Start-> Run-> ODBC data source administrator-> User DSN-> Select the Excel driver-> Options-> Rows to scan. This option specifies the number of rows to scan to determine the data type of each column. The data type is determined given the maximum number of kinds of data found. You need the increase the number of rows initially scanned(Refer screen-shot)The maximum value to be specified is 16. If you have have numbers in first 16 rows then it won't interpret as varchar and therefore a .0 to the end of it. There should be some text in atleast first four rows for the driver to interpret as varchar. Same thing would result using same driver and settings with any application.

Regards,

Ashwin

[pgoloskie]

I am having the same problem. I have tried the solution listed above to change the Rows To Scan to 16, but this did not help.

[ashwin]

@pgoloskie: Could you please share the excel file where this is reproducible?

[Author]

Posts