Webmaster Forum / ColdFusion / Advanced Techniques / March 2006

Hello,

I have made a script that uploads a csv file into a database table. De data is
directly inserted in a temporary table. The second step is then after uploading
it in the temporary table to insert the data into another table. However I have
a date field in the other table and the date which were in the csv file and
thus now in the temporary table is like this: Wed Nov 09 12:17:15 CET 2005 What
I really want is to insert it like this: 2005-11-09

I made a script which makes a sql query (temporary table) and lists the date
field(zen.z_scandatum) and then changes this value of Wed Nov 09 12:17:15 CET
2005 into 2005-11-09:

It works like this:

<cfif zen.recordcount GT 0>
    <cfset temp = ValueList(zen.z_scandatum)>
    <CFLOOP INDEX="Teller" FROM=1 TO=#ListLen(temp)#>
        <cfset myString  = ListGetAt(temp,Teller)>
<cfswitch expression="#Len(Trim(myString))#">
            <cfcase value="28"><!--- vb: BER-4999--->
                <cfset maand = Mid(myString,5,3)>
                <cfif IsDefined("maand") AND #maand# EQ "Jan"><cfset maand = 01>
                <cfelseif IsDefined("maand") AND #maand# EQ "Feb"><cfset maand = 02>
                <cfelseif IsDefined("maand") AND #maand# EQ "Mar"><cfset maand = 03>
                <cfelseif IsDefined("maand") AND #maand# EQ "Apr"><cfset maand = 04>
                <cfelseif IsDefined("maand") AND #maand# EQ "May"><cfset maand = 05>
                <cfelseif IsDefined("maand") AND #maand# EQ "Jun"><cfset maand = 06>
                <cfelseif IsDefined("maand") AND #maand# EQ "Jul"><cfset maand = 07>
                <cfelseif IsDefined("maand") AND #maand# EQ "Aug"><cfset maand = 08>
                <cfelseif IsDefined("maand") AND #maand# EQ "Sep"><cfset maand = 09>
                <cfelseif IsDefined("maand") AND #maand# EQ "Oct"><cfset maand = 10>
                <cfelseif IsDefined("maand") AND #maand# EQ "Nov"><cfset maand = 11>
                <cfelseif IsDefined("maand") AND #maand# EQ "Dec"><cfset maand = 12>
                </cfif>
                <cfset dag = MID(myString,9,2)>
                <cfset jaar = LSParseNumber(Right(myString, 4)) >
               
            </cfcase>
<cfcase value="23">
                <cfset maand = Mid(myString,4,3)>
                <cfif IsDefined("maand") AND #maand# EQ "Jan"><cfset maand = 01>
                <cfelseif IsDefined("maand") AND #maand# EQ "Feb"><cfset maand = 02>
                <cfelseif IsDefined("maand") AND #maand# EQ "Mar"><cfset maand = 03>
                <cfelseif IsDefined("maand") AND #maand# EQ "Apr"><cfset maand = 04>
                <cfelseif IsDefined("maand") AND #maand# EQ "May"><cfset maand = 05>
                <cfelseif IsDefined("maand") AND #maand# EQ "Jun"><cfset maand = 06>
                <cfelseif IsDefined("maand") AND #maand# EQ "Jul"><cfset maand = 07>
                <cfelseif IsDefined("maand") AND #maand# EQ "Aug"><cfset maand = 08>
                <cfelseif IsDefined("maand") AND #maand# EQ "Sep"><cfset maand = 09>
                <cfelseif IsDefined("maand") AND #maand# EQ "Oct"><cfset maand = 10>
                <cfelseif IsDefined("maand") AND #maand# EQ "Nov"><cfset maand = 11>
                <cfelseif IsDefined("maand") AND #maand# EQ "Dec"><cfset maand = 12>
                </cfif>
                <cfset dag = MID(myString,7,2)>
                <cfset jaar = LSParseNumber(Right(myString, 4)) >
               
               
            </cfcase>  
            <cfdefaultcase>
               <cfset maand = 00 >
                <cfset dag = 00>
                <cfset jaar = 0000>
                <cfset datum = #jaar#-#maand#-#dag#>
            </cfdefaultcase>
        </cfswitch>
         <CFQUERY NAME="update" DATASOURCE="rug">  
INSERT INTO dev (dev_serie, scandate)
SELECT '#zen.z_serienr#','#jaar#-#maand#-#dag#'
</CFQUERY>
</cfloop>
</cfif>

As you see it works fine for the scandate value to be inserted in the right
way into the database table dev. However I also want to insert the
zen.z_serienr into the dev_serie field of the dev table. Actually insert the
dev_serie that belongs to the good scandate in the same row!

Now It just inserts the first value of zen.z_serienr for each scandate insert.
This is because zen.z_serienr is not in a/the list or something. But is there a
possibility to get this in my table in a good way?

A solution would be (I guess) to do it in two steps:

First make a list of the zen.z_serienr and insert each value into the new
table (by using loop).
And then a update of this table  and try to insert the scandate in a
2006-12-01 way in the right row...

But how can I do this? I really don't hav an Idea.

Is there somebody who could help me with this?

Thank you,

Kabbi

(The reason I want the date in the table like this; 2006-09-12 is that later
on the dates has to be compared to the dates in another table)