Inserting CSV files into the DB with PHPmyadmin

[Stefany93]

Howdy colleagues,

 

I have like 4000 CSV files that I need to import into the DB keyurl. Each file has 10 columns, however, I only need to dump the keyword column into the DB table keyurl which has these fieds:

    int_glcode keyword

int_glcode is an AUTO_INCREMENT column. However, when I upload my CSV file, I get the error "Invalid column count at line 1" I tried with the "Upload CSV with LOAD DATA" option and I get "only one row inserted" message while in reality only the int_glcode gets added to.

 

I also tried creating a fake table with 10 columns to dump the data from the CSV file, but that failed too. it shows "only one row inserted" message yet again.

 

Please tell me the correct way to do it, I am lost. 

This is the average CSV file that I need to dump:

I just need to fetch the Keyword column, nothing else.

 
Keyword,Visits,% New Visits,New Visits,Bounce Rate,Pages / Visit,Avg. Visit Duration,Transactions,Revenue,Ecommerce Conversion Rate
(not set),53,100.00%,53,50.94%,2.02,00:00:32,0,$0.00,0.00%
ac blower located in 2000 buick lesabre,53,50.94%,27,100.00%,1.00,00:00:00,0,$0.00,0.00%
(not provided),27,0.00%,0,100.00%,1.00,00:00:00,0,$0.00,0.00%
01 lasbre heat and ac diagram,27,0.00%,0,100.00%,1.00,00:00:00,0,$0.00,0.00%
2000 buick lesabre ac blower,27,100.00%,27,100.00%,1.00,00:00:00,0,$0.00,0.00%
2001 buick lesabre blower,27,100.00%,27,100.00%,1.00,00:00:00,0,$0.00,0.00%
fan blower motor for 2001 buick lesabre,27,100.00%,27,100.00%,1.00,00:00:00,0,$0.00,0.00%
fan motor 2001 buick la sabre,27,100.00%,27,0.00%,2.96,00:02:10,0,$0.00,0.00%
how to change a blower motor on a 2000 buick lesabre,27,100.00%,27,100.00%,1.00,00:00:00,0,$0.00,0.00%
hvac blower motor replacement on a 2001 buick lesabre,27,100.00%,27,100.00%,1.00,00:00:00,0,$0.00,0.00%
,322,75.16%,242,83.54%,1.33,00:00:16,0,$0.00,0.00%
 
Day Index,Visits
9/1/08,0
9/2/08,0
 

Thank you very much! 

[Barand]

try

$sql = "LOAD DATA LOCAL INFILE '$filename'
    INTO TABLE keyurl
    FIELDS TERMINATED BY \",\"
    LINES TERMINATED BY \"\n\"
    IGNORE 1 LINES
    (keyword,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy)
";
$db->query($sql) or die($db->error);

[Stefany93]

Thank you very much for the reply but it seems that the files were super weird so neither thing worked. I was forced to built a small Perl script to cut out the first column's contents needed to be dumped into the DB. Here is the script if anyone needs it.

I am sorry, it is without comments because I had to write it in a hurry:

 

 

 

open(my $file_txt, '>>', 'file.txt') or die('could not open the god damn filex');
my @files_for_dump = glob('files_dump\\*');
open(my $file_handle, $file_name); # or die "Could not open file '$filename' $!";
my $count_files = @files_for_dump;
for(my $x = 0; $x < $count_files; $x++){
    open(my $r, $files_for_dump[$x]);
    while(my $line = <$r>){
    my @keywords = split('[,]', $line); 
    my @t = shift(@keywords);
    foreach(@t){
 
        print $file_txt $_."\n"
 
}
}
}

[jazzman1]

What errors did you get using Barand's script?