exporting users not tab delimited

Once you've installed phpList Version 2... ask questions here!
Forum rules
Please help the volunteers to help you by supplying the version of phpList you are using, browser & version and if possible, a link to your phpList installation. This is for Version 2 of phpList (the orange one).

exporting users not tab delimited

Postby bluelotus » 6:58pm, Wed 25 Jan, 2006

I tried to export all the users in my database, followed the instructions, etc., but the .cvs file that was exported has all the categories clumped into one column, instead of having separate columns for name, email, etc.

Any ideas on how to fix this would be greatly appreciated! I'd hate to have to do it manually... ;-)
bluelotus
phpLister
 
Posts: 12
Joined: 6:39am, Tue 17 Jan, 2006

Postby carterco » 7:28pm, Wed 25 Jan, 2006

Can you describe the steps you took? Did you do this from:

Lists -> View Members --> Export CSV?

Or

Main Page -> Export

I just did the latter and was able to open in Excel and Notepad++ no problems. I'm doing the other method now...

OK, both went off without a hitch. How about, well, you can't very well post the exported file (yikes) but ... more info? Include your phpList version
Last edited by carterco on 7:35pm, Wed 25 Jan, 2006, edited 1 time in total.
carterco
PL Geek
 
Posts: 76
Joined: 1:04am, Wed 12 Oct, 2005
Location: Pasadena, California, USA

Postby carterco » 7:31pm, Wed 25 Jan, 2006

clumped into one column, instead of having separate columns


What were you expecting? (no sarcasm intended)

This is one column, it's a text file. between each field's value is a tab. open the file in Excel. If for some reason it doesn't automatically parse the columns go ahead and open it, wait, a list:
  1. Launch excel and open your exported file.
  2. click the column heading
  3. Menu: Data -> Text to Columns
  4. The wizard will probably correctly intuit that it's tab delimited, so just Next, Next, Finish...

let us know if this clarifies or fixes the issue.
(text file = one column, rows are carriage return/linefeeds, no other way to possibly interpret a text file)
carterco
PL Geek
 
Posts: 76
Joined: 1:04am, Wed 12 Oct, 2005
Location: Pasadena, California, USA

Postby bluelotus » 9:59pm, Wed 25 Jan, 2006

Hi,

Whenever I have exported other databases to a .csv file it opens with the right information in the right colums. So that's what I was expecting, I guess.

Anyway, I tried what you suggested and nothing changed. Any other ideas?

Thanks!
bluelotus
phpLister
 
Posts: 12
Joined: 6:39am, Tue 17 Jan, 2006

Postby bluelotus » 10:04pm, Wed 25 Jan, 2006

Oh, I got it! I needed to highlight the column to make any changes... Thanks for your help!
bluelotus
phpLister
 
Posts: 12
Joined: 6:39am, Tue 17 Jan, 2006

Postby carterco » 10:17pm, Wed 25 Jan, 2006

OK, let's see... (switching computers).

I tried what you suggested and nothing changed.


Gotta have more info, sport, expand on that "nothing"... nothing changed? errors? file would not open (and no error)? are you:

  • Able to open the file in Excel, in any way?
  • If not able to open, does Excel give you an error?
  • If you did open, did you follow the steps i listed for text data to columns, if so, what happened.
  • Do you save the file to disk or try to open it directly from the browser (often a huge problem: save it to disk then open it).
  • if you saved the file to disk, open in Notepad (or better still, Notepad++) -- is there any data?


Specficity: exactly what are the dialog boxes messages, if you get errors.

Because you might be using phpList to export, begininning with the ID column as the first column, Excel may gripe, thinking it's an SYLK file. Ignore excel's warning.
http://support.microsoft.com/kb/q215591/

Finally, you're using IE or Firefox or Safari? If you don't know how to prevent it from automatically opening switch browsers for a few minutes.

We'll get this... be patient.
carterco
PL Geek
 
Posts: 76
Joined: 1:04am, Wed 12 Oct, 2005
Location: Pasadena, California, USA

Postby carterco » 10:32pm, Wed 25 Jan, 2006

Coool. Our replies overlapped.

Glad to have been of help.

I also noticed an odd, well, quirky behavior: copy your .csv file and name it anything .txt

open this dot text file.

no problem, right?

apparently excel behaves subtly different based upon the extension. I also played with opening the file in Notepad++ and changing the first column from "ID" to "myID", again, excel behaved very differently.

Anyway, glad we got it solved!
Last edited by carterco on 5:21pm, Sun 05 Feb, 2006, edited 1 time in total.
carterco
PL Geek
 
Posts: 76
Joined: 1:04am, Wed 12 Oct, 2005
Location: Pasadena, California, USA

Postby bluelotus » 10:41pm, Wed 25 Jan, 2006

Yeah... I've always thought that Excel and Word have a mind of their own... Not my favorite programs... :wink:

Thanks again!
bluelotus
phpLister
 
Posts: 12
Joined: 6:39am, Tue 17 Jan, 2006

It's broken because it isn't a CSV to begin with

Postby portsd » 8:55pm, Tue 26 Sep, 2006

This is really a bug in PHPList. The exported file that comes out of PHPList isn't a Comma-Separated-Value sheet. It's a Tab-Delimited text file. Solution for Excel:

(1) Rename the exported file from .csv to .txt
(2) In Excel, Open the .txt file and import using the tab-delimited option

More complex method (if you really insist on having a .csv):

(1) Open the .csv file in a "real" text editor and replace the \t values with a "," (comma, minus the quotes) using regular expressions
(2) Save the .csv file and try that in Excel

Real fix:

PHPList needs to be updated so that it exports actual CSV files or leave as-is and let us know that this is actually a tab-delimited text export.
portsd
phpLister
 
Posts: 5
Joined: 11:17pm, Wed 19 Apr, 2006
Location: San Diego, CA

Re: It's broken because it isn't a CSV to begin with

Postby H2B2 » 9:08pm, Tue 26 Sep, 2006

portsd wrote:Solution for Excel:

Have you tried this setting in config.php?
Code: Select all
# the mime type for the export files. You can try changing this to
# application/vnd.ms-excel to make it open automatically in excel
$export_mimetype = 'application/csv';

# if you want to use export format optimized for Excel, set this one to 1
define("EXPORT_EXCEL",0);
H2B2
Moderator
 
Posts: 7188
Joined: 1:51am, Wed 15 Mar, 2006

Postby lsander » 1:11am, Mon 19 Feb, 2007

I changed the application/csv to application/vnd.ms-excel, and I changed the Export_Excel value to 1. The resulting exported files open OK in Excel 2003, but if I am exporting the ID field, I get the SYLK error message described in a previous post. When I get it, I just click OK, and everything proceeds properly.

Another problem is with the lists to which each user subscribes, presented in the rightmost column of the exported file. You just get a run-on list of lists, separated by a space. If you've set up your list names without spaces, you could parse that column and get the individual lists, I suppose. My list names include spaces, so this column is something of a mess.

The whole Export thing is a bit too buggy for a good program like PHPlist:

. The format of the exported files seems not really to be CSV (Comma Separated Values)
. Optimizing for Excel requires setting an obscure flag in config.php
. The first characters of the exported file can be ID, which Excel doesn't like.
. The lists are all run together. Give us a separator that we can parse, please, or put each list in its own column.
lsander
PL Geek
 
Posts: 57
Joined: 4:12pm, Thu 11 Jan, 2007
Location: Pittsburgh, Pennsylvania, USA

Patch, Fix possible is "strtolower".

Postby alekals » 12:27pm, Mon 25 Jun, 2007

ORIGINAL VERSION:(from line 72 to line 82)
Code: Select all
[72] if (is_array($cols)) {
[73]     while (list ($key,$val) = each ($DBstruct["user"])) {
[74]       if (in_array($key,$cols)) {
[75]         if (!ereg("sys",$val[1])) {
[76]           print $val[1].$col_delim;
[77]         } elseif (ereg("sysexp:(.*)",$val[1],$regs)) {
[78]           print $regs[1].$col_delim;
[79]         }
[80]       }
[81]     }
[82]    }



PATCHED VERSION: (from line 72 to line 86)
Code: Select all
  if (is_array($cols)) {
    while (list ($key,$val) = each ($DBstruct["user"])) {
      if (in_array($key,$cols)) {
        if (!ereg("sys",$val[1])) {
          print $val[1].$col_delim;
        } elseif (ereg("sysexp:(.*)",$val[1],$regs)) {
           if ( (ereg("ID",$val[1]) ) && (EXPORT_EXCEL) ) {
              print strtolower($regs[1]).$col_delim;
           }else{
              print $regs[1].$col_delim;
           }     
        }//end elseif       
      }
    }// end while
  }


Excel bug:
http://support.microsoft.com/kb/215591/it
alekals
phpList newbie
 
Posts: 1
Joined: 8:00am, Thu 31 May, 2007

Postby wilsoncortez » 10:27pm, Fri 09 Nov, 2007

Hi

The above solution doesn´t work for me.:-(

it only allows a "smooth bypass" the excell warning but the file still is messy.

The only way that worked for me was open excell go to data>import>external data and choose the csv file just exported from phpList.

Another thing, to allow further manipulation of that file in MS Excell, I had to save It in XML format.

Can you guys say if that´s a MS EXcell or a phplist bug?

Thanks
wilsoncortez
PL Nut
 
Posts: 15
Joined: 8:53pm, Fri 09 Nov, 2007

Possible answer?

Postby rontom » 3:10am, Mon 15 Dec, 2008

I found some info and posted my solution at

http://forums.phplist.com/viewtopic.php?p=57861#57861

To me it looks like it is problem of how to open/import it to Excel and not a problem with the ID

Ron
rontom
PL Nut
 
Posts: 27
Joined: 10:09am, Thu 12 Feb, 2009


Return to Question & Problems - after installing

Who is online

Users browsing this forum: No registered users and 6 guests