Jump to content


Photo

Decimals Added To Varchar #'s After Import


  • Please log in to reply
3 replies to this topic

#1 ahillenb

ahillenb

    Newbie

  • Members
  • Pip
  • 1 posts

Posted 21 March 2012 - 11:41 PM

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

#2 ashwin

ashwin

    Advanced Member

  • Members
  • PipPipPip
  • 275 posts
  • Gender:Male
  • Location:Bangalore, India

Posted 22 March 2012 - 05:16 AM

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

Attached Files



#3 pgoloskie

pgoloskie

    Newbie

  • Members
  • Pip
  • 1 posts

Posted 02 August 2012 - 10:29 PM

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.

#4 ashwin

ashwin

    Advanced Member

  • Members
  • PipPipPip
  • 275 posts
  • Gender:Male
  • Location:Bangalore, India

Posted 03 August 2012 - 06:41 AM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users