• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 765
  • Last Modified:

Reading and comparing CSV and database table data

hi

I am trying to compare the data in a datebase table with the data in a CSV file. Only the first column is being used/compared in the CSV file to/with a column in the DB table. Whatever is listed in the CSV file column and is not equal to that which is in the database table must be displayed to the browser. Here is some code:

<cfset DepartmentCodes = QueryNew('DepartmentCode')>
<cfset strFilePath = (CFFILE.ServerDirectory & "\" & CFFILE.ServerFile) />

<!--- get and read the CSV-TXT file --->
<cffile action="read" file="#strFilePath#" variable="csvfile">

<!---loop through the CSV-TXT file on line breaks and insert into database --->
<cfloop index="index" list="#csvfile#" delimiters="#chr(10)##chr(13)#">
<cfset CSVcolumn = '#listgetAt('#index#',1)#'>

<cfquery name="GetCodes" datasource="#serverDNS#">
SELECT DepartmentID
FROM Departments
WHERE Deleted = 0
AND Code <> '#CSVcolumn#'
</cfquery>
<cfif GetCodes.Recordcount GT 0>
<cfset NewRow = queryAddRow(DepartmentCodes)>
<cfset tempCodes = querySetCell(DepartmentCodes, 'DepartmentCode', #CSVcolumn#)>
</cfif>
</cfloop>
<cfoutput query="DepartmentCodes">
#DepartmentCode#<br />
</cfoutput>

Problem:
This is returning all the Data in the CSV column, all i need is the data not in the database table which are listed in the CSV file.

Anyone?

Thank you.
Garth.
Direct email gclaasen@tio.co.za
0
TIO-Solutions
Asked:
TIO-Solutions
  • 4
  • 3
2 Solutions
 
hieloCommented:
Hello TIO-Solutions,
Try changing this:
<cfset CSVcolumn = '#listgetAt('#index#',1)#'>

to this:
<cfset CSVcolumn = '#listgetAt('#index#',1,',')#'>


Regards,

hielo
0
 
TIO-SolutionsAuthor Commented:
Thanks for the reply Hielo.
This is what i get back from the query;
BDCTBAwweett

It should look like this;
ww
ee
tt

Reason is that BD, CT, BA are already in the table and do not need to be displayed. the other three are in the code column of the csv file, but not in the table. the last three need to be displayed.
0
 
_agx_Commented:
I think your query logic is wrong.  I think you should be matching _on_ the code and adding the value to the query if the record count _does_ equal 0.

     ...
       WHERE Deleted = 0
      AND Code = '#CSVcolumn#'
   </cfquery>
   <cfif GetCodes.Recordcount EQ 0>
       <cfset NewRow = queryAddRow(DepartmentCodes)>
        ...
    </.cfif>


As an aside, maybe you have a reason for doing the comparison in CF, but it seems like it would be easier if you imported the CSV data into a table, then used a single LEFT JOIN to get the differences

-- find codes that exist in table 1 that do not exist in table 2
SELECT  t1.Code
FROM    TableOne AS t1 LEFT JOIN TableTwo AS t2
              ON t1.Code = t2.Code AND t2.Deleted = 0
WHERE   t2.Code IS NULL
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
TIO-SolutionsAuthor Commented:
Everything is working properly now.
Thanks to Ben Nadel, the following code did the job.
thanks for your efforts.
<cfset DepartmentCodes = QueryNew('DepartmentCode')>
		
		<cfset strFilePath = (CFFILE.ServerDirectory & "\" & CFFILE.ServerFile) />
		
		<!--- get and read the CSV-TXT file --->
		<cffile action="read" file="#strFilePath#" variable="csvfile">
		
		<cfquery name="GetCodes" datasource="tio">
				SELECT TourplanDepartmentCode
				FROM Departments
				WHERE Deleted = 0
		</cfquery>
		
		<!---loop through the CSV-TXT file on line breaks and insert into database --->
		<cfloop index="index" list="#csvfile#" delimiters="#chr(10)##chr(13)#">
		
				<!--- Get the current department. --->
				<cfset strDepartment = ListGetAt( index, 1 ) />
				
				<!--- Check to see if it exists in the query. --->
				<cfquery name="qDepartment" dbtype="query">
						SELECT
								*
						FROM
								GetCodes
						WHERE
								TourplanDepartmentCode = <cfqueryparam value="#strDepartment#" cfsqltype="cf_sql_varchar" />
				</cfquery>
				
				
				<!--- Check to see if department was found. --->
				<cfif NOT qDepartment.RecordCount>
								<cfset NewRow = queryAddRow(DepartmentCodes)>
								<cfset tempCodes = querySetCell(DepartmentCodes, 'DepartmentCode', strDepartment)>
				</cfif>
		
		</cfloop>
		
		<cfoutput query="DepartmentCodes">
		
								#DepartmentCode#<br />
		
		</cfoutput>

Open in new window

0
 
TIO-SolutionsAuthor Commented:
Thanks you guys.
0
 
_agx_Commented:
TIO-Solutions,

Really? That looks like exactly what I suggested.
0
 
TIO-SolutionsAuthor Commented:
Yeah. The problem was that i did yours and it still returned all the codes.

What you forgot or maybe did not notice is that the query:
<cfquery name="GetCodes" datasource="tio">
                        SELECT TourplanDepartmentCode
                        FROM Departments
                        WHERE Deleted = 0
            </cfquery>
should have been outside the loop. With the query outside the loop it gave me all the codes not listed/found.
0
 
_agx_Commented:
No, it works perfectly fine even inside the loop.  I suspect you may have implemented the code wrong.  Perhaps you missed it but there were two code changes.

1)  to change the <> to equals
 ...
       WHERE Deleted = 0
      AND Code = '#CSVcolumn#'
   </cfquery>

2)  Add the row if the the recordcount _equals_ zero

   <cfif GetCodes.Recordcount EQ 0>
       <cfset NewRow = queryAddRow(DepartmentCodes)>
        ...
    </.cfif>

So if the table contains the codes: BD, CT, BA
And the file contains the codes:  ww, ee, tt

The output of the query is:  ww, ee, tt    (not everything).  

0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now