Uploading a CSV file into a Access Database? 1

[Neotrix]

All. I have no idea how to approach this, but I am assumming that I need to use CFCONTENT somewhere.

THE SITUATION:
I have created a CSV file upload page, with no problems, it works well!

Now what I need to do, is read the contents of the CSV file (itemdescription, value), and compare it with existing records in the database.

Easy you might say? Here is where it gets tricky!

If a itemdescription (from the CSV file) matches one already in the database (exactly), then update the value (from the CSV to the database).
If the itemdescription (in the CSV file) doesnt match exactly to whats in the database, but has a few possible matches, then add it to an array we create with details of the closest possible match(s) (which can be sorted/matched up later) on a seperate page.

Anyone done anything similar?
Anyone got any solutions?

It would all be very much appreciated!

 

[tlhawkins]

Without spending nights and weekends on this (sounds like a great project) I would suggest a Query like this

<CFQUERY name=&quot;compare&quot; datasource=&quot;mine&quot;>
Select *
From DescriptionTable
Where Description like '%CSVDescription%'
</CFQUERY>

This should give you a Query list comparing your CSV description to the database description and allowing all entries that contain the CSV description. The problem I can see with this is that there are a million different ways that records could be similar, but keep in mind that the like aggregate allows wildcards. I would work on that.

Hope it helps.

 

[CFHub]

I wouldn't put this in my &quot;easy things to do before lunch basket&quot;.

You may be able to use CFHTTP to convert the CSV directly to a query first. Then QueryToArray()/ArraySort and a CFLOOP may finish it all off...

Depending on the &quot;validity&quot; of your CSV file, the CFHTTP thing can be a great time saver:

http://cfhub.com/advanced/cfhttp/query.cfm

 

[tleish]

CFHub, great trick. I didn't know you could query a comma deliminated file with CFHTTP. Thanks, I'm going to add this to my bag of tricks.
- tleish