I'm trying to upload this csv file to mysql on my hosting.
The csv is built by two columns:
alias, id
and than each row contains the data.
Here is an image
But the mysql rejects me. why is that?
-
possible duplicate of Import CSV file directly into MySQLMark Baker– Mark Baker2012-01-03 17:45:41 +00:00Commented Jan 3, 2012 at 17:45
-
How or what does MySQL reject? And is this actually a csv file, or an Excel file?Mark Baker– Mark Baker2012-01-03 17:46:10 +00:00Commented Jan 3, 2012 at 17:46
-
If you are trying to upload a csv file then you have to select the CSV format in the import section. Either mention what kind of tool you are using with version or let us know what it's reject (Error code)..Miko– Miko2012-01-03 17:59:39 +00:00Commented Jan 3, 2012 at 17:59
-
csv do not have columns. Either you show up the excel sheet, or you didn't export it as csv.Esselans– Esselans2012-01-03 18:57:37 +00:00Commented Jan 3, 2012 at 18:57
Add a comment
|
3 Answers
Create a table "test_table " with two fields "alias" and "id", then execute this command:
LOAD DATA LOCAL INFILE '/importfile.csv'
INTO TABLE test_table
IGNORE 1 LINES
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(alias, id);
if your are using phpMyAdmin use the import option of phpMyAdmin. Choose CSV file format
Comments
You can also open up csv from excel and generate series of insert statements. Not the best solution but might be useful if you're looking for something quick and dirty.
Comments
Solution using PHP
$file = 'path/to.csv';
$lines = file($file);
$firstLine = $lines[0];
foreach ($lines as $line_num => $line) {
if($line_num==0) { continue; } //escape the header column
$arr = explode(",",$line);
$column1= $arr[0];
$column2= $arr[1];
echo $column1.$column2."<br />";
//put the mysql insert statement here
}
