Member Avatar for rogenie

I am using the latest phpmyadmin. I have a database and within it is a whole bunch of tables. I want to load a semicolon delimited text file but it doesn't seem to work. Here is the sample of my text file where 55 is the field id. Field title is the country name and the last is the ordering of my countries.

55;Portugal;178
55;Puerto Rico;179
55;Qatar;180
55;Reunion;181
55;Romania;182
55;Russia;183

All i am trying to do is populate the country list so I dont have to enter it manually. Is there any other way of doing this? Please help!

Thank you so much

  • 3 Contributors
  • 3 Replies
  • 187 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by rogenie

Dani AI

Generated

Quick summary and context: phpMyAdmin can import semicolon-delimited files, but the import options must match the file (delimiter, enclosure, line endings and character set). Both ’s suggestion to parse strings in PHP and ’s PHP script are valid fallbacks for small datasets, but for bulk inserts the built-in CSV/LOAD DATA path is simpler and much faster.

phpMyAdmin import checklist (CSV using LOAD DATA):

  • Select the target table, go to Import, choose “CSV using LOAD DATA”.
  • Set “Fields terminated by” to ; and set “Fields enclosed by” to " only if values are quoted.
  • Set “Lines terminated by” to the file’s line ending (\r\n for Windows, \n for Unix) or use AUTO.
  • Choose the correct character set (UTF-8) and skip the first line if it’s a header.
  • Explicitly map the file columns to the table columns (skip auto-increment id fields).
  • If any column name is a reserved word (for example ORDER), escape it with backticks or rename it before importing.

When access to the server is available, LOAD DATA is efficient (example):

LOAD DATA LOCAL INFILE '/path/to/countries.txt'
INTO TABLE countries
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(fieldid, countryname, ordering);

Note: LOCAL may be required and MySQL’s secure-file-priv or client/server settings can block LOAD DATA; use the mysql client on the server if phpMyAdmin restrictions apply.

Quick troubleshooting:

  • Verify each row has the same number of fields as the column list.
  • Remove UTF-8 BOM if present (it breaks the first field).
  • Check PHP upload_max_filesize and phpMyAdmin import limits.
  • Test with a tiny sample and inspect MySQL error output.
  • Confirm table constraints (types, NOT NULL, foreign keys) accept the incoming values.

If phpMyAdmin still fails, the PHP parsing route shown by or a PDO-based importer will work reliably for smaller imports.

Recommended Answers

All 3 Replies

Member Avatar for Shanti C

use explode function to separate them...
see this...

if my post is not reach your requirement...please ignore this...

Member Avatar for R0bb0b

You might have to debug this a little but not much. This is how I would do it:

<?php
$data = "55;Portugal;178
55;Puerto Rico;179
55;Qatar;180
55;Reunion;181
55;Romania;182
55;Russia;183";

$queries = array();
foreach(explode("\n", $data) as $row)
{
    $line = "insert into tablename(fieldid, countryname, order) values(";
    foreach(explode(";", $row) as $col)
    {
        $line .= "'" . $col . "', ";
    }
    $queries[] = substr($line, 0, -2) . ");";
}

mysql_query("begin");
$commit = "commit";
$error = "";
$counter = 1;
foreach($queries as $query)
{
    if($commit == "commit") 
    {
        if(!mysql_query($query))
        {
            $error = "error in query " . $counter . ":" . mysql_error();
            $commit = "rollback";
        }
        $counter++;
    }
}
if($commit == "rollback")
{
    echo "transaction rolled back<br />" . $error;
}
mysql_query($commit);
?>
Edited by mike_2000_17 because: Fixed formatting
Member Avatar for rogenie

Thanks for inputs. still playing with it.

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.