It's now solved, and thanks to all who suggested things for me. It turns
out I was using REReplace to convert the CRs to CR/LF in the original files
(using the function as follows: <cfset variable = #REReplace("#variable#",
"#CHR(10)#", "#CHR(13)##CHR(10)# ")#> ) when in fact the CRs in the
original file weren't CRs at all but LFs. (Hex 0A) Where is my ASCII table
when I need it! DOH!!!
So, using the following is a much more elegant way to convert the UNIX file
into a DOS file for processing through the custom tag CFX_CSVtoQuery :
<cfset LF = chr(13)>
<Cfset CRLF = chr(10) & chr(13)>
<cfset rawCSV = replace(rawCSV,LF,CRLF,"ALL")>
Once the file is processed in this way properly, the custom tag just flies
through the file and does what it's supposed to do as advertised.
My most sincere thanks to everyone for their help, and I'm considering
nominating James Silva (www.gruden.com) for Australian of the Year.
Cheers,
Mike Kear
Windsor, NSW, Australia
AFP WebWorks
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:cf-talk@;linenoize.com]
Sent: Wednesday, 30 October 2002 6:57 AM
To: CF-Talk
Subject: Re: DSN-less connection to a CSV file?
The same way I described. :-) You just use CFDIRECTORY to determine the
name of the file. CFFILE to read in the file. Replace() to replace the LFs
to CRLFs. CFFILE to save the changes.
Then do your usual import routine with CF.
Put that in CFSCHEDULER and it would be automatic.
-Novak
----- Original Message -----
From: "Michael Kear" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, October 29, 2002 12:01 AM
Subject: RE: DSN-less connection to a CSV file?
> Thanks but everything has to happen daily and fully automatically. How do
I
> do that in CF?
>
> Cheers,
> Mike Kear
> Windsor, NSW, Australia
> AFP WebWorks
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:cf-talk@;linenoize.com]
> Sent: Tuesday, 29 October 2002 7:13 PM
> To: CF-Talk
> Subject: Re: DSN-less connection to a CSV file?
>
> Quick and dirty fix is to open the file using CFFILE then replace all of
the
> LFs with CRLFs... then save the file. I've done this in the past without
> any problem. You will also want to make sure that there are no LFs
anywhere
> that there shouldn't be...
>
> -Novak
>
> ----- Original Message -----
> From: "Michael Kear" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Monday, October 28, 2002 11:17 PM
> Subject: RE: DSN-less connection to a CSV file?
>
>
> > Well I have the CFX_CSVtoQuery tag, I've installed it, and I can read
in
> > comma delimited text files but not these CSV files I needed to use in
the
> > first place. It reads the file but says zero rows processed. When I
open
> > the .csv file in my trusty UltraEdit, it asks if I want to convert it to
> DOS
> > format, so I took a look at the .csv file in hex and the line endings
only
> > have 0A not 0D 0A. (in other words a line feed, not carriage return
line
> > feed) So I think the tag only recognises the file as a single line.
> >
> > Anyone know what I can do about this? I can't have the information
> > supplier make any change to the files - its' created automatically and
> FTP'd
> > into our site anew every day.
> >
> > Cheers,
> > Mike Kear
> > Windsor, NSW, Australia
> > AFP WebWorks
> >
> >
> > IF you're interested here's some extra data - the first few lines of one
> of
> > the files concerned (can't post more for copyright reasons), plus the
code
> > I'm trying to use to access it.
> >
> > First the file:
> >
> > Cash Management Accounts - $1,000.00
> > "Institution Name","Account/Product Name","Effective Rate","Nominal
> > Rate","Website Link","Customer Service Phone"
> > "Institution Number One","Youbeauty
> > Account",4.8025,4.70,"http://www.institutionone.com.au","1800-666-666";
> > "Institution number two","Cash Management
> > Account",4.3337,4.25,"http://www.institutiontwo.com.au","1800-555-555";
> >
> >
> > Now the code:
> >
> > <CFSET CSV_StartRow = 2>
> >
> > <CFX_CSVToQuery
> > FILE="C:\Inetpub\wwwroot\testdir\td6m20k.csv"
> > NAME="MyQuery"
> > ROWNUMBERS="LineNum"
> > MAXROWS="15"
> > STARTROW="#CSV_StartRow#"
> > LONGROWSARE="BAD"
> > ONBADROW="SKIP">
> >
> > <CFOUTPUT>
> > <P>Rows Read: #CSV_RowsProcessed#<BR>
> > Bad Rows: #CSV_BadRows#<BR>
> > ReachedEOF: #CSV_ReachedEOF#<BR>
> > StartRow: #CSV_StartRow#<BR>
> > </CFOUTPUT>
> >
> > <cfdump var="# MyQuery #">
> >
> > And this results in a page that shows the following:
> >
> > Rows Read: 0
> > Bad Rows:
> > ReachedEOF: YES
> > StartRow: 1
> >
> >
> >
> >
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
