a difficult one... get values for all data instead of one variable...

[gadjodilo77]

Hi,

With a little help I am able to insert a csv file into a database table. Now I have to insert some values of this table into another table. The problem is that I also have to insert some other data into the other table. For instance in the CSV table I have a column called PC_volgnr There are numbers in it like: BER-1001 BER-1999 BER-4020 etc.... each row a different value. The 1 and 4 represent a type of device and the 001, 999, and 020 represent the nr of device of that type. So I have to get the types and last value nr's from the values in the PC_volgnr column also. I manage to do this. But only for single variables. I want to be able to insert the PC_volgnr into another table but als want to know the typenr and the last valuenr of that device so I can put those values also in a field in the new table.

Currently I have it like this:

<cfquery name="list" datasource="">
SELECT *
FROM inventimport WHERE PC_volgnr NOT IN (SELECT dev_volgnr FROM dev) Order by inv_id
</cfquery>

<cfset temp = ValueList(list.PC_volgnr)>
<CFSET CurLen = ListLen(temp, ";")>

<cfscript>
myString="#list.PC_volgnr#";
//no. obtained after removing first 7 characters from the leftmost 8
deviceNumber=RemoveChars(Left(myString,8),1,7);
WriteOutput(deviceNumber);
</cfscript>

<cfset myString2 = "BER-1001">
<!--- extract last 3 characters--->
<cfset codenumber = Right(myString2, 3)>
<!--- convert the string into a number, discarding beginning zeros --->


<CFLOOP INDEX="Teller" FROM=1 TO=#ListLen(temp)#>
items: <cfoutput>#Teller#: #ListGetAt(temp, Teller)# <br></cfoutput>
</cfloop>

last nr: <cfoutput>#LSParseNumber(codenumber)#</cfoutput>

type: <cfoutput>#deviceNumber#</cfoutput>

This all works very good for a single variable. But what if I have to read out a database table field for a certain column all data per rows (BER-1001, BER-1002,...etc).

As you see I now get the type only for the first record from the database table. I want to have it for all. Could this also be done for the last three numbers of the items in column PC_volgnr? (Now I use myString2 = "BER-1001" but I want to put in the results of a database table query and then the value for each found item in each row)..... So I can insert the values from a query into another database table. And for each item insert the type nr into a field and the last number in another field (like 1 for BER-1001, and 25 for BER-1025 etc...[where the type is 1 in this example])]

Is there somebody who could help me on the way with this?

Gr, Kabbi

 

[gadjodilo77]

Someone explained me a little. Here is the solution, if you might have the same problem:

<cfif q.recordcount GT 0><!--- anders niks zien --->
<cfset temp = ValueList(q.PC_volgnr)>
<CFLOOP INDEX="Teller" FROM=1 TO=#ListLen(temp)#>
<cfset myString = ListGetAt(temp,Teller)>
<!--- of gebruik Mid() --->
<cfswitch expression="#Len(Trim(myString))#">
<cfcase value="8"><!--- vb: BER-4999--->
<cfset typenumber = Mid(myString,5,1)>
</cfcase>
<cfcase value="9"><!--- vb: B-06-4001--->
<cfset typenumber = Mid(myString,6,1)>
</cfcase>
<cfcase value="11"><!--- vb: BER-06-4001--->
<cfset typenumber = Mid(myString,8,1)>
</cfcase>
<cfdefaultcase>
<cfset typenumber = "geen type">
</cfdefaultcase>
</cfswitch>
<cfset codenumber = LSParseNumber(Right(myString, 3))>
<cfoutput>#myString# / #typenumber# / #codenumber#</cfoutput><br>
</CFLOOP>
</cfif>

Grt, kabbi

 

[TruthInSatire]

if you have a csv you should be able to use cfhttp to get the text file in a query format.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.