0

I am importing a .csv into a MySql table using LOAD DATA INFILE and would like to find a way around columns containing formatting like "6.10111E+11" -- this should import as "610111447853" but is instead 610111000000. The table col is VARCHAR as sometimes there are letters and those are necessary. Formatting the .csv column as numeric before saving it in the shared location does not seem to work. Can I specify some form of "Set" on that column upon import?

here is my code:

$query = <<<eof
    LOAD DATA LOCAL INFILE '/home/c/Documents/UTC/UTC.csv'
     INTO TABLE UTC_import
    FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
     IGNORE 1 LINES
eof;
caro
  • 863
  • 3
  • 15
  • 36
  • `6.10111E+11` is never going to become `610111447853` without further information: at best it becomes `610111000000`—the remaining precision has been lost! – eggyal Mar 28 '15 at 22:00
  • yes that is what i am getting, 610111000000, you're right. How can I get the correct original number? – caro Mar 28 '15 at 22:01
  • It's not in the CSV file, so you will have to revert to the source from which it came. – eggyal Mar 28 '15 at 22:01
  • well shiz I can't use load data infile with xlsx. – caro Mar 28 '15 at 22:04
  • Sure you can. You just need to be more careful when generating the CSV; for example if you first [format the cells as text](http://www.extendoffice.com/documents/excel/671-excel-change-number-to-text.html#formatcell), then the generated CSV should import into MySQL fine. – eggyal Mar 28 '15 at 22:06
  • aight I'll get on it. thanks! – caro Mar 28 '15 at 22:07

0 Answers0