is a decimal separator, semi-colon would be used as a field delimiter
instead (in that case, you might have commas in the fields, if there are
figures with decimals).
/Hugo
-------------------------------------------------------------
Hugo Ahlenius E-Mail: [EMAIL PROTECTED]
Project Officer Phone: +46 8 230460
UNEP GRID-Arendal Fax: +46 8 230441
Stockholm Office Mobile: +46 733 467111
WWW: http://www.grida.no
-------------------------------------------------------------
| -----Original Message-----
| From: CFUG Spain [mailto:[EMAIL PROTECTED]
| Sent: Friday, February 13, 2004 08:46
| To: CF-Talk
| Subject: Re: [cftalk] RE: Importing CSV into Database - Update
|
| A problem I had with importing CSV at least CSV created by
| excel is that the files it creates are not always comma
| delimited, they are sometimes semicolon delimited.
|
| 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]
