0

i have some big .csv files.

i want to load them to mySql with LOAD DATA INFILE.

but the files have lines that the same.

how could i load the files to mySql without the same lines?

this is my source:

// Import csv data in table in respective rows.
$sql = "LOAD DATA LOCAL INFILE '$position' INTO TABLE temp 
        FIELDS TERMINATED BY '  '
        LINES TERMINATED BY '\n'
        IGNORE $i LINES
        (CHROM, POS, ID, REF, ALT, QUAL, FILTER, INFO)";


// Execute query
if (mysqli_query($config,$sql)) {
    echo "Table populated successfully.\n";
} else {
    echo "Error populating table: " . mysqli_error($config) . ".\n";
}

in the mySql i have the columns: CHROM, POS, ID, REF, ALT, QUAL, FILTER, INFO.

i can know that it same lines if CHROM && POS equal in different lines.

i can have same lines JUST in different files.

ldoroni
  • 629
  • 1
  • 7
  • 18
  • Your English isn't very good, it's hard to understand what you want. You want to prevent an error if there are duplicate lines? – Barmar Dec 27 '14 at 07:10
  • @Barmar no, maybe example will be better: if i have 3 files with the same line, i want that in mySql, that line will be just 1 time (not 3 times). – ldoroni Dec 27 '14 at 07:15
  • I don't think you can do that with MySQL. It can either ignore all duplicates or report all duplicates as errors, it doesn't care whether they're in the same file or not. – Barmar Dec 27 '14 at 07:17
  • 2
    The primary purpose of the 'LOAD DATA INFILE ...' MySQL feature is speed and it will not bother with duplicates. You want to use the ' ... ON DUPLICATE KEY UPDATE ...' syntax one the data on, you can do it in two steps. Check this http://stackoverflow.com/questions/15271202/mysql-load-data-infile-with-on-duplicate-key-update – marekful Dec 27 '14 at 07:18

1 Answers1

0

IGNORE INTO TABLE temp instead of INTO TABLE. When you create the table use DISTINCT(field)

Zarrao Zaga
  • 169
  • 2
  • 8