Copy link to clipboard
Copied
The error:
Error Occurred While Processing Request | ||||||||
|
The code:
<cffile action="read" file="http://website.com/test1.txt" variable="data" attributes="readonly" >
<cfoutput >
#data#<br>
</cfoutput>
<cfloop index="i" list="#data#" delimiters= "#chr(10)##chr(13)#" >
<cfquery datasource="X" username="Y" password="Z" timeout="90">
INSERT INTO P2
( MLSID,
BathFull,
BathHalf,
Beds,
BuildName,
City,
LandTenure,
ListPrice,
MatrixUID, MLSArea,
Neighbourhood,
Parking,
AddressOK,
PhotoCount,
PropertyType,
Public,
Status,
StreetName,
StreetNumber,
StreetSuf,
Maint,
Postal,
SQFT)
VALUES
(
'#replace(listGetAt(i,1,chr(9)),'"','','all')#',
'#replace(listGetAt(i,2,chr(9)),'"','','all')#',
'#replace(listGetAt(i,3,chr(9)),'"','','all')#',
'#replace(listGetAt(i,4,chr(9)),'"','','all')#',
'#replace(listGetAt(i,5,chr(9)),'"','','all')#',
'#replace(listGetAt(i,6,chr(9)),'"','','all')#',
'#replace(listGetAt(i,7,chr(9)),'"','','all')#',
'#replace(listGetAt(i,8,chr(9)),'"','','all')#',
'#replace(listGetAt(i,9,chr(9)),'"','','all')#',
'#replace(listGetAt(i,10,chr(9)),'"','','all')#',
'#replace(listGetAt(i,11,chr(9)),'"','','all')#',
'#replace(listGetAt(i,12,chr(9)),'"','','all')#',
'#replace(listGetAt(i,13,chr(9)),'"','','all')#',
'#replace(listGetAt(i,14,chr(9)),'"','','all')#',
'#replace(listGetAt(i,15,chr(9)),'"','','all')#',
'#replace(listGetAt(i,16,chr(9)),'"','','all')#',
'#replace(listGetAt(i,17,chr(9)),'"','','all')#',
'#replace(listGetAt(i,18,chr(9)),'"','','all')#',
'#replace(listGetAt(i,19,chr(9)),'"','','all')#',
'#replace(listGetAt(i,20,chr(9)),'"','','all')#',
'#replace(listGetAt(i,21,chr(9)),'"','','all')#',
'#replace(listGetAt(i,22,chr(9)),'"','','all')#',
'#replace(listGetAt(i,23,chr(9)),'"','','all')#'
)
</cfquery>
</cfloop>
This is a single row example of the information importing. I included headers to show the spacing.
MLS Number | Baths Full | Baths Half | Beds Total | Building Name | City | Land Tenure | List Price | Matrix Unique ID | MLS Area Major | Neighbourhood | Parking Total | Permit Address Internet YN | Photo Count | Property Type | Public Remarks | Status | Street Name | Street Number | Street Suffix | Maintenance Expense | Postal Code | Sqft Total |
2806782 | 1 | 0 | 3 | 2275.00 | 1337510 | METRO | KALIHI VALLEY | 2 | 0 | 0 | RNT | SERENE AND COOL KALIHI VALLEY. LOCATED ON A PRIVATE ROAD. DOWNSTAIR AVAILABLE 6/1/2008. RENOVATED. FURNISHED WITH REFRIGERATOR, MICROWAVE, WASHER & DRYER. WATER, BASIC CABLE INCLUDED. TENANT HAVE SEPARATE ELECTRIC METER. START SHOWING BY 1ST WEEK OF MAY. BY APPT ONLY. | A | 1088 |
My Question:
I have no control over the data entry. Anyone of these can be blank. I need to enter the information and preserve the integrity of the information. How do I include the empty spaces, not knowing which will be blank, and have the information insert corrwectly into my database?
Thank you in advance.
You could give this a try:
<cfset dataModified = "">
<cfloop index="line" list="#data#" delimiters= "#chr(10)##chr(13)#">
<!--- Take each row in turn and convert it into a comma-delimited list. --->
<cfset line=replaceNocase(line,chr(9),",","all")>
<!---Replace with 'NULL' each blank item in the list--->
<cfset rowWithNulls = "">
<cfloop index="idx" from="1" to="23">
<cfset rowItem = listgetat(line,idx,',','yes')>
<cfif trim(rowItem) is "">
...
Copy link to clipboard
Copied
The text file is space-delimited? Any way you can get it comma-delimited or delimited by some other character? If not, I'm not sure what you can do to deal with "blank" values.
Copy link to clipboard
Copied
The file is tab delimited. some of the fields might have more that one element in it and they would be separated by commas. An example could be "ASAP,DELTA,SEP". I can do a CSV file however the example just shown concerns me.
Copy link to clipboard
Copied
You could give this a try:
<cfset dataModified = "">
<cfloop index="line" list="#data#" delimiters= "#chr(10)##chr(13)#">
<!--- Take each row in turn and convert it into a comma-delimited list. --->
<cfset line=replaceNocase(line,chr(9),",","all")>
<!---Replace with 'NULL' each blank item in the list--->
<cfset rowWithNulls = "">
<cfloop index="idx" from="1" to="23">
<cfset rowItem = listgetat(line,idx,',','yes')>
<cfif trim(rowItem) is "">
<cfset rowWithNulls = listAppend(rowWithNulls,'NULL')>
<cfelse>
<cfset rowWithNulls = listAppend(rowWithNulls,rowItem)>
</cfif>
</cfloop>
<!---Build up a new version of the original data, with comma replacing chr(9) and NULL replacing blank entries--->
<cfset dataModified = listAppend(dataModified,rowWithNulls,"#chr(10)##chr(13)#")>
</cfloop>
<cfloop index="i" list="#dataModified#" delimiters= "#chr(10)##chr(13)#" >
<cfquery datasource="X" username="Y" password="Z" timeout="90">
INSERT INTO P2
( MLSID,
BathFull,
BathHalf,
Beds,
BuildName,
City,
LandTenure,
ListPrice,
MatrixUID, MLSArea,
Neighbourhood,
Parking,
AddressOK,
PhotoCount,
PropertyType,
Public,
Status,
StreetName,
StreetNumber,
StreetSuf,
Maint,
Postal,
SQFT)
VALUES
(
'#replace(listGetAt(i,1),'"','','all')#',
'#replace(listGetAt(i,2),'"','','all')#',
...
...
...
'#replace(listGetAt(i,23),'"','','all')#'
)
</cfquery>
</cfloop>
Copy link to clipboard
Copied
Thank you.
I hope your answers help other people as much as they helped me.