Member Avatar for cliffcc

How to just import one column of csv.file into mysql? Thanks!

  • 2 Contributors
  • 3 Replies
  • 95 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by Stefano Mtangoo

Dani AI

Generated

Two practical options to import just one CSV column into MySQL: use MySQL server-side loading for speed, or stream-parse the CSV in PHP so you can filter/validate each row. This answers directly. pointed to a search, so below are compact, actionable examples and quick gotchas.

Fast, server-side (LOAD DATA): map CSV fields into user variables and set only the column you want.

LOAD DATA LOCAL INFILE '/path/to/file.csv'
INTO TABLE target_table
CHARACTER SET 'utf8'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(@col1, @col2, @col3)
SET target_column = NULLIF(@col2, '');

This reads three CSV fields and assigns the second field into target_column. Use LOCAL only if the client and server allow it (see MySQL docs for LOAD DATA INFILE).
MySQL LOAD DATA INFILE

Controlled, row-by-row (PHP + PDO + fgetcsv): good when you need validation or complex transformations.

<?php
$pdo = new PDO('mysql:host=localhost;dbname=mydb;charset=utf8mb4','dbuser','dbpass',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$pdo->beginTransaction();
$stmt = $pdo->prepare('INSERT INTO target_table (target_column) VALUES (:val)');
if (($h = fopen('/path/to/file.csv','r')) !== false) {
    fgetcsv($h); // skip header
    while (($row = fgetcsv($h)) !== false) {
        $val = trim($row[1]); // zero-based: 1 = second column
        if ($val === '') continue;
        $stmt->execute([':val' => $val]);
    }
    fclose($h);
}
$pdo->commit();
?>

For CSV parsing details see PHP's fgetcsv docs.
PHP fgetcsv

Troubleshooting notes: ensure correct delimiter/encapsulation and remove BOM if present; use transactions or batch inserts for speed; consider INSERT IGNORE or ON DUPLICATE KEY for duplicates; if LOAD DATA fails, check local_infile server/client settings and file permissions.

Recommended Answers

All 3 Replies

Member Avatar for cliffcc

the link have been removed!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.