i have some textarea fields that contain several lines and include line breaks. when i export for excel, i don't want the line breaks in the export file because excel interprets them as new record. how can i get these to all run together as just one field without line breaks? thanks

You should be able to do a substitution. If I can just remember what the regular expression is for carriage returns and linefeeds.

Try $text =~ s/\n/ /g;

or

$text =~ s/\r/ /g;

(That's a space between the last two / characters so that your words don't run together after removing the new line. You may have to put quotes around the space. I haven't tried this out.)

The difference between the two is that the \n is the newline character and the \r is the return. I can never remember which one is used when. Use the one that works.


JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.

thanks for pointing me in the right direction. i found the code i needed in sub split_encode or sub join_encode:

$tmp =~ s/\n/``/g; # Change newline to `` symbol.

i let the regular db.cgi code do its thing. then in export.pl, i added the above before it actually writes the field.

i also changed double quotes to single quotes, and the csv field delimiter to a comma so that excel can open more easily. works great.

now my only problem is i can't assign a default sort order to the export. i have a default sort order set up in config file, but want a different sort field in the export. i'll search here and see if i can find anything. i added a hidden field along with mh (so it exports all records), but it doesn't work.

I'm having the same problem with the text area. I'm a newbie at this an am not following your changes. What did you change and which sub routine?

I don't even have an export.pl

i have my export subroutines in a separate .pl file, but you can put them wherever you want:

sub csv_record {
# --------------------------------------------------------
# writes record in CSV format
my (%rec) = @_; # Load any defaults to put in the VALUE field.
my ($tmp);
my $num = 0;

# Export all non-admin fields (admin form length = -2)
for (my $i =0; $i <= $#db_cols; $i++) {
if ($num > 0) {print CSVFILE ",";}
$tmp = $rec{$db_cols[$i]};
$tmp =~ s/\n/``/g; # Change newline to `` symbol. THIS IS THE IMPORTANT PART
$tmp =~ s/"/'/g; # change double quote to single quote
print CSVFILE qq|"$tmp"|;
# print CSVFILE qq|"$rec{$db_cols[$i]}"|; # commented this out
$num += 1;
}
print CSVFILE "\n";
}

Thank you very much!

Bill

I notice that at the end of each record the last field shows a box symbol when I open it in excel. Don't see this in the default.db. Any idea what this is? Can I get rid of it?

Bill

Also noticed that since I change ID to RecordID so that excel would import the csv file I no longer have an ID number when I go to add a record. Can you tell me where that is referenced so I make all of the necessary changes?

Sorry. I must have just missed it. It is fixed. Still don't know what is causing the box symbol at the end of each record when imported into excel.

I'm feeling really stupid. I changed the $db_key = 'ID' to ='RecordID' in default.cfg. Now instead of seeing what you added when you go to record added succesfully, it doesn't show any info.

I guess it now can't find the record number?