I am not 100% sure what you need, but I have to deal with CR, LF and
CR/LF characters in EDI files quite often. Some customers want
embedded LF characters but do not want the record to actually return.
I.E. One long record, etc. etc. Others want hard returns.
So I have found a solution by using the RsearchAndReplace plugin to
change the line feed or end of file characters in the file before
loading. I can then load the file as I need, either multiple records
or long single records.
In my case, the EDI format is fairly standardized, so I can pick and
choose where to place any CR or EOF characters. This may not work in
your case, but modifying the file before loading works for me.
Bob Thompson
LaPorte, IN
219-363-7441
Sent from my iPod
On Sep 20, 2010, at 3:26 PM, "Sami Aaron" <[email protected]>
wrote:
Is there a way to load a text file into a VARCHAR field if there are
embedded hard-returns within the field in the text file? Is there a
setting to NOT assume the CRLF is the end of record mark?
What happens is that the end of file mark is never reached and the
database just gets bigger & bigger until I kill it.
Here's what my import file looks like:
~39310~|~102991~|~102991~|~AD02956~|~Valuation for Claim No:
AD02956~|~Delivery Date: 09/20/2010 2:12:33 PM
Company: WEST BEND MUTUAL
Claim Number: AD02956
Year: 1994
Make: KOBELCO
Model: SK400 LC III
VIN: 00570
ID: 102991
ACV: $46,793.00~|~09/20/2010 02:12PM~|~4~|~~|~X~|
Everything from "Delivery Date" to the next "~" is data for one
VARCHAR field.
Here's my code to import the file:
SET DELIMIT=NULL
SET DELIMIT='|'
SET QUOTES=NULL
SET QUOTES=~~~~
LOAD ImportEmail FROM .xFilename_Email AS ASCII
SET QUOTES=NULL
SET QUOTES=''''
SET DELIMIT=NULL
SET DELIMIT=','
Thanks for any ideas!
Sami
____________________________
Sami Aaron
Software Management Specialists
913-915-1971
[email protected]
Please consider the environment before printing this e-mail
