[mod] Small hack to make CSV import work (for me at least)

[shumisha]

Hello !

I have been trying to import some users with custom attributes (eg firstname, last name, company, title) from some existing excel file, and I bumped into some problems. Browsing this forum, I could get going by turning my csv files from excel into text files, TAB-separated, but even then the import would fail as some of the imported data contained : or , or other special characters. I had to remove them to be able to import, which was not acceptable in some cases. So I looked into it, and I gathered some pieces of codes from the internet that did the trick :

1 - Prepare data :

- The goal is to import an excel data file. As an example, let's say the fields are : email,firstname, lastname, company, title. They need not be in that order, even though for all trials I did they were in this order.
- The fields name must be on the first line. Case does not matter, even though again I have not checked.
- Save the file as a csv file. Because of Excel using various locale separator, and also not enclosing fields with , or ; in double quotes, I suggest you use the following excel macro to create the properly formatted csv instead of the Save as... function from excel :

Code: Select all

Sub SaveAsCSV()
Dim Range As Object, Line As Object, Cell As Object
Dim StrTemp As String

Dim Separateur As String

Separateur = ","
Set Range = ActiveSheet.UsedRange

Open "Export.csv" For Output As #1
For Each Line In Range.Rows
StrTemp = ""
For Each Cell In Line.Cells
StrTemp = StrTemp & Chr(34) & Trim(CStr _
(Cell.Text)) & Chr(34) & Separateur
Next
Print #1, Mid(StrTemp, 1, Len(StrTemp) - 1) '= " "
Next
Close
End Sub


This macro will create a csv file named export.csv (in the current directory) based on the current spreadsheet data. The content of each cell will be enclosed between double-quotes, so as to preserve special characters. The file will look like :

"email","firstname", "lastname", "company", "title"
"theboss@company.com","Johnny left","is Right","Our company","Mng:of:Business"
"thehacker@company.com","buddy","Goodman","Our company","Mkr:of:Business"
.....
.....

2 - Hack phplist

This is only for last version as of today, 2.10.2. I do not know about other version.
You should edit the file /lists/admin/import1.php

- Around line 90, you will find the following code. Comment it out by adding a double forward slash (//) at the beginning of each line, as shown below

Code: Select all

// Check file for illegal characters
// $illegal_cha = array(",", ";", ":", "#","\t");

  //for($i=0; $i<count($illegal_cha); $i++) {
    //if( ($illegal_cha[$i] != $import_field_delimiter) && ($illegal_cha[$i] != $import_record_delimiter) && (strpos($email_list, $illegal_cha[$i]) != false) ) {
      //Fatal_Error($GLOBALS['I18N']->get('invalid_delimiter')." $import_field_delimiter, $import_record_delimiter");return;
   //}
  //};



Please note code shown here is after adding the //. The original code is without these.

- On line 14, just after the line ob_end_flush(), insert the following code :

Code: Select all

// shumisha 2006-04-06
function csv_string_to_array($str){
   $expr="/,(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))/";
   $results=preg_split($expr,trim($str));
   return preg_replace("/^\"(.*)\"$/","$1",$results);}


Please note that this code is not from me,it is a piece of code displayed on this page : http://fr.php.net/fgetcsv by an anonymous poster.

- approx. on line 110, replace the line

Code: Select all

$uservalues = explode($import_field_delimiter,$line);

by the line

Code: Select all

$uservalues = csv_string_to_array($line);

Save the file back and you should be able to import now.

3 - Import your csv file

- in phplist, got to Manageusers, then select import users.

- select import emails with different values for attributes. The hack should also work with the second option (import emails with the same values for attributes), but there is another bug there, and attributes seems not to be correctly populated by phplist. But the first option works fine.

- proceed as a normal import :
- select destination list(s)
- set file to be imported
- in field delimiter, type a comma (,) if your csv file is comma separated. If you have used the excel macro shown above, this will be the case. If your csv is from another source, you may have ; as a delimiter for example. Just type the proper caracter there.
- do enter anything as Record delimiter
- set other parameters as you see fit
- Select send notification email or Make confirmed immediatly as you see fit
- Hit the import button


It works for me !!

[bevan]

Thank you for your contribution!!

Would you mind adding this to an apropriate place in the docs? docs.phplist.com/HomePage

Another solution to saving CSV with the correct delimiters is OpenOffice.org. It's free, opensource, and excellent!!

I'v moved this thread to the answers forum

[shumisha]

Well thank you.

I don't mind if you move it in docs of course (or am I to understand that I should be doing it ?) however, I would suggest you would delay that a little bit so as to have a little more of feedback. I have not tested this on very large lists for example, and generally speaking, there is more testing to do.

regards

[shumisha]

Just to confirm my previous post, I have noticed that when importing, users are set by phplist to receive text msgs instead of HTML. You can change that later, but it is easy to forget !
I'll look into it, maybe it is just a setting.

[shumisha]

My bad, there are errors in my first posts. I realised that while looking in more details in the code. The fix I have proposed is OK for the second option for importing, that is import emails with the same values for attributes. However, if one uses this option, as previously stated, there is a bug which makes the attribute not being filled up properly.

During my tests, it appears this first option worked because :
- with this option, phplist do not perform the test on illegal characters that many people see if using import emails with the same values for attributes
- in my test data, I did not have any comma. If one has commas in some fields, then this would cause importcsv.php some problems

To use the first option, import emails with different values for attributes, I have to apply the same fix to another file. In this case, /lists/admin/import2.php is used, which in turn requires /lists/admin/commonlib/importcsv.php
Solution should be:

1 - insert into importcsv.php same code used before :

Code: Select all

// shumisha 2006-04-06
function csv_string_to_array($str){
   $expr="/,(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))/";
   $results=preg_split($expr,trim($str));
   return preg_replace("/^\"(.*)\"$/","$1",$results);}


2 - replace on line 319 (of importcsv.php)

Code: Select all

# get rid of text delimiters generally added by spreadsheet apps
    $line = str_replace('"','',$line);

    $values = explode($_SESSION["import_field_delimiter"],$line);


by

Code: Select all

  $values = csv_string_to_array($line);


This should work, but I have not made a single test of it. If someone can step in, would be appreciated. If we can validate this on several different cases, then I'll aggregate these few posts into a global one. ...Later !

[shumisha]

Hello,

I have been looking into this a bit more, and finally I think I have come up with something that is working allright.
I have found several more bugs in the import code. When using import emails with the same values for attributes, the attribute were not filled up properly, and a new attribute 'info' was created. Also, the users were not added to the selected list because one variable appeared not be initialised.
When using import emails with different values for attributes, the fix proposed in my last post works. There was also something that caused me problems : the 'Receive html email' field was not set. In this option, it does not appear in the import setup screen. If I try to set up a htmlemail in my imported file, phplist prompts me about what field I want to map this column to, but if I select 'htmlemail' in the drop-down list, I get a MYSQL error.
So I did the dirty thing, I forced all users imported through this option to receive HTML email. This is done on line 474 in /lists/admin/commonlib/pages/importcsv.php. If you want to have it the opposite way, that is all users receive text emails, justs change

Code: Select all

$tmp = '1';

to

Code: Select all

$tmp = '0';

So now the situation is as follow :

1 - Prepare data as per my first post is still valid

remember that the csv file must have name of attribute on the first row if using the import emails with different values for attributes option, whereas the names of attributes must not be included in the csv file if using the import emails with the same values for attributes option. In the latter case, email must be in the first column, and other columns must have data for each attribute (if any) defined in phplist attribute screen, in the same order

2 - I have done many changes to the two files involved in the import process. SO I have attached the modified version to this post :
- import1.zip should be unzipped locally and uploaded in /lists/admin
- importcsv.zip should be unzipped locally and uploade in /lists/admin/commonlib/pages

I think there is a need for the developer of this project to clean this part of the code, as I have found many things that I believe are just from past versions, and the import features have not been updated accordingly with the rest of the code. Also, my changes may not be very neat as I am not really a PHP programmer. Should work most of the time though !

[shumisha]

BTW, there is still something that I could not figure out : if an attribute in the csv file has commas in it, then these commas are discarded upon importation.
for example, customer company name may be : "Big, Strong, Smart & Co". I'd like to keep these commas as they are part of their names. With the files attached to my last post, this company name is imported, but it would look like : Big Strong Smart & Co afterwards.

The tricky part is that the function csv_string_to_array($str) used to break down the line read from csv file does a good job. It keeps the commas where they are supposed to be. I have verified that upon returning from this function, the name is : Big, Strong, Smart & Co (with commas).

But somewhere between there and the time I go check the users using the manage users menu, all commas within fields have disapeared.

I would appreciate some help on this, I don't know where to look.

[dls4]

Hello there, I have posted this question before and received almost ZERO response from anyone in the fora. But I'll give it another shot.

I initially was receiving the following error message on import :
"Fatal Error: Cannot read /tmp/Export.csv1144700496. file is not readable !"

I initially was using the 'saveAs' in Excel to save to a tab-delimited text file and got the same error. The file is set properly - i.e. field names initialized on the first line, starting with email and so on. I checked the files out with BBEdit to make sure there was no extra tab from the Excel file and that had no effect.

I then stumbled upon the import CSV options along with all of the code fixes from this entry (thanks for all the hard work, btw). I have the exported .csv file (see error message) but I'm still getting the same error.

I have a slightly modified directory structure on my site - essentially all of the phplist files are in a directory called 'phplist'. I had to make a couple of code changes in the config.php file with the initial configuration of the software, but all of that seems to work fine now. Could this extra directory be the problem? I don't think it should - all of the directories are the same beyond that - and the 'tmp/' folder generated on import seems to be relative to the import files...

still stumped on this and haven't heard back from anyone...

oh, I'm using a Mac. I know there was an applescript floating around the phpBB forums that helped deal with some wierd linefeed/carriagereturn issues when using Dreamweaver or BBEdit on Macs to edit .tpl files, but I can't find it, and I'm not sure that this would solve any of the issues.

Any help would be fabulous.

[shumisha]

Hi dls4!

Not sure, I'd have to look into the code to see exactly were this error message is generated, but I believe the error is not linked to the file content, but rather to the fact that the /tmp directory, or the /tmp/Export.csv1144700496. is not readable. That it is it does not have the proper read and write attribute.
You set up the tmp directory in config.php, but I think I remember it is an absolute path. So maybe if you set it up as /tmp, then you may not have access to it. You could try changing the location to something well into you install : create for example /path/to/phplist/install/lists/admin/tmp, and set the $tmpdir variable accordingly.

[dls4]

Hey there -

thanks for the prompt reply. I tried your suggestion - I even created a new directory called 'imports' and changed the $tmpdir variable to follow the same path that is set in the init phase of the config.php file (/phplist/lists/admin/imports).

I added a file to that directory through an FTP client, (then deleted it), so I THINK its read/write, but perhaps not, as I am getting a new error. I get the following error as soon as I go to the import page :

'Warning: The temporary directory for uploading () is not writable, so import will fail'

then, on an attempt to import the file (still using the exported .csv file), I get the same error message as before.

irritating..

I checked the code in import1.php, but I couldn't tell if it was using anything odd - it seems to be using fopen() to write the file. We are on a unix server running apache.. that shouldn't be a problem with the ISP would it? I know they got hacked and we had to use a wierd workaround for a certain form of writing a temp file to the server.. but I don't recall whether or not the workaround was for fopen() or not..

might this be the problem?

dls

[dls4]

I've been digging around and it looks like our ISP has disabled 'allow_url_fopen' and won't be reinstating it. I have posted a support request to see if anyone has any suggestions, but I know that our workaround has involved cURL.

I found the code chunk that writes the Fatal Error message, (I think). It starts on line 68 (of the edited file you submitted) -

if( !($fp = fopen ($newfile, "r"))) {
Fatal_Error($GLOBALS['I18N']->get('unreadable')." (".$newfile.")");
return;

got any advice?

[shumisha]

Hmmm, no I don't think this is a problem. I have looked it up and allow_url_fopen does only allow to access remote files (url) using fopen. But what phplist is trying to access here is a local file. So that should not be the issue. I have no idea for now !!

[shumisha]

Re-reading your previous post :

thanks for the prompt reply. I tried your suggestion - I even created a new directory called 'imports' and changed the $tmpdir variable to follow the same path that is set in the init phase of the config.php file (/phplist/lists/admin/imports).

I added a file to that directory through an FTP client, (then deleted it), so I THINK its read/write, but perhaps not, as I am getting a new error. I get the following error as soon as I go to the import page :

'Warning: The temporary directory for uploading () is not writable, so import will fail'

This is definetely an access permission issue. The warning you get is displayed right at the beginnin of the import1.php by this code :

Code: Select all

if (!is_dir($GLOBALS["tmpdir"]) || !is_writable($GLOBALS["tmpdir"])) {
  Warn($GLOBALS['I18N']->get('temp_dir_not_writeable')." (".$GLOBALS["tmpdir"].")");
}

This means that phplist cannot find or write this tmp directory. You should see displayed the name of the tmp dir that phplist is trying to access adn you don't see it. And the reason for that is that $GLOBALS["tmpdir"] is '', as otherwise it would be displayed at the end of the warning text, as can be seen in the php code.

Why is that ? it seems odd that phplist is otherwise working, but only the $tmpdir is not set properly from the config.php ??

[shumisha]

The only reason I can see is that the dir is actually not writeable by the phplist process. Did you tried chmoding it to 777, just to see if it makes a difference. Also do you have safe-mode on ?

[macsurfing]

Thankyou for those files zipped up on here, they solved my email import problems in a flash