Import CSV file in MySQL RDBMS

Hi all, I hope in your help.

I need import in db mysql the file excel .xls.

With the google search I realized I need use the LOAD DATA FILE syntax and that before you import the excel file in mysql I have to export it in CSV format separated by commas.

But I have this problem.

The values of excel file are:

N   Val1   Val2    
1   73,4   0,073

In the csv file export:

N,Val1,Val2			
1,73,4,0,073

When I use LOAD DATA FILE for import to csv file in db mysql the import on the table is:

N   Val1   Val2    
1   73     0

In the table the fields Val1 and Val2 are settings as DECIMAL (10, 2) values.

I need instead in mysql table:

N   Val1   Val2    
1   73,4   0,073

I have tried in LOAD DATA the replace function, without success:

REPLACE(Val1, ',', '.')

Anybody know how can I do that?

Thank you in advance.

All the best
Antonio

Why? Can’t you use another separator, like a pipe - “|” - or something that isn’t in any of the values?

Are you sure? In csv files when there is a comma in a value the whole value must be enclosed in quotes. I can’t believe Excel doesn’t do this. Also, AFAIK, Excel delimits with semicolons.

You may try converting all numerical values to text in Excel, replacing commas with periods and then exporting.

Also, you could try HeidiSQL - it has a tool for importing CSV files and it has option “Input file contains local formatted numbers, e.g. 1.234,56 in Germany” and many other options like specifying delimiter, enclosing character, etc.

Thank you very much, I have solved my problem with your suggestion: now I use separator a pipe - “|”

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.