ColdFusion MX 7 - Types of Datasources

[lostatsea]

I need a bit of help with ColdFusion MX 7 and types of datasources:

I am used to connecting to databases and creating recordsets, but now I have this situation and not sure how it all works. So far I have:

Added .txt files to my local server directory from an external drive
Used cfdirectory = list to get all the files to display on my .cfm page

I would like to add the list to a database instead of displaying on a page just yet, but can't seem to figure out how to do this since I have no datasource - only using the local server directory right now to get the files list.

Is there a way I can use the directory as a datasource in CFAdministrator?

I need to open each .txt file separately and save some of the contents to Oracle 9i as their own record (e.g., PCNAME, ProductName, ServerName)

Also, how do I create a ODBC connection string in CFMX7?

Thanks to all in advance.

Lost@Sea

[usachrisk]

Hope I can help answer some of these.

A datasource is the name for a connection to a database. It doesn't need to be on your local machine, you can use the CF Administrator to connect to any datasource anywhere (so long as it's accessable, and you're authorized).

You can use CFFILE to read a Text file, parse it how however you like, and then insert it into a database using SQL.

Does this make sense?

[lostatsea]

Yes, thank you very much.

I have been able to open the .txt file from the external directory on our network, but the contents are returning jumbled and I can't seem to find out how to get only the fields I want out of the mess. (e.g., SMBIOS Signature:, DMI Signature:, etc. + the data after it)

Example: This is how the text is laid out in the original .txt file.

SMBIOSD -- Version 3.04
(C) Copyright 2000-2002 xxxxxx, Inc. All rights reserved.
======================== SMBIOS Header ========================================
SMBIOS header is present at segment F043h.
SMBIOS Signature: _SM_.
SMBIOS Checksum: xx.
SMBIOS Struc Table Length: xx.
SMBIOS Revision #: xx.
SMBIOS Maximum Struc Size: xxx.
SMBIOS Entry Point Revision: 0.
SMBIOS Reserved: xxxx.
DMI Signature: xxx.
DMI Checksum: xxxx.
DMI Struc Table Length: xxxx.
DMI Struc Table Address: xxxxxx.
DMI # Strucs: xx.
DMI Revision #: x.

ÍÍ> Type 1: System Information
Struc length is 25 bytes.
Struc handle is xxxxxx.
Manufacturer: Dell Computer Corporation
Product Name: OptiPlex xxxxxx
Version: (none)
Serial Number: xxxxxxx
Universal Unique ID #: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Wake-Up Type: Power Switch

After I use CFFILE to read it I get:

SMBIOSD -- Version 3.04 (C) Copyright 2000-2002 xxxxxx, Inc. All rights reserved.======================== SMBIOS Header ========================================SMBIOS header is present at segment F043h.SMBIOS Signature: _SM_.SMBIOS Checksum: xx.SMBIOS Struc Table Length: xx.SMBIOS Revision #: xx.SMBIOS Maximum Struc Size: xxx.SMBIOS Entry Point Revision: 0.SMBIOS Reserved: xxxx.DMI Signature: xxx.DMI Checksum: xxxx.DMI Struc Table Length: xxxx.DMI Struc Table Address: xxxxxx.DMI # Strucs: xx.DMI Revision #: x.ÍÍ> Type 1: System InformationStruc length is 25 bytes.Struc handle is xxxxxx.Manufacturer: Dell Computer CorporationProduct Name: OptiPlex xxxxxx Version: (none)Serial Number: xxxxxxxUniversal Unique ID #: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxWake-Up Type: Power Switch

I thought i could just simply set up the : delimiter to pull certain fields out of this mess, but I have not found a way to do this and then put the info into a database.

The other trouble is that fields like Serial Number: has a space in the name, but in my table it is SerialNumber.

I am totally confused as to how to pull out just the data I want from the jumbled mess and only have certain info sent to the db.

Any help you can give me would be appreciated.

Hope I can help answer some of these.

A datasource is the name for a connection to a database. It doesn't need to be on your local machine, you can use the CF Administrator to connect to any datasource anywhere (so long as it's accessable, and you're authorized).

You can use CFFILE to read a Text file, parse it how however you like, and then insert it into a database using SQL.

Does this make sense?

[usachrisk]

It's a bit of work, but here's a good hint. First, CFLOOP over the file using carriage returns (chr(10) or chr(13)) as a delimiter. You'll now be able to look at the file line by line. Then, treat the individual line as a list, using : as a delimiter. Now your left side is the "column", and the right side is the "value". You may have to trim the values and remove any carriage return codes from them, but once you get this far, you should be well on your way.

I'm not sure how well you work with ColdFusion, so please let me know if you need clarificiation on anything above.

[lostatsea]

It's a bit of work, but here's a good hint. First, CFLOOP over the file using carriage returns (chr(10) or chr(13)) as a delimiter. You'll now be able to look at the file line by line. Then, treat the individual line as a list, using : as a delimiter. Now your left side is the "column", and the right side is the "value". You may have to trim the values and remove any carriage return codes from them, but once you get this far, you should be well on your way.

I'm not sure how well you work with ColdFusion, so please let me know if you need clarificiation on anything above.

Well, this is what I have so far. I am able to read the data, but where and how to get the carriage return and delimiter is beyond me since the myvariable pulls out the entire content using cffile=read (the jumbled stuff I showed you earlier):

<cfloop query = "myfileslist">
<cffile
action="read"
file="C:\CFusionMX7\wwwroot\LTrakker\PCData\#myfiles.Name#"
variable="myvariable">
<cfoutput>
<table>
<tr>
<td>
#Name#
</td>
</tr>
<tr>
<td>
#myvariable#
</td>
</tr>
</table>
</cfoutput>

</cfloop>

Thanks again for your help. I know this has to be simple. I think I have burned my brain cells with this (I love learning new things in CF though). :-)

[lostatsea]

Ok, here is some new info:

I am now able to break up the text but I can't seem to get it to read all the files in the directory. It reads only one of the two files in my directory:

<cfdirectory
action="List"
directory="C:\CFusionMX7\wwwroot\LocationInfo\PCStuff\"
name="myfileslist"
filter="*.txt"
sort="Name ASC">

<!--- CF reads the file --->
<cfloop query="myfileslist">
<CFFILE ACTION="Read"
file="C:\CFusionMX7\wwwroot\LocationInfo\PCStuff\#myfileslist.Name#"
VARIABLE="file_content">
</cfloop>

<!--- each line becomes an element of the same array--->
<CFSET FILE_ARRAY = ARRAYNEW(1)>
<CFLOOP LIST="#file_content#" INDEX="rc" DELIMITERS="#Chr(10)#">
<CFSET RC = ARRAYAPPEND(FILE_ARRAY, rc)>
</CFLOOP>

<CFLOOP INDEX="loopcount" FROM="1" TO="88">
<!---<cfoutput>#FILE_ARRAY[loopcount]#<BR />
</cfoutput>--->


<cfoutput>
#myfileslist.Name#<br />
#FILE_ARRAY[23]#<br />
#FILE_array[25]#<br />
#FILE_array[37]#<br />
</cfoutput>
</cfloop>

I am missing something, but don't know what it is. I have never used arrays before and wish I knew of a substitute at this point so I can just read the text files and the specific contents from the text file and just save the record to Oracle.

Any help would be appreciated.

Thanks in advance.

It's a bit of work, but here's a good hint. First, CFLOOP over the file using carriage returns (chr(10) or chr(13)) as a delimiter. You'll now be able to look at the file line by line. Then, treat the individual line as a list, using : as a delimiter. Now your left side is the "column", and the right side is the "value". You may have to trim the values and remove any carriage return codes from them, but once you get this far, you should be well on your way.

I'm not sure how well you work with ColdFusion, so please let me know if you need clarificiation on anything above.

[lostatsea]

ok

I finally got this to work. Here is my solution. I found a resource and edited it to work for multiple files. It works beautifully. Now I have to parse it again to enter all data after the : to place in Oracle.

Thanks to everyone for your help. :-)
__________________________________________________________

<!---Read all text files from the directory/folder PCStuff.--->

<h3>Example - Reading text files from a server directory.</h3>

<CFDIRECTORY
action="List"
directory="C:\CFusionMX7\wwwroot\LocTrakker\PCStuff\"
name="myfileslist"
filter="*.txt"
sort="Name ASC">

<!--- NESTED LOOPS---Read all files in the directory and save to a variable. --->
<CFLOOP query = "myfileslist">
<CFFILE action="Read"
file="c:\CFusionMX7\wwwroot\LocTrakker\PCStuff\#myfileslist.Name#"
variable="file_content">

<!--- Each line becomes an element of the same array. All file content is displayed with a carriage return. --->

<CFSET file_array = ArrayNew(1)>
<CFLOOP list="#file_content#" index="rc" delimiters="#Chr(10)#">
<CFSET rc = ArrayAppend(file_array, rc)>
</CFLOOP>

<!---Then choose which line(s) from the text file you would like to display.
"Name" is the name used by Windows to pull the files from the folder. Items in [] is the line number.--->
<CFOUTPUT>
<Table>
<tr>
<td>#myfileslist.Name#</td>
</tr>
<tr>
<td>#file_array[23]#</td>
</tr>
<tr>
<td>#file_array[24]#</td>
</tr>
<tr>
<td>#file_array[25]#</td>
</tr>
<tr><td>&nbsp;</td></tr>
</Table>
</CFOUTPUT>
</CFLOOP>

LAS