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
TIO-SolutionsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.