Dae,
I have questions:
<cfloop index="index" list="#txtfile#" delimiters="#chr(10)##chr(13)#">
<cfquery name="importtxt" datasource="sqlconnect">
INSERT INTO testload (date_add,name,phone,zip)
VALUES
('#listgetAt('#index#',1, '|')#',
'#listgetAt('#index#',2, '|')#',
'#listgetAt('#index#',3, '|')#',
'#listgetAt('#index#',4)#'
)
</cfquery>
</cfloop>
First, are you sure that #chr(10)##chr(13)# is working for you? I've always
concatenated those two things together to get the carriage return linefeed.
#chr(10) & chr(13)#... Either way, the double set of pound signs isn't
necessary here one set will do.
Second, the last list get at is missing the pipe delimiter so that one will
default to a comma. Did you do that on purpose or is that a typo?
Also again, It isn't necessary to have all those pound signs in your
listgetat statements. The following will work:
<cfquery name="importtxt" datasource="sqlconnect">
INSERT INTO testload (date_add,name,phone,zip)
VALUES
('#listgetAt('index',1, '|')#',
'#listgetAt('index',2, '|')#',
'#listgetAt('index',3, '|')#',
'#listgetAt('index',4)#'
)
</cfquery>
Does any of that help? Let us know.
Cheers,
Chris
On 7/19/07, Dae <[EMAIL PROTECTED]> wrote:
>
> Someone help me... I need a way, other then using DTS to insert data into
> a
> SQL table. I was able to find some code on easyCFM.com, and it works, but
> only with comma delimited files. What I need to know is how I might be
> able
> to correct this so I can use files with other delimiters.
>
> Below is the text file and code. The way it is now, it works fine. When
> I
> change the delimiter from a comma ',' to say tilde '~' in the file and the
> code, I bombs!
>
> test.txt file:
>
> 20070510,Joe Smith, 312-555-1212,32121
> 20070510,Mary Smith, 312-555-1212,32121
> 20070509,John Doe, 413-555-4312,54331
> 20070508,Mike Cole, 541-555-9119,21112
> 20070504,Jane Short, 801-555-1332,73124
>
>
>
> =====================================
> Here's the code:
>
> <!--- get and read the TXT file --->
> <cffile action="read" file="test.txt" variable="txtfile">
>
> <!--- loop through the TXT file on line breaks and insert into database
> --->
> <cfloop index="index" list="#txtfile#" delimiters="#chr(10)##chr(13)#">
> <cfquery name="importtxt" datasource="sqlconnect">
> INSERT INTO testload (date_add,name,phone,zip)
> VALUES
> ('#listgetAt('#index#',1, '|')#',
> '#listgetAt('#index#',2, '|')#',
> '#listgetAt('#index#',3, '|')#',
> '#listgetAt('#index#',4)#'
> )
> </cfquery>
> </cfloop>
>
> <!--- use a simple database query to check the results of the import -
> dumping query to screen --->
> <cfquery name="rscsvdemo" datasource="sqlconnect">
> SELECT * FROM testload
> </cfquery>
> <cfdump var="#rscsvdemo#">
>
> ================================
>
> Someone help!! How do I fix it to work with other delimiters? I get the
> below error if I can the comma to say a pipeline '|'. I made sure to
> change
> the test.txt file to match.
>
> Invalid list index 4. In function ListGetAt(list, index [, delimiters]),
> the value of index, 4, is not a valid as 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 in *testload.cfm: line 15*
>
> 13 : '#listgetAt('#index#',2, '|')#',
> 14 : '#listgetAt('#index#',3, '|')#',
> *15 : '#listgetAt('#index#',4)#'*
> 16 : )
> 17 : </cfquery>
>
>
> ================================
>
> Thanks all!!
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 &
Flex 2
Free Trial
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284117
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
