That would actually remove all the carriage returns and then when the data
was migrated to the new database the carraige returns that are in the fields
would not exist.
Using the same methodoligy I think this would be a better approach:
1) Export the CSV file with any field delimiter you want. Use the text
"line_end" as the row delmiter. When your file is exported any CR or LF
characters in the file will be those that are in fields (which you do want
to preserve through the migration).
2) Use the following perl statments to replace all CRLF's, then LFCR's, then
LF's then CR's with the text "carriage_return_linefeed". The last segment of
the statement replaces "line_end" with CRLF giving you a nicely formated
file delimited by LF's.
cat yourfile.csv | perl -pe 's:\n\r:carriage_return_linefeed:g;' | perl -pe
's:\r\n:carriage_return_linefeed:g;' | perl -pe
's:\n:carriage_return_linefeed:g;' | perl -pe
's:\r:carriage_return_linefeed:g;' | perl -pe 's:line_end:\n:g;' >
yourfile-migr.csv
3) Import your data into the new database and then use a replace function
native to your database to replace "carriage_return_linefeed" with the ASCII
character 10 and character 13 (that's CRLF).
Let me know how it works for you,
Camden
----- Original Message -----
From: "CF-LINUX" <[EMAIL PROTECTED]>
To: "CF-Linux" <[EMAIL PROTECTED]>
Sent: Wednesday, February 07, 2001 1:07 PM
Subject: RE: Deleting carrage returns in a CSV file
> cat filename|perl -pe 's:\r::g;' > newfile
>
> -p perform action on each line of input
> -e execute the following commands
> s substitute
> : (first character after s) delimits :pattern:replace:
> \r regexp to match (return)
> [EMPTY] pattern to replace (between ::)
> g apply globally (not just to first occurance) on the line
>
> Try that and let me know if it works.
>
> Rob
>
> -----Original Message-----
> From: Frank Hilliard [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, February 07, 2001 3:27 AM
> To: CF-Linux
> Subject: Re: Deleting carrage returns in a CSV file
>
>
> Let me try to explain the issue again, now that I've worked through it a
> couple of times. When you export a table from Access you can select the
> delimiter you want. I've been using
> semicolons because there were commas inside my tables. There also were a
> large number of carrage returns because the table contained HTML. Any
> exported Access txt or csv file also will
> contain carrage returns at the end of each line no matter what you select.
> Neither the PSQL "copy" command nor the PGAccess import utility will
accept
> carrage returns anywhere in the
> file. This means they have to be eliminated. It is impossible to use a
text
> editor to automatically "replace" carrage returns because they are
> indistinguishable from spaces. However, if
> you know where they are, you can manually delete them with the backspace
> key. I have detailed my experiences in a document posted on the Vancouver
> CFUG site here:
> http://cfug-vancouver.com/postgresqlport.cfm
>
> If anyone has some simple and elegant way to avoid this hassle, I'd love
to
> see it. I'm especially interested in a methodology in which Cold Fusion in
> Linux is used to "insert data into
> postgres". Sound like magic to me. :)
>
> Frank Hilliard
>
> Jason Brooke wrote:
>
> > They would be ok in the postgres database - but Frank said the means by
> which
> > he is importing the data, is using carriage returns as row delimiters -
> that's
> > why I suggested doing a simple select * from the access database. then
> loop
> > over the result set inserting the data into postgres from a cf script.
It
> > won't care about the carriage returns
> >
> > jason
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-linux%40houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_linux or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
