So your code that looks for the second value in a comma delimited list would never get found as it is in fact delimited by semicolons.
Try a ListLen with commas and if it is only 1 try a ListLen with semicolon (;) and if you get 12 then change the delimiters.
Hope this may help.
Allan Cliff
----- Original Message -----
From: Pascal Peters
To: CF-Talk
Sent: Friday, February 13, 2004 8:32 AM
Subject: [cftalk] RE: Importing CSV into Database - Update
It looks like you have an incorrect (maybe empty) line you try to
import. Check for ListLen() of your line and if it isn't 21 you have a
problem. Maybe you can ignore the import then and write an error
message.
As you use chr(13) as a dilimiter, I assume you are on windows. Try
using chr(13)chr(10) as list delimiter, it may solve your problem.
As a side note, <cfhttp> can read a csv file and convert it into a
query. It could make your life easy. There are also some custom tags
that do it if I remember correctly.
Pascal
> -----Original Message-----
> From: Jillian Carroll [mailto:[EMAIL PROTECTED]
> Sent: donderdag 12 februari 2004 19:04
> To: CF-Talk
> Subject: RE: Importing CSV into Database - Update
>
> I've played with this a little more. The error I am now
> getting (after removing the cftry so I could see) is this:
>
> *** *** ***
>
> Error Diagnostic Information
> An error occurred while evaluating the _expression_:
> #ListGetAt(l, 2)#
>
> Error near line 56, column 8.
>
> In function ListGetAt(list, index [, delimiters]) the value
> of index, which is 2, is not a valid index for the list given
> as a the first argument (this list has 1 elements). Valid
> indexes are in the range 1 through the number of elements in
> the list The error occurred while processing an element with
> a general identifier of (#ListGetAt(l, 2)#), occupying
> document position (56:7) to (56:23).
>
> *** *** ***
>
> Where should I go from here? Should I be taking a different approach?
>
> --
> Jillian
>
> -----Original Message-----
> From: Jillian Carroll [mailto:[EMAIL PROTECTED]
> Sent: February 12, 2004 10:57 AM
> To: CF-Talk
> Subject: Importing CSV into Database
>
>
> Good morning,
>
> I am having a heck of a time with this little import script.
> When it runs,
> all I receive are the messages that 'An error occurred while
> updating the
> database.' (as I indicated it should do if the query fails)
>
> Can anybody point me in the right direction?
>
> Thanks!!
>
> --
> Jillian
>
> *** *** ***
>
> <!--- Identify the CSV source file --->
> <cffile action="" file="/data/intr/pages/tools/data.csv"
> variable="csv_import">
>
> <!---
> Identify the line delimiter
> chr(13) = carriage return
> A complete list can be found at http://www.asciitable.com
> --->
> <cfset dm = chr(13)>
>
> <cfoutput>
> <!--- Loop through each line in the CSV source file, using the
> delimiter specified. --->
> <cfloop index="k" list="#csv_import#" delimiters="#dm#">
> <!--- Loop through all records on each line, using the
> delimiter specified. --->
> <cfloop index="l" list="#k#" delimiters=",">
> <!---
> Wrap action in cftry tag to catch any
> problems, and display an
> error message in case the database update is
> unable to be completed.
> --->
> <cftry>
> <!--- Perform SQL insert --->
> <cfquery name="insert" datasource="#DSN#">
> INSERT INTO person_temp (
> person_id,
> salutation_id,
> first_name,
> last_name,
> title,
> qualification,
> organization_id,
> department_id,
> office_id,
> status,
> role_id,
> org_position,
> national_board,
> userid,
> password,
>
> work_phone,
> work_phone_ext,
> fax_phone,
> cell_phone,
> pager_phone,
> email_wireless,
> lung_family,
> )
> VALUES (
>
> nextval('"person_person_id_seq"'::text),
> #ListGetAt(l, 1)#,
> '#ListGetAt(l, 2)#',
> '#ListGetAt(l, 3)#',
> #ListGetAt(l, 4)#,
> '#ListGetAt(l, 5)#',
> #ListGetAt(l, 6)#,
> #ListGetAt(l, 7)#,
> #ListGetAt(l, 8)#,
> '#ListGetAt(l, 9)#',
> #ListGetAt(l, 10)#,
> #ListGetAt(l, 11)#,
> #ListGetAt(l, 12)#,
>
> '#ListGetAt(l, 13)#',
> '#ListGetAt(l, 14)#',
>
> '#ListGetAt(l, 15)#',
> '#ListGetAt(l, 16)#',
> '#ListGetAt(l, 17)#',
> '#ListGetAt(l, 18)#',
> '#ListGetAt(l, 19)#',
>
> '#ListGetAt(l, 20)#',
> '1',
> '#ListGetAt(l, 21)#',
> )
> </cfquery>
>
> <!--- If the update succeeds, display
> confirmation message. --->
> <br /><br />
> Database update completed.
>
> <!--- If the update failes, display error
> message. --->
> <cfcatch type="any">
> <br /><br />
> An error occured while updating the
> database.
> </cfcatch>
> </cftry>
> </cfloop>
> </cfloop>
> </cfoutput>
>
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
