Importing Excel sheet into phpMyAdmin

问题:

I'm currently playing around with phpMyAdmin and I have encountered a problem. When importing my CSV into phpMyAdmin it's rounding the numbers. I have set the column to be a float and the column in Excel to be a Number (Also tried text/General) to no avail. Has anyone else encountered this issue and found a viable work-around?

A second question, is it possible for me to upload the CSV file so that it matches the column names in phpMyAdmin to Excel column names and enters the data in the correct column?

回答1:

1. Your file should be look like this(decimal fields are of general type):

xlssheet

2. Save as CSV. File will be probably saved with ; separated

This is for new table:

3. Open phpMyAdmin, choose your database, click to import and select file to upload
4. Change format to CSV if there is not selected
5. Change in format specific options - columns separated with: ;
6. Be sure that checkbox (The first line of the file contains the table column names (if this is unchecked, the first line will become part of the data)) is SELECTED
7. Click Go
8. New table will be created with the structure according to the forst line in CSV.

This is for existing table:

3. Open phpMyAdmin, choose your database, CHOOSE YOUR TABLE which match the structure of imported file, click to import and select file to upload
4. Change format to CSV if there is not selected
5. Change in format specific options - columns separated with: ;
6. Change skip number of queries to 1 (this will skip the first line with column names)
7. Click Go
8. Selected table wich has the same structure as CSV will be updated and rows in CSV inserted.

回答2:

// connecting dB
$mysqli = new mysqli('localhost','root','','testdB');

// opening csv
$fp = fopen('data.csv','r');

// creating a blank string to store values of fields of first row, to be used in query
$col_ins = '';

// creating a blank string to store values of fields after first row, to be used in query
$data_ins = '';

// read first line and get the name of fields
$data = fgetcsv($fp);
for($field=0;$field< count($data);$field++){
    $col_ins = "'" . $col[$field] . "' , " . $col_ins;
}

// reading next lines and insert into dB
while($data=fgetcsv($fp)){
    for($field=0;$field<count($data);$field++){
        $data_ins = "'" . $data[$field] . "' , " . $data_ins;
    }
    $query = "INSERT INTO `table_name` (".$col_ins.") VALUES(".$data_ins.")";
    $mysqli->query($query);
}    
echo 'Imported...';

回答3:

I've had the same issue. Solved changing the separator between the integer part and the decimal part from comma to point.

i.e. 365,40 to 365.40

That worked for me.