Tab delimited input into database

Good Day

I am working on a project that requires inserting content from tab delimited files into a access database, the files are contained in a zip file that is sent from a different server every night with a the name of the zip file being the date it was sent on (02-07-05.zip), the files in the zip file are tab delimited files, but they are called;
- 02-07-05.M
- 02-07-05.O
- 02-07-05.P

Each file contants the original column information that of course will not have to be entered.

I have created 3 tables in my database to store this information called members, offices, and people. The content in each file are not consistent, for example one of the column are called age, the value rage from 16, 24, 30 and OLD (it is in text form) also some column values are left blank.

Each time I need to insert the content into the database (every night) the old content has to be completed removed and then re-entered, because items are removed and new items are entered frequently.

Any Suggestions


Review of what needs to be done:
- Zipped file has to be unzipped
- open each file
- wipe old data and database
- enter content from files (allow for empty data)
- remove unzipped files from server
- done every night around midnight
(Run the script as a schedule task)
- try to keep the load on server low


I have tried many different processes for inserting the data from the file but my mane problem that I have come across is the empty values.

Any help you can give will be greatly appreciated.

All you need to do is loop over the tab-delimited data, using the tab character as the delimiter for a list loop. Then each iteration of the loop is one tab-delimited line of the file. Insert each one into the database.

How can I go about doing that, without putting too much load on the server, there will be about 200 results that will have to be added every night, would not create a new connection to the database every time I run the CFQUERY function?

No, ColdFusion maintains database connections unless you have explicitly unchecked that box when you created the datasource. And no, running 200 insert statements will not do anything to your server. If it was 20,000 inserts then you'd have to worry.

ok thats good then, one last question concerning this item, some of the colums contain empty values and useing this coding setup:



the last value returns an error saying that the list only has 11 items, I am sure that I have done this before, but how could I do a simple check to see if each list item has a value?

Thank you

you could get the ListLen to get the number of elements in the list. if its less than 12 then do your error checking stuff.

But if the list has only 11 entries, you will probably need to check every one of the list positions to determine which one is missing, otherwise you'll always be assuming that it's the last one in the list when it may in fact not be.

This should be a simple question but how would I be able to check all of them to ensure each colum had a value, kiteless.

ColdFusion ignores empty lis elements. So if your list contains of 3 elements and one of them is empty (i.e. element1,,element2) then ColdFusion would threat that as an 2-elemental list.
Maybe replacing the empty elements in the list with "", or whatever empty value would be satisfying for your need, could help you out.

There are also some custom list functions at cflib.org that deal with lists without merging empty list elements together as the built-in CFML list functions do.

I found what I needed to do thank you.