CODEWITHSUNDEEP
phpmysqlmysql-error-1064
user1133158
user1133158

Reputation: 71

Import CSV file into the MySQL database

I have records into .CSV file and I want to import them into MySQL database.

Whenever I import the .CSV I get the message Import has been successfully finished... but only 79 out of 114 records are be inserted into the database.

When I try to import the .CSV file with 411 records, just 282 are be inserted. The CSV file which got 411 records includes two categories of records Active and Sold whereby 114 records are Active.

Has someone gotten this type of problem? If so what should be done?

Upvotes: 6

Views: 1237

Answers (3)

user1594629
user1594629

Reputation: 17

I had this problem too. Even though its a bit old and these recommendations go in one direction here is the solution I found. I was creating a large database and import and had the same thing happen, after trial and trials I realized that a key was created somehow assigned (I didn't recognize it because Iwas using the new skin and always use the old skin). Took out the key that somehow got assigned not by me and left the primary only and boom, absorbed the data upload. Also, had issues with view now, and request times out with respect to your question. I pushed up the viewer display a lot to thousands and now stuck, cannot access the config file anywhere with my CP. So as will hang and hosting customer support to lazy to read my concerns and override it on their end-I will have to remove whole table instead of any DROP as can't even run SQL as freezes with overload. So, food for thought would be to keep you table view down, which sucks like in my case b/c I need to look at 17,000 rcords visually quickly to ensure my .csv was correct rather then functions as if issues then can spot them and correct in the control which makes more sense to me anyway.

Upvotes: 1

Cybercartel
Cybercartel

Reputation: 12582

I wrote my own csv importer with php. I use php command fgetcsv to read the csv file and then I use mysql insert command in a loop.

$handle = fopen($this->file, "r");
$i=0;
$delimiter = ($this->fieldDelimiter == 'TAB') ? chr(9) : $this->fieldDelimiter;
while (($data = fgetcsv($handle, 10000, $delimiter)) !== FALSE)
{
     $mydata[] = $data;
}
fclose ($handle);
reset ($mydata);
if ($this->CSVhasTitle)
{
      $mydata = array_slice($mydata,1); //delete first row
}

Then I loop through my array and I use mysql insert: 

foreach ($mydata as $value) 
{
     INSERT INTO $table (...) VALUES (....)
}

But I add exact columnnames into the array before the loop. I've an array of all columnames.

Upvotes: 1

Justin Swanhart
Justin Swanhart

Reputation: 1856

Take a look at your CSV file. It very likely contains something like

1,2,"some data",1
2,5,"data,with,comma",2

If you don't specify COLUMNS OPTIONALLY ENCLOSED BY '"' (SINGLE_QUOTE DOUBLE_QUOTE SINGLE_QUOTE) then the commas embedded in the string data in the second row, third column will not be imported properly.

Check the CSV to see what enclosure character is being used and specify that in the phpmyadmin interface.

Upvotes: 0

Related Questions