0

I'm trying to save some ID values from CSV that are automatically converted to exponent numbers by Excel.

Like 382383816413 becomes 3.82384E+11. So I'm doing a full import into my MySQL database with:

LOAD DATA LOCAL INFILE
  'file.csv'
INTO TABLE my_table
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(@`item_id`,
@`colum2`,
@`colum3`)

SET
  item_id = @`item_id`;

I've tried using cast like:

CAST('3.82384E+11' as UNSIGNED) and it gives me just 3.

CAST('3.82384E+11' as BIGINT) and it doesn't work.

CAST('3.82384E+11' as UNSIGNED BIGINT) and gives me 3 again.

So, what's the better way to convert string exponent numbers to real big integers in MySQL?

Leo Ribeiro
  • 1,195
  • 1
  • 14
  • 25
  • 1
    This is an [Excel problem](https://superuser.com/questions/452832/turn-off-scientific-notation-in-excel). Once the CSV data is mangled there's no easy fix. Excel's exponential notation often discards data. – tadman Mar 12 '18 at 17:12
  • 2
    _"Like `382383816413` becomes `3.82384E+11`"_ - you're aware that information has already been lost at this point, right? – CBroe Mar 12 '18 at 17:17

2 Answers2

0

Set column format as text instead of number in excel. Refer below link.

PHPExcel - set cell type before writing a value in it

Sriram Jano
  • 56
  • 7
0

My option was to convert the column with 3.82384E+11 to number in the excel file, so it get back to the original value. Then I export to CSV and use SQL query to import it fine.

Leo Ribeiro
  • 1,195
  • 1
  • 14
  • 25