CODEWITHSUNDEEP
phphtmlmysqlcsvtextbox
BenTyler
BenTyler

Reputation: 43

Import txt file/data to my mysql database

I have a txt file with 1000 user names in it. They are seperated by lines. (break line).

I wanna import those user names into my mysql table. I've tried the csv method but it's not as simple as uploading a txt file. Because I need to convert the txt file to a csv file and it's a waste of time.

CSV import code:

mysql_connect('localhost', 'user', 'pass') or die(mysql_error());   
mysql_select_db('db_name') or die(mysql_error());   

$lines = file('import.txt');   
$company_names = "";   
$insert_string = "INSERT INTO `ImportedWordList`(`Word`) VALUES";  
$counter = 0;  
$maxsize = count($lines);  
foreach($lines as $line => $company) {  
$insert_string .= "('".$company."')";  
$counter++;  
if($counter < $maxsize){  
$insert_string .= ",";  
}//if  
}//foreach  
mysql_query($insert_string) or die(mysql_error());

I also tried one method that import a txt data BUT the txt file has to be on the server. It also wastes my time, because if we're talking about big files. It's a waste of time & usage to upload those files to the server and only then start to upload them to the database.

Code:

<?php   

//connect to the database  
$connect = mysql_connect("localhost","username","password");  
mysql_select_db("mydatabase",$connect); //select the table  
//  

if ($_FILES[csv][size] > 0) {  

    //get the csv file  
    $file = $_FILES[csv][tmp_name];  
    $handle = fopen($file,"r");  

    //loop through the csv file and insert into database  
    do {  
        if ($data[0]) {  
            mysql_query("INSERT INTO contacts (contact_first, contact_last, contact_email) VALUES  
                (  
                    '".addslashes($data[0])."',  
                    '".addslashes($data[1])."',  
                    '".addslashes($data[2])."'  
                )  
            ");  
        }  
    } while ($data = fgetcsv($handle,1000,",","'"));  
    //  

    //redirect  
    header('Location: import.php?success=1'); die;  

}  

?>  

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
<html xmlns="http://www.w3.org/1999/xhtml">  
<head>  
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />  
<title>Import a CSV File with PHP & MySQL</title>  
</head>  

<body>  

<?php if (!empty($_GET[success])) { echo "<b>Your file has been imported.</b><br><br>"; } //generic success notice ?>  

<form action="" method="post" enctype="multipart/form-data" name="form1" id="form1">  
  Choose your file: <br />  
  <input name="csv" type="file" id="csv" />  
  <input type="submit" name="Submit" value="Submit" />  
</form>  

</body>  
</html>

I'm interested in the best way to import the txt data, maybe using a text box and I'll paste into it the user names and it will upload them or maybe uploading the txt file but it won't keep it on the server, it will just import what's inside of it.

Looking for help, thanks.

Upvotes: 2

Views: 1472

Answers (1)

dg131
dg131

Reputation: 156

Have a look at this code, it should fulfill your purpose :

<?php
$input_file = fopen("your_input_file.txt", "r") or die("Unable to open file!");
$output_file = fopen("your_output_file.txt", "w");
while(!feof($input_file)) {
    $user = fgets($input_file);
    $user = trim($user);
    $query = "insert into table_name values ('" . $user . "');\n";
    fwrite($output_file, $query);
    echo $query;
}
fclose($input_file);
fclose($output_file);
?>

Just replace your_input_file and your_output_file with the filenames of your input and output file.

Some additional info:

  1. I've used trim function to chop off the trailing \n that will be read with the user (since you said your file had new line characters as separators)
  2. The output file will be created if it doesn't exist, so you need not be worried about that. Just enter a valid filename.
  3. The input file must be in the directory where this php codes resides. Similarly the output will be created in that directory.

Upvotes: 2

Related Questions