how to import more than 10 000 emails in one go

[gandalf100]

Is it possible to import more than 10000 emails in one go?

[Hernol]

I dont think so, I think it crash after 1mb file.

[gandalf100]

Well it does.
I increased the value memory_limit in php.ini and it worked for over 60,000 emails.

[ramziddin]

Hi Everybody

I am new to php list and I am really wondering how you managed(more precisely succeeded) to import so many users in one go. I looked to the php.ini and the maximum upload limit is set to 25 MB. But I cannot upload 100 KB of file with user emails. It is only possible to import 2750 emails in one go in my case.
What might be causing this problem?

Could you give me some suggestion on resolving my issue?

Multo Gracio

Ramziddin

[Jason Kuenkler]

And we had a ubbthreads site, and have a mysql database with a table that houses the 62K + email addresses.

How do I tie phplists to the database, so it pulls in new subscriptions and new members whenever we send out a newsletter?

We've tried the csv method, and it's crashing all the time. Is there no way to just use a sql database as it's email source?

[H2B2]

Have you looked at some of the solutions mentioned under the "Tip & Tricks from the forum" section of http://docs.phplist.com/ImportUsersInfo

* phpList import and safe_mode fixed
* Import Emails from another Database Solution
* Importing users without file uploads
* Small hack to make CSV import work (Excel - phplist)
* Import users pre-marked to receive html in CSV file
* Import users pre-marked to receive html in CSV file

[thisisit3]

I've had the same problem, i couldn't import large text files of emails due to an error that the file was too big.

i had to edit the file admin/import1.php and change the limit from 1MB to 5MB, worked fine.

[nigelduc]

I had to import 12,000 names in a 5MB file. I tried the suggestions found in the forum, increasing the memory_limit in php.ini and a limit set in /admin/import1.php and neither worked, I still got the "file too big" error. I searched for that text and found it in /admin/commonlib/pages/importcsv.php line 95 where there is a limit set on the tmp file:

if (filesize($_FILES["import_file"]['tmp_name']) > 1000000) { /* Change 1000000 */

This fixed it for me and I have successfully imported 6MB files. I hope this helps someone else.

Thanks for a great little app!

[chris87]

But I cannot upload 100 KB of file with user emails. It is only possible to import 2750 emails in one go in my case.
What might be causing this problem?

Does someone else has this problem?

I can't imagine the 1-MB-Limit does influence the file that it is impossible to upload a file that has less than 100KB.

I've never uploaded more than 2500 adresses in one upload...


Thanks,

Christoph

[suntereo]

I had to import 12,000 names in a 5MB file. I tried the suggestions found in the forum, increasing the memory_limit in php.ini and a limit set in /admin/import1.php and neither worked, I still got the "file too big" error. I searched for that text and found it in /admin/commonlib/pages/importcsv.php line 95 where there is a limit set on the tmp file:

if (filesize($_FILES["import_file"]['tmp_name']) > 1000000) { /* Change 1000000 */

This fixed it for me and I have successfully imported 6MB files. I hope this helps someone else.

This *DID* work for me too! THANK YOU!! THANK YOU!! You just saved me countess hours of work splitting files into smaller files... well no more splitting needed! Yahoo!!

[rickpcb]

I found a tool for splitting large csv files into smaller ones - it's pretty handy...
unfortunately I can't post the link here... do a google search for split excel csv into smaller files - the result is in the top 5 - it is on fxfisherman dot com in the forums...

[jc19]

nigelduc wrote:
I had to import 12,000 names in a 5MB file. I tried the suggestions found in the forum, increasing the memory_limit in php.ini and a limit set in /admin/import1.php and neither worked, I still got the "file too big" error. I searched for that text and found it in /admin/commonlib/pages/importcsv.php line 95 where there is a limit set on the tmp file:

if (filesize($_FILES["import_file"]['tmp_name']) > 1000000) { /* Change 1000000 */
****************************************
Can you tell me where to access /admin/commonlib/pages/importcsv.php ? Thank you.

[pro2sell]

hi,

are you sick of uploading your csv one by one?

i was


so here's how you can import your lists from a directory on your server:

1. use your favorite ftp-client, goto admin directory and create a new directory "csv" (admin->csv) then upload your *.csv to import
2. insert this line in your config.php @line 49

Code: Select all

$adminpages = '/lists/admin';
$csvUploadDir = getenv("DOCUMENT_ROOT").$adminpages."/csv/";


under $adminpages, not above or your files will NOT be found!!!

3. replace admin/commonlib/pages/importcsv.php with this

Code: Select all

<?php

ob_end_flush();
print '<script language="Javascript" src="js/progressbar.js" type="text/javascript"></script>';

ignore_user_abort();
set_time_limit(500);
$illegal_cha = array (
  ",",
  ";",
  ":",
  "#",
  "\t"
);
$email_list = array ();
$subselect = '';
if (!isset ($GLOBALS['scheme'])) {
  $GLOBALS['scheme'] = 'http';
}
if (!isset($everyone_groupid)) {
  $everyone_groupid = -1;
}

$system_tmpdir = ini_get("upload_tmp_dir");
if (!isset ($GLOBALS["tmpdir"]) && !empty ($system_tmpdir)) {
  $GLOBALS["tmpdir"] = $system_tmpdir;
}
if (!is_dir($GLOBALS["tmpdir"]) || !is_writable($GLOBALS["tmpdir"]) && !empty ($system_tmpdir)) {
  $GLOBALS["tmpdir"] = $system_tmpdir;
}

#if (ini_get("open_basedir")) {
if (!is_dir($GLOBALS["tmpdir"]) || !is_writable($GLOBALS["tmpdir"])) {
  Warn(sprintf($GLOBALS['I18N']->get('The temporary directory for uploading (%s) is not writable, so import will fail'), $GLOBALS["tmpdir"]));
}

if (!isset ($GLOBALS["assign_invalid_default"]))
  $GLOBALS["assign_invalid_default"] = $GLOBALS['I18N']->get('Invalid Email') . ' [number]';

function my_shutdown() {
  # print "Shutting down";
  # print connection_status(); # with PHP 4.2.1 buggy. http://bugs.php.net/bug.php?id=17774
}

function output($msg) {
  if ($GLOBALS['commandline']) {
    @ ob_end_clean();
    $msg = str_replace('<br/>', "\n", $msg);
    print strip_tags($msg);
    @ ob_start();
  } else {
    print $msg . "\n";
  }
}

function parsePlaceHolders($templ, $data) {
  $retval = $templ;
  foreach ($data as $key => $val) {
    if (!is_array($val)) {
      $retval = preg_replace('/\[' . preg_quote($key) . '\]/i', $val, $retval);
    }
  }
  return $retval;
}

function clearImport() {
  if (is_file($_SESSION["import_file"])) {
    unlink($_SESSION["import_file"]);
  }
  $_SESSION["import_file"] = "";
  $_SESSION["systemindex"] = "";
  $_SESSION["import_attribute"] = "";
  $_SESSION["test_import"] = "";
  $_SESSION["assign_invalid"] = "";
  $_SESSION["overwrite"] = "";
}

register_shutdown_function("my_shutdown");
require_once $GLOBALS["coderoot"] . "structure.php";

## identify system values from the database structure
$system_attributes = array ();
reset($DBstruct["user"]);
while (list ($key, $val) = each($DBstruct["user"])) {
  if (!ereg("^sys", $val[1])) {
    $system_attributes[strtolower($key)] = $val[1];
  } #elseif (ereg("sysexp:(.*)",$val[1],$regs)) {
  #$system_attributes[strtolower($regs[1])] = $key;
  #}
}

ob_end_flush();
if (!empty ($_GET["reset"]) && $_GET["reset"] == "yes") {
  clearImport();
  print '<h1>' . $GLOBALS['I18N']->get('Import cleared') . '</h1>';
  print PageLink2($_GET["page"], $GLOBALS['I18N']->get('Continue'));
  return;
} else {
  #  if ($_SESSION["test_import"])
  print '<p>' . PageLink2($_GET["page"] . "&amp;reset=yes", $GLOBALS['I18N']->get('Reset Import session')) . '</p>';
}

if (isset ($_POST["import"])) {
  $test_import = (isset ($_POST["import_test"]) && $_POST["import_test"] == "yes");
  $_SESSION["test_import"] = $test_import;
if (!isset ($_POST['local_import_file'])) {

  if (!$_FILES["import_file"]) {
    Fatal_Error($GLOBALS['I18N']->get('File is either too large or does not exist.'));
    return;
  }
  if (empty ($_FILES["import_file"])) {
    Fatal_Error($GLOBALS['I18N']->get('No file was specified. Maybe the file is too big? '));
    return;
  }

  if (!$GLOBALS['commandline'] && filesize($_FILES["import_file"]['tmp_name']) > (IMPORT_FILESIZE*1000000)) {
    # if we allow more, we will certainly run out of memory
    Fatal_Error($GLOBALS['I18N']->get('File too big, please split it up into smaller ones'));
    return;
  }
  if (!preg_match("/^[0-9A-Za-z_\.\-\s \(\)]+$/", $_FILES["import_file"]["name"])) {
    Fatal_Error($GLOBALS['I18N']->get('Use of wrong characters in filename, allowed are: ') . "0-9A-Za-z[SPACE]_.-()");
    return;
  }
   
  if ($_FILES["import_file"] && $_FILES["import_file"]['size'] > 10) {
    $newfile = $GLOBALS['tmpdir'] . '/' . basename($_FILES['import_file']['name']) . time();
    if (!$GLOBALS['commandline']) {
      move_uploaded_file($_FILES['import_file']['tmp_name'], $newfile);
    } else {
      copy($_FILES["import_file"]['tmp_name'], $newfile);
    }
    $_SESSION["import_file"] = $newfile;
    if (!($fp = fopen($newfile, "r"))) {
      Fatal_Error(sprintf($GLOBALS['I18N']->get('Cannot read %s. file is not readable !'), $newfile));
      return;
    }
    fclose($fp);
  }
  elseif ($_FILES["import_file"]) {
    Fatal_Error($GLOBALS['I18N']->get('Something went wrong while uploading the file. Empty file received. Maybe the file is too big, or you have no permissions to read it.'));
   echo "BLAHR: ".$_POST['local_import_file'];
    return;
  }

   }else{
      $_SESSION["import_file"] = $_POST['local_import_file'];
   }
 
  if (!$_POST["notify"] && !$test_import) {
    Fatal_Error($GLOBALS['I18N']->get('Please choose whether to sign up immediately or to send a notification'));
    return;
  } else {
    $_SESSION["notify"] = $_POST["notify"];
  }
 
  if (isset ($_POST["import_record_delimiter"]) && $_POST["import_record_delimiter"] != "") {
    $_SESSION["import_record_delimiter"] = $_POST["import_record_delimiter"];
  } else {
    $_SESSION["import_record_delimiter"] = "\n";
  }

  if (!isset ($_POST["import_field_delimiter"]) || $_POST["import_field_delimiter"] == "" || $_POST["import_field_delimiter"] == "TAB") {
    $_SESSION["import_field_delimiter"] = "\t";
  } else {
    $_SESSION["import_field_delimiter"] = $_POST["import_field_delimiter"];
  }
  $_SESSION["show_warnings"] = !empty($_POST["show_warnings"]);
  $_SESSION["assign_invalid"] = !empty($_POST["assign_invalid"]);
  $_SESSION["omit_invalid"] = !empty($_POST["omit_invalid"]);
  if (isset($_POST['lists']) && is_array($_POST['lists'])) {
    $_SESSION["lists"] = $_POST["lists"];
  } else {
    $_SESSION["lists"] = array();
  }
  if (isset($_POST['groups']) && is_array($_POST['groups'])) {
    $_SESSION["groups"] = $_POST["groups"];
  } else {
    $_SESSION["groups"] = array();
  }
  $_SESSION["overwrite"] = !empty($_POST["overwrite"]);
  $_SESSION["notify"] = $_POST["notify"]; # yes or no
  $_SESSION["listname"] = $_POST["listname"];
  $_SESSION["retainold"] = !empty($_POST["retainold"]);
  $_SESSION["throttle_import"] = !empty($_POST["throttle_import"]) ? sprintf('%d',$_POST["throttle_import"]):0;
}

if (!empty ($_GET["confirm"])) {
  $_SESSION["test_import"] = '';
}

if ($_SESSION["import_file"]) {
  # output some stuff to make sure it's not buffered in the browser
  for ($i = 0; $i < 10000; $i++) {
    print '  ' . "\n";
  }
  output("<p>" . $GLOBALS['I18N']->get('Reading emails from file ... '));
  flush();
  if (filesize($_SESSION['import_file']) > 50000) {
    @ ini_set("memory_limit", memory_get_usage() + 50 * filesize($_SESSION['import_file']));
  }
  $email_list = file_get_contents($_SESSION["import_file"]);
  flush();
  // Clean up email file
  $email_list = trim($email_list);
  $email_list = str_replace("\r", "\n", $email_list);
  $email_list = str_replace("\n\r", "\n", $email_list);
  $email_list = str_replace("\n\n", "\n", $email_list);

  if ($_SESSION["import_record_delimiter"] != "\n") {
    $email_list = str_replace($_SESSION["import_record_delimiter"], "\n", $email_list);
  };

  # not sure if we need to check on errors
  /*
    for($i=0; $i<count($illegal_cha); $i++) {
      if( ($illegal_cha[$i] != $import_field_delimiter) && ($illegal_cha[$i] != $import_record_delimiter) && (strpos($header, $illegal_cha[$i]) != false) ) {
        $errpos = strpos($email_list, $illegal_cha[$i]);
        $startpos = ( $errpos > 20 ) ? $errpos - 20 : 0;
        print '<h1>';
        printf($GLOBALS['I18N']->get('Error was around here &quot;%s&quot;'),substr( $email_list, $startpos, 40 ));
        print '</h1>';
        printf('<h1>',$GLOBALS['I18N']->get('Illegal character was %s').'</h1>',$illegal_cha[$i]);
        Fatal_Error($GLOBALS['I18N']->get('A character has been found in the import which is not the delimiter indicated, but is likely to be confused for one. Please clean up your import file and try again')." $import_field_delimiter, $import_record_delimiter");
        return;
      }
    };
  */
  #  error_reporting(E_ALL);
  // Split file/emails into array
  $email_list = explode("\n", $email_list); //WARNING the file contents get replace by an array
  output(sprintf('..' . $GLOBALS['I18N']->get('ok, %d lines') . '</p>', sizeof($email_list)));
  $header = array_shift($email_list);
  $header = str_replace('"', '', $header);
  $total = sizeof($email_list);
  $headers = explode($_SESSION["import_field_delimiter"], $header);
  $headers = array_unique($headers);

  ### Compare header line with system and user attributes
  ## build user_attributes array
  $req = Sql_Query(sprintf('select * from %s order by listorder,name', $tables["attribute"]));
  while ($row = Sql_Fetch_Array($req)) {
    $attributes[$row["id"]] = $row["name"];
  }
  $used_systemattr = array ();
  $used_attributes = array ();
  for ($i = 0; $i < sizeof($headers); $i++) {
    $column = clean($headers[$i]);
    #    print $i."<h1>$column</h1>".$_POST['column'.$i].'<br/>';
    $column = preg_replace('#/#', '', $column);
#    $dbg = "Field $i: $headers[$i] - $column - form/option:" . $_POST['column' . $i];
    if (in_array(strtolower($column), array_keys($system_attributes))) {
      #      print "System $column => $i<br/>";
      $_SESSION["systemindex"][strtolower($column)] = $i;
      array_push($used_systemattr, strtolower($column));
#      $dbg .= " =system";
    }  elseif (strtolower($column) == "list membership" || (isset($_POST['column' . $i]) && $_POST['column' . $i] == 'skip')) {
      ## skip was chosen or it's list membership, which we don't want to import since it's too complicated.
      $_SESSION["import_attribute"][$column] = array (
        "index" => $i,
        "record" => 'skip',
        "column" => "$column"
      );
      array_push($used_systemattr, strtolower($column));
#      $dbg .= " =skip";
    } else {
      if (isset ($_SESSION["import_attribute"][$column]["record"]) && $_SESSION["import_attribute"][$column]["record"]) {
        ## mapping has been defined
#        $dbg .= " =known mapping in session: " . $_SESSION["import_attribute"][$column]["record"];
      }
      elseif (isset ($_POST["column$i"])) {
        ## attribute mapping was chosen
        $_SESSION["import_attribute"][$column] = array (
          "index" => $i,
          "record" => $_POST["column$i"],
          "column" => "$column"
        );
#        $dbg .= " =mapping chosen";
      } else {
        ## define mapping based on existing attribute or ask for it
        #@@ Why is $attributes not used
        $existing = Sql_Fetch_Row_Query("select id from " . $tables["attribute"] . " where name = \"$column\"");
        $_SESSION["import_attribute"][$column] = array (
          "index" => $i,
          "record" => $existing[0],
          "column" => $column
        );
        array_push($used_attributes, $existing[0]);
        if ($existing[0]) {
  #        $dbg .= " =known attribute id=" . $existing[0];
        } else {
#          $dbg .= " =request mapping";
        };
      }
    }
    //dbg('Imported column',$dbg, 'import'); //debug
  }
  if (!isset ($_SESSION["systemindex"]["email"])) {
    Fatal_Error($GLOBALS['I18N']->get('Cannot find column with email, please make sure the column is called &quot;email&quot; and not eg e-mail, and that your column delimiter is correct'));
    return;
  }
#  dbg($_SESSION["import_attribute"]);

  ### build option list from known attributes
  $unused_systemattr = array_diff(array_keys($system_attributes), $used_systemattr);
  $unused_attributes = array_diff(array_keys($attributes), $used_attributes);
  $options = '<option value="new">-- ' . $GLOBALS['I18N']->get('Create new one') . '</option>';
  $options .= '<option value="skip">-- ' . $GLOBALS['I18N']->get('Skip Column') . '</option>';
  foreach ($unused_systemattr as $sysindex) {
    $options .= sprintf('<option value="%s">%s</option>', $sysindex, substr($system_attributes[$sysindex], 0, 25));
  }
  foreach ($unused_attributes as $attindex) {
    $options .= sprintf('<option value="%s">%s</option>', $attindex, substr(stripslashes($attributes[$attindex]), 0, 25));
  }
  ### use above selector for each unknown imported attribute
  $ls = new WebblerListing($GLOBALS['I18N']->get('Import Attributes'));
  $request_mapping = 0;
  foreach ($_SESSION["import_attribute"] as $column => $rec) {
    if (trim($column) != '' && !$rec["record"]) {
      $request_mapping = 1;
      $ls->addElement($column);
      $ls->addColumn($column, $GLOBALS['I18N']->get('select'), '<select name="column' . $rec["index"] . '">' . $options . '</select>');
    }
  }
  if ($request_mapping) {
    $ls->addButton($GLOBALS['I18N']->get('Continue'), 'javascript:document.importform.submit()');
    print '<p>' . $GLOBALS['I18N']->get('Please identify the target of the following unknown columns') . '</p>';
    print '<form name="importform" method="post">';
    print $ls->display();
    print '</form>';
    return;
  }
}

### show summary
if ($_SESSION["test_import"]) {
  $ls = new WebblerListing($GLOBALS['I18N']->get('Summary'));
  foreach ($_SESSION["import_attribute"] as $column => $rec) {

    if (trim($column) != '') {
      $ls->addElement($column);
      if ($rec["record"] == "new") {
        $ls->addColumn($column, $GLOBALS['I18N']->get('maps to'), $GLOBALS['I18N']->get('Create new Attribute'));
      }
      elseif ($rec["record"] == "skip") {
        $ls->addColumn($column, $GLOBALS['I18N']->get('maps to'), $GLOBALS['I18N']->get('Skip Column'));
      } elseif (is_numeric($rec["record"])) {
        $ls->addColumn($column, $GLOBALS['I18N']->get('maps to'), $attributes[$rec["record"]]);
      } else {
        $ls->addColumn($column, $GLOBALS['I18N']->get('maps to'), $system_attributes[$rec["record"]]);
      }

    }
  }
  print $ls->display();
  print '<h3>';
  printf($GLOBALS['I18N']->get('%d lines will be imported'), $total);
  print '</h3>';
  print '<p>' . PageLink2($_GET["page"] . "&amp;confirm=yes", $GLOBALS['I18N']->get('Confirm Import')) . '</p>';
  print '<p><h1>' . $GLOBALS['I18N']->get('Test Output') . '</h1></p>';
#  dbg($_SESSION["import_attribute"]);
}

### show progress and adjust working space
if (sizeof($email_list)) {
  $import_field_delimiter = $_SESSION["import_field_delimiter"];
  if (sizeof($email_list) > 300 && !$_SESSION["test_import"]) {
    # this is a possibly a time consuming process, so show a progress bar
    print '<script language="Javascript" type="text/javascript"> document.write(progressmeter); start();</script>';
    flush();
    # increase the memory to make sure we are not running out
    #    $mem = sizeof($email_list);
    ini_set("memory_limit", "32M");
  }

  ### store the chosen mappings in the $system_attribute_mapping list
  # print "A: ".sizeof($import_attribute);
  foreach ($system_attributes as $key => $val)
    if (isset ($_SESSION["systemindex"][$key]))
      $system_attribute_mapping[$key] = $_SESSION["systemindex"][$key];

  # #Bas bugfix 0008106: import 'foreignkey' fails; bad SQL
  # When the user chose to map an unknown import attribute to a system attribute this attribute ends up in
  # $_SESSION["import_attribute"]. This code moves the attribute to the system mappings array
  foreach ($_SESSION["import_attribute"] as $column => $item) {
    if (!is_numeric($item["record"]) && $item["record"] != 'new' && $item["record"] != 'skip') {
      $system_attribute_mapping[$item["record"]] = $item["index"];
      unset ($_SESSION["import_attribute"][$column]);
    };
  };
  //  dbg('$system_attribute_mapping', $system_attribute_mapping); //debug
  //  dbg('$_SESSION["import_attribute"]',$_SESSION["import_attribute"]); //debug

  ### Parse the lines into records
  #  print "<br/>Loading emails .. ";
  flush();
  $count = array ();
  $count["email_add"] = 0;
  $count["exist"] = 0;
  $count["list_add"] = 0;
  $count["group_add"] = 0;
  $c = 1;
  $count["invalid_email"] = 0;
  $num_lists = sizeof($_SESSION["lists"]);
  $total = sizeof($email_list);
  $cnt = 0;
  $count["emailmatch"] = 0;
  $count["fkeymatch"] = 0;
  $count["dataupdate"] = 0;
  $additional_emails = 0;
  foreach ($email_list as $line) {
    # will contain attributes to store / change
    $user = array ();
    # get rid of text delimiters generally added by spreadsheet apps
    $line = str_replace('"', '', $line);

    $values = explode($_SESSION["import_field_delimiter"], $line);
    $system_values = array ();
    foreach ($system_attribute_mapping as $column => $index)
      $system_values[$column] = $values[$index];
    ## Check completeness
    $index = clean($system_values["email"]);
    $invalid = 0;
    if (!$index) {
      if ($_SESSION["show_warnings"])
        Warn($GLOBALS['I18N']->get('Record has no email') .
        ": $c -> $line");
      $index = $GLOBALS['I18N']->get('Invalid Email') . " $c";
      $system_values["email"] = $_SESSION["assign_invalid"];
      $invalid = 1;
      $count["invalid_email"]++;
    }

    //print ("<pre>" . var_dump($_SESSION["import_attribute"]) . "</pre>"); // debug
    //    dbg('_SESSION["import_attribute"',$_SESSION["import_attribute"]); //debug

    if (sizeof($values) != (sizeof($_SESSION["import_attribute"]) + sizeof($system_attributes)) && !empty($_SESSION['test_import']) && !empty($_SESSION["show_warnings"]))
      Warn("Record has more values than header indicated (" .
      sizeof($values) . "!=" .
       (sizeof($_SESSION["import_attribute"]) + sizeof($system_attributes)) .
      "), this may cause trouble: $index");
    if (!$invalid || ($invalid && $_SESSION["omit_invalid"] != "yes")) {
      $user["systemvalues"] = $system_values;
      reset($_SESSION["import_attribute"]);
      $replace = array ();
      while (list ($key, $val) = each($_SESSION["import_attribute"])) {
        $user[$val["index"]] = addslashes($values[$val["index"]]);
        $replace[$key] = addslashes($values[$val["index"]]);
      }
    } else {
      # Warn("Omitting invalid one: $email");
    }
    $user["systemvalues"]["email"] = parsePlaceHolders($system_values["email"], array_merge($replace, $system_values, array (
      "number" => $c
    )));
    $user["systemvalues"]["email"] = clean($user["systemvalues"]["email"]);
    $c++;
    if ($_SESSION["test_import"]) {
      $html = '';
      foreach ($user["systemvalues"] as $column => $value) {
        if ($value) {
          $html .= "$column -> $value<br/>\n";
        } else {
          $html .= "$column -> " . $GLOBALS['I18N']->get('clear value') . "<br/>\n";
        }
      }
      reset($_SESSION["import_attribute"]);
      foreach ($_SESSION["import_attribute"] as $column => $item) {
        if ($user[$item["index"]]) {
          if ($item["record"] == "new") {
            $html .= ' ' . $GLOBALS['I18N']->get('New Attribute') . ': ' . $item["column"];
          }
          elseif ($item["record"] == "skip") {
            # forget about it
            $html .= ' ' . $GLOBALS['I18N']->get('Skip value') . ': ';
          } else {
            $html .= $attributes[$item["record"]];
          }
          $html .= " -> " . $user[$item["index"]] . "<br>";
        }
      }
      if ($html)
        print '<blockquote>' . $html . '</blockquote>';
    } else {
      # do import
      ## create new attributes
      foreach ($_SESSION["import_attribute"] as $column => $item) {
        if ($item["record"] == "new") {
          Sql_Query(sprintf('insert into %s (name,type) values("%s","textline")', $tables["attribute"], addslashes($column)));
          $attid = Sql_Insert_id();
          Sql_Query(sprintf('update %s set tablename = "attr%d" where id = %d', $tables["attribute"], $attid, $attid));
          Sql_Query("create table " . $GLOBALS["table_prefix"] . "listattr_attr" . $attid . "
                      (id integer not null primary key auto_increment, name varchar(255) unique,
                      listorder integer default 0)");
          $_SESSION["import_attribute"][$column]["record"] = $attid;
        }
      }
      $new = 0;
      $cnt++;
      if ($cnt % 25 == 0) {
        output("<br/>$cnt/$total");
        flush();
      }
      if (!empty($user["systemvalues"]["foreign key"])) {
        $result = Sql_query(sprintf('select id,uniqid from %s where foreignkey = "%s"', $tables["user"], $user["systemvalues"]["foreign key"]));
        # print "<br/>Using foreign key for matching: ".$user["systemvalues"]["foreign key"];
        $count["fkeymatch"]++;
        $exists = Sql_Affected_Rows();
        $existing_user = Sql_fetch_array($result);
        # check whether the email will clash
        $clashcheck = Sql_Fetch_Row_Query(sprintf('select id from %s
                  where email = "%s"', $tables["user"], $user["systemvalues"]["email"]));
        if ($clashcheck[0] != $existing_user["id"]) {
          $duplicatecount++;
          $notduplicate = 0;
          $c = 0;
          while (!$notduplicate) {
            $c++;
            $req = Sql_Query(sprintf('select id from %s where email = "%s"', $tables["user"], $GLOBALS['I18N']->get('duplicate') .
            "$c " . $user["systemvalues"]["email"]));
            $notduplicate = !Sql_Affected_Rows();
          }
          if (!$_SESSION["retainold"]) {
            Sql_Query(sprintf('update %s set email = "%s" where email = "%s"', $tables["user"], "duplicate$c " .
            $user["systemvalues"]["email"], $user["systemvalues"]["email"]));
            addUserHistory("duplicate$c " . $user["systemvalues"]["email"], "Duplication clash ", ' User marked duplicate email after clash with imported record');
          } else {
            if ($_SESSION["show_warnings"])
              print Warn($GLOBALS['I18N']->get('Duplicate Email') . ' ' . $user["systemvalues"]["email"] . $GLOBALS['I18N']->get(' user imported as ') . '&quot;' . $GLOBALS['I18N']->get('duplicate') . "$c " . $user["systemvalues"]["email"] . "&quot;");
            $user["systemvalues"]["email"] = $GLOBALS['I18N']->get('duplicate') . "$c " . $user["systemvalues"]["email"];
          }
        }
      } else {
        $result = Sql_query(sprintf('select id,uniqid from %s where email = "%s"', $tables["user"], $user["systemvalues"]["email"]));
        # print "<br/>Using email for matching: ".$user["systemvalues"]["email"];
        $count["emailmatch"]++;
        $exists = Sql_Affected_Rows();
        $existing_user = Sql_fetch_array($result);
      }
      if ($exists) {
        // User exist, remember some values to add them to the lists
        $count["exist"]++;
        $userid = $existing_user["id"];
        $uniqid = $existing_user["uniqid"];
      } else {
        // user does not exist
        $new = 1;
        // Create unique number
        mt_srand((double) microtime() * 1000000);
        $randval = mt_rand();
        # this is very time consuming when importing loads of users as it does a lookup
        # needs speeding up if possible
        $uniqid = getUniqid();
        $confirmed = $_SESSION["notify"] != "yes" && !preg_match("/Invalid Email/i", $index);

        $query = sprintf('INSERT INTO %s (email,entered,confirmed,uniqid)
                  values("%s",now(),%d,"%s")', $tables["user"], $user["systemvalues"]["email"], $confirmed, $uniqid);
        $result = Sql_query($query, 1);
        $userid = Sql_insert_id();
        if (!$userid) {
          # no id returned, so it must have been a duplicate entry
          if ($_SESSION["show_warnings"])
            print Warn($GLOBALS['I18N']->get('Duplicate Email') . ' ' . $user["systemvalues"]["email"]);
          $c = 0;
          while (!$userid) {
            $c++;
            $query = sprintf('INSERT INTO %s (email,entered,confirmed,uniqid)
                          values("%s",now(),%d,"%s")', $tables["user"], $user["systemvalues"]["email"] .
            " ($c)", 0, $uniqid);
            $result = Sql_query($query, 1);
            $userid = Sql_insert_id();
          }
          $user["systemvalues"]["email"] = $user["systemvalues"]["email"] . " ($c)";
        }

        $count["email_add"]++;
        $some = 1;
      }

      reset($_SESSION["import_attribute"]);
      if ($new || (!$new && $_SESSION["overwrite"] == "yes")) {
        $query = "";
        $count["dataupdate"]++;
        $old_data = Sql_Fetch_Array_Query(sprintf('select * from %s where id = %d', $tables["user"], $userid));
        $old_data = array_merge($old_data, getUserAttributeValues('', $userid));
        $history_entry = $GLOBALS['scheme'] . '://' . getConfig("website") . $GLOBALS["adminpages"] . '/?page=user&id=' . $userid . "\n\n";
        foreach ($user["systemvalues"] as $column => $value) {
          $query .= sprintf('%s = "%s",', $column, $value);
        }
        if ($query) {
          $query = substr($query, 0, -1);
          # this may cause a duplicate error on email, so add ignore
          Sql_Query("update ignore {$tables["user"]} set $query where id = $userid");
        }
        foreach ($_SESSION["import_attribute"] as $item) {
          if (isset ($user[$item["index"]]) && $item['record'] != 'skip') {
            $attribute_index = $item["record"];
            $uservalue = $user[$item["index"]];
            # check whether this is a textline or a selectable item
            $att = Sql_Fetch_Row_Query("select type,tablename,name from " . $tables["attribute"] . " where id = $attribute_index"); ////
            switch ($att[0]) {
              case "select" :
              case "radio" :
                $val = Sql_Query("select id from $table_prefix" . "listattr_$att[1] where name = \"$uservalue\"");
                # if we do not have this value add it
                if (!Sql_Affected_Rows()) {
                  Sql_Query("insert into $table_prefix" . "listattr_$att[1] (name) values(\"$uservalue\")");
                  Warn("Value $uservalue added to attribute $att[2]");
                  $user_att_value = Sql_Insert_Id();
                } else {
                  $d = Sql_Fetch_Row($val);
                  $user_att_value = $d[0];
                }
                break;
              case "checkbox" :
                if ($uservalue && $uservalue != "off")
                  $user_att_value = "on";
                else
                  $user_att_value = "off";
                break;
              case "date" :
                $user_att_value = parseDate($uservalue);
                break;
              default :
                $user_att_value = $uservalue;
                break;
            }

            Sql_query(sprintf('replace into %s (attributeid,userid,value) values(%d,%d,"%s")', $tables["user_attribute"], $attribute_index, $userid, $user_att_value));
          } else {
            if ($item["record"] != "skip") {
              # add an empty entry if none existed
              Sql_Query(sprintf('insert ignore into %s (attributeid,userid,value) values(%d,%d,"")', $tables["user_attribute"], $item["record"], $userid));
            }
          }
        }
        $current_data = Sql_Fetch_Array_Query(sprintf('select * from %s where id = %d', $tables["user"], $userid));
        $current_data = array_merge($current_data, getUserAttributeValues('', $userid));
        $information_changed = 0;
        foreach ($current_data as $key => $val) {
          if (!is_numeric($key))
            if (isset($old_data[$key]) && $old_data[$key] != $val && $old_data[$key] && $key != "password" && $key != "modified") {
              $information_changed = 1;
              $history_entry .= "$key = $val\n*changed* from $old_data[$key]\n";
            }
        }
        if (!$information_changed) {
          $history_entry .= "\nNo user details changed";
        }
        addUserHistory($user["systemvalues"]["email"], "Import by " . adminName(), $history_entry);
      }

      #add this user to the lists identified
      if (is_array($_SESSION["lists"])) {
        reset($_SESSION["lists"]);
        $addition = 0;
        $listoflists = "";
        while (list ($key, $listid) = each($_SESSION["lists"])) {
          $query = "replace INTO " . $tables["listuser"] . " (userid,listid,entered) values($userid,$listid,now())";
          $result = Sql_query($query, 1);
          # if the affected rows is 2, the user was already subscribed
          $addition = $addition || Sql_Affected_Rows() == 1;
          $listoflists .= "  * " . $_SESSION["listname"][$key] . "\n";
        }
        if ($addition)
          $count["list_add"]++;
        if (!TEST && $_SESSION["notify"] == "yes" && $addition) {
          $subscribemessage = ereg_replace('\[LISTS\]', $listoflists, getUserConfig("subscribemessage", $userid));
          if (function_exists('sendmail')) {
            sendMail($user["systemvalues"]["email"], getConfig("subscribesubject"), $subscribemessage, system_messageheaders(), $envelope);
            if (isset ($_SESSION["throttle_import"])) {
              sleep($_SESSION["throttle_import"]);
            }
          }
        }
      }
      if (!is_array($_SESSION["groups"])) {
        $groups = array ();
      } else {
        $groups = $_SESSION["groups"];
      }
      if (isset ($everyone_groupid) && !in_array($everyone_groupid, $groups)) {
        array_push($groups, $everyone_groupid);
      }
      if (defined('IN_WEBBLER') && is_array($groups)) {
        #add this user to the groups identified
        reset($groups);
        $groupaddition = 0;
        while (list ($key, $groupid) = each($groups)) {
          if ($groupid) {
            $query = "replace INTO user_group (userid,groupid) values($userid,$groupid)";
            $result = Sql_query($query);
            # if the affected rows is 2, the user was already subscribed
            $groupaddition = $groupaddition || Sql_Affected_Rows() == 1;
          }
        }
        if ($groupaddition)
          $count["group_add"]++;
      }
    } // end else
    if ($_SESSION["test_import"] && $c > 50)
      break;
  }

  ### Summary of finished import
  if (!$_SESSION["test_import"]) {
    print '<script language="Javascript" type="text/javascript"> finish(); </script>';

    $report = "";
    if (empty($some) && !$count["list_add"]) {
      $report .= '<br/>' . $GLOBALS['I18N']->get('All the emails already exist in the database and are member of the lists');
    } else {
      $report .= sprintf('<br/>' . $GLOBALS['I18N']->get('%s emails succesfully imported to the database and added to %d lists.'), $count["email_add"], $num_lists);
      $report .= sprintf('<br/>' . $GLOBALS['I18N']->get('%d emails subscribed to the lists'), $count["list_add"]);
      if ($count["exist"]) {
        $report .= sprintf('<br/>' . $GLOBALS['I18N']->get('%s emails already existed in the database'), $count["exist"]);
      }
    }
    if ($count["invalid_email"]) {
      $report .= sprintf('<br/>' . $GLOBALS['I18N']->get('%d Invalid Emails found.'), $count["invalid_email"]);
      if (!$_SESSION["omit_invalid"]) {
        $report .= sprintf('<br/>' . $GLOBALS['I18N']->get('These records were added, but the email has been made up from ') . $_SESSION["assign_invalid"]);
      } else {
        $report .= sprintf('<br/>' . $GLOBALS['I18N']->get('These records were deleted. Check your source and reimport the data. Duplicates will be identified.'));
      }
    }
    if ($_SESSION["overwrite"] == "yes") {
      $report .= sprintf('<br/>' . $GLOBALS['I18N']->get('User data was updated for %d users'), $count["dataupdate"]);
    }
    $report .= sprintf('<br/>' . $GLOBALS['I18N']->get('%d users were matched by foreign key, %d by email'), $count["fkeymatch"], $count["emailmatch"]);
    if (!$GLOBALS['commandline']) {
      print $report;
      if (function_exists('sendmail')) {
        sendMail(getConfig("admin_address"), $GLOBALS['I18N']->get('phplist Import Results'), $report);
      }
      if (function_exists('logevent')) {
        logEvent($report);
      }
    } else {
      output($report);
    }
    clearImport();
  } else {
    printf($GLOBALS['I18N']->get('Test output<br/>If the output looks ok, click %s to submit for real') . '<br/><br/>', PageLink2($_GET["page"] . "&amp;confirm=yes", $GLOBALS['I18N']->get('Confirm Import')));
  }

  print '<p>' . PageLink2($_GET["page"], $GLOBALS['I18N']->get('Import some more emails'));

  return;
}
?>


<ul>
<?php print formStart('enctype="multipart/form-data" name="import"');?>
<?php

if ($GLOBALS["require_login"] && !isSuperUser()) {
  $access = accessLevel("import2");
  if ($access == "owner")
    $subselect = " where owner = " . $_SESSION["logindetails"]["id"];
  elseif ($access == "all") $subselect = "";
  elseif ($access == "none") $subselect = " where id = 0";
}

if (Sql_Table_Exists($tables["list"])) {
  $result = Sql_query("SELECT id,name FROM " . $tables["list"] . " $subselect ORDER BY listorder");
  $c = 0;
  if (Sql_Affected_Rows() == 1) {
    $row = Sql_fetch_array($result);
    printf('<input type=hidden name="listname[%d]" value="%s"><input type=hidden name="lists[%d]" value="%d">%s <b>%s</b>', $c, stripslashes($row["name"]), $c, $row["id"], $GLOBALS['I18N']->get('Adding users to list'), stripslashes($row["name"]));
  } else {
    print '<p>' . $GLOBALS['I18N']->get('Select the lists to add the emails to') . '</p>';
    while ($row = Sql_fetch_array($result)) {
      printf('<li><input type=hidden name="listname[%d]" value="%s"><input type=checkbox name="lists[%d]" value="%d">%s', $c, stripslashes($row["name"]), $c, $row["id"], stripslashes($row["name"]));
      $some = 1;
      $c++;
    }

    if (!$some) {
      echo $GLOBALS['I18N']->get('No lists available') . ' ' . PageLink2("editlist", $GLOBALS['I18N']->get('Add a list'));
    }
  }
}

if (defined('IN_WEBBLER') && Sql_Table_Exists("groups")) {
  $result = Sql_query("SELECT id,name FROM groups ORDER BY listorder");
  $c = 0;
  if (Sql_Affected_Rows() == 1) {
    $row = Sql_fetch_array($result);
    printf('<p><input type=hidden name="groupname[%d]" value="%s"><input type=hidden name="groups[%d]" value="%d">Adding users to group <b>%s</b></p>', $c, $row["name"], $c, $row["id"], $row["name"]);
    ;
  } else {
    print '<p>' . $GLOBALS['I18N']->get('Select the groups to add the users to') . '</p>';
    while ($row = Sql_fetch_array($result)) {
      if ($row["id"] == $everyone_groupid) {
        printf('<li><input type=hidden name="groupname[%d]" value="%s"><input type=hidden name="groups[%d]" value="%d"><b>%s</b> - ' . $GLOBALS['I18N']->get('automatically added'), $c, $row["name"], $c, $row["id"], $row["name"]);
      } else {
        printf('<li><input type=hidden name="groupname[%d]" value="%s"><input type=checkbox name="groups[%d]" value="%d">%s', $c, $row["name"], $c, $row["id"], $row["name"]);
        ;
      }
      $some = 1;
      $c++;
    }
  }
}



?>

</ul>

<table border="1">
<tr><td colspan=2>
<?php echo $GLOBALS['I18N']->get('importintro')?>
</td></tr>
<tr><td><?php echo $GLOBALS['I18N']->get('File containing emails')?>:<br/>
</td><td><input type="file" name="import_file">
<br/><?php printf($GLOBALS['I18N']->get('uploadlimits'),ini_get("post_max_size"),ini_get("upload_max_filesize"));printf($GLOBALS['I18N']->get('phplistuploadlimit'),IMPORT_FILESIZE);echo "<p>DIR: ".$csvUploadDir;?><br><select name="local_import_file" size="5"><?foreach (glob($csvUploadDir."*.csv") as $filename) {echo "<option value='".$filename."'>".str_replace($csvUploadDir,'',$filename)." - Size: " . filesize($filename) . " - Date: ".date("d. M. Y: ",filectime($filename))."</option>";}?>
</select></p></td></tr><tr><td><?php echo $GLOBALS['I18N']->get('Field Delimiter')?>:</td><td><input type="text" name="import_field_delimiter" size=5> <?php echo $GLOBALS['I18N']->get('(default is TAB)')?></td></tr>
<tr><td><?php echo $GLOBALS['I18N']->get('Record Delimiter')?>:</td><td><input type="text" name="import_record_delimiter" size=5> <?php echo $GLOBALS['I18N']->get('(default is line break)')?></td></tr>
<tr><td colspan=2><?php echo $GLOBALS['I18N']->get('testoutput_blurb')?></td></tr>
<tr><td><?php echo $GLOBALS['I18N']->get('Test output')?>:</td><td><input type="checkbox" name="import_test" value="yes"></td></tr>
<tr><td colspan=2><?php echo $GLOBALS['I18N']->get('warnings_blurb')?></td></tr>
<tr><td><?php echo $GLOBALS['I18N']->get('Show Warnings')?>:</td><td><input type="checkbox" name="show_warnings" value="yes"></td></tr>
<tr><td colspan=2><?php echo $GLOBALS['I18N']->get('omitinvalid_blurb')?></td></tr>
<tr><td><?php echo $GLOBALS['I18N']->get('Omit Invalid')?>:</td><td><input type="checkbox" name="omit_invalid" value="yes"></td></tr>
<tr><td colspan=2><?php echo $GLOBALS['I18N']->get('assigninvalid_blurb')?>
</td></tr>
<tr><td><?php echo $GLOBALS['I18N']->get('Assign Invalid')?>:</td><td><input type="text" name="assign_invalid" value="<?php echo $GLOBALS["assign_invalid_default"]?>"></td></tr>
<tr><td colspan=2><?php echo $GLOBALS['I18N']->get('overwriteexisting_blurb')?></td></tr>
<tr><td><?php echo $GLOBALS['I18N']->get('Overwrite Existing')?>:</td><td><input type="checkbox" name="overwrite" value="yes"></td></tr>
<tr><td colspan=2><?php echo $GLOBALS['I18N']->get('retainold_blurb')?></td></tr>
<tr><td><?php echo $GLOBALS['I18N']->get('Retain Old User Email')?>:</td><td><input type="checkbox" name="retainold" value="yes"></td></tr>
<tr><td colspan=2><?php echo $GLOBALS['I18N']->get('sendnotification_blurb')?></td></tr>
<tr><td><?php echo $GLOBALS['I18N']->get('Send&nbsp;Notification&nbsp;email')?>&nbsp;<input type="radio" name="notify" value="yes"></td><td><?php echo $GLOBALS['I18N']->get('Make confirmed immediately')?>&nbsp;<input type="radio" name="notify" value="no"></td></tr>
<tr><td colspan=2><?php echo $GLOBALS['I18N']->get('If you are going to send notification to users, you may want to add a little delay between messages')?></td></tr>
<tr><td><?php echo $GLOBALS['I18N']->get('Notification throttle')?>:</td><td><input type="text" name="throttle_import" size=5> <?php echo $GLOBALS['I18N']->get('(default is nothing, will send as fast as it can)')?></td></tr>

<tr><td><input type="submit" name="import" value="<?php echo $GLOBALS['I18N']->get('Import')?>"></td><td>&nbsp;</td></tr>
</table>
</p>
</form>


4. goto manage users->import users->import emails with different values for attributes
5. select your file from file list
6. make your choices of import
7. be happy

btw, don't forget to hack -> phplistuploadlimit if you have files bigger than 1mb -> admin/lib.php @line 75 ...

cheers,
pro2sell

PS: the upload/import as before still works

[Dragonrider]

nigelduc wrote:
I had to import 12,000 names in a 5MB file. I tried the suggestions found in the forum, increasing the memory_limit in php.ini and a limit set in /admin/import1.php and neither worked, I still got the "file too big" error. I searched for that text and found it in /admin/commonlib/pages/importcsv.php line 95 where there is a limit set on the tmp file:

if (filesize($_FILES["import_file"]['tmp_name']) > 1000000) { /* Change 1000000 */
****************************************
Can you tell me where to access /admin/commonlib/pages/importcsv.php ? Thank you.

look in your lists folder, download lists/admin/commonlib/pages/importcsv.php into a TEXT editor, or use your webspace cpanel TEXT editor, find the line, and make your alterations, then re-save your file (make a backup before amending it, just in case).

[moinhaidar]

Hi,
Having ubuntu hardy I tweaked following files in order to import 100000+ emails in one go...

/etc/php5/cli/php.ini
fixed upload_max_filesize from 2 MB to 30 MB
/etc/php5/apache2/php.ini
fixed upload_max_filesize from 2 MB to 30 MB
[PHPList] admin/lib.php
fixed IMPORT_FILESIZE from 1 to 3

Having no success, tweaked admin/commonlib/pages/importcsv.php and fixed IMPORT_FILESIZE of this line

Code: Select all

if (!$GLOBALS['commandline'] && filesize($_FILES["import_file"]['tmp_name']) > (IMPORT_FILESIZE*3000000))

to 3000000

but in vain!!!

I could not make it beyond 1 MB(around 40000 emails) at a time

any suggestion would be appreciable...

--
Moin Haidar