>- see footer for list info -< Sadly not. Have been discussing it with Snake at the moment, looking at converting the Tab File into XML, passing to a SP and looping through it that way.
Lee -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Simon Baynes Sent: 09 September 2006 18:43 To: Coldfusion Development Subject: Re: [CF-Dev] Import of 10k records from a tab delimited txt >- see footer for list info -< Can you not move the file to the DB server? If you can you can use a DTS package to import the file into a temporary table, then you can run a stored proceedure to do all the stuff you are doing in CF? This is preferrable as SQL 2000 is built for operations like this CF is not. On 9/9/06, Snake <[EMAIL PROTECTED]> wrote: > >- see footer for list info -< > Is there any rule to new/updated records. > > I.E. > > Do they upload a new file (with a different filename) when there are > new records, or do they just re-upload the same file each time, and > you have to see if it contains updates, new records or both? > If new records are always in a new file, then you can keep track of > previous filenames so this will tell you whether you need to do an > INSERT or an UPDATE. > > You will have some advantage with using an SP for the update/add > record queries. But Either way your going to have to do the looping > ove rthe file with CF as the file will be on the web server so the DB > server will not be able to connect to it with a SP directly. > > Russ > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Lee Fortnam > Sent: 09 September 2006 16:36 > To: 'Coldfusion Development' > Subject: [CF-Dev] Import of 10k records from a tab delimited txt > Importance: High > > >- see footer for list info -< > Dear All, > > I have a system which needs to take a number of records (upwards of > 10k to > 12k) of peoples contact numbers from a surgery DB. Each patient may > have a mobile and landline so the format of the file is as follows: > > Pat ID NHS Number Home Mobile Email > ###### ###### ####### 0 0 > > OR > > Pat ID Number Type Number > ##### Mobile Phone ########### > ##### Telephone - Home ########### > > The numbers have been removed above to protect the innocent!!! > > There are a couple of different text formats as not all surgeries use > the same patient management system and they are all a pain in the bum > to get info out of. > > The issue is, the surgery can upload this file at any time using a > form on a secure website. This is being done using the Flash Forms > cf_flashUpload from ASFusion. Getting the file up to the site is not a > problem. The problem is when I need to import these records into the > DB. This file might be an initial insert in which case I need to push > all records into the DB, equally the file might be an updated file > from a previous upload in which case only the numbers that have changed need to be updated. > > Question is, what is the most efficient way to do this. I am using > CFMX7 and MS SQL 2000. My Stored Proc knowledge is not brilliant but > would welcome any guidance you can provide. > > At the moment I simply step through the file record by record and > check if the patient info is already in the DB, if it is and the data > is different, update the record, if not in the DB, insert it. As my > knowledge of Store proc is not good the logic for this is being > handled by CF and not in a single Stored proc as might be more efficient. > > Ideally, I need the system to receive the file, run the import and > then return the status of the import to the user, i.e. how many > records where added, updated etc, as this may take a few seconds I > would like to have a screen which polls the system to see if the > import has completed but this is not a must, just thinking aloud. > > Look forward to your input. > > With Regards, > > Lee > > _______________________________________________ > > For details on ALL mailing lists and for joining or leaving lists, go > to http://list.cfdeveloper.co.uk/mailman/listinfo > > -- > CFDeveloper Sponsors:- > >- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -< > >- Lists hosted by www.Gradwell.com -< > >- CFdeveloper is run by Russ Michaels, feel free to volunteer your > >help -< > > > _______________________________________________ > > For details on ALL mailing lists and for joining or leaving lists, go > to http://list.cfdeveloper.co.uk/mailman/listinfo > > -- > CFDeveloper Sponsors:- > >- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -< > >- Lists hosted by www.Gradwell.com -< > >- CFdeveloper is run by Russ Michaels, feel free to volunteer your > >help -< > -- Simon Baynes www.simonbaynes.com _______________________________________________ For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo -- CFDeveloper Sponsors:- >- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -< >- Lists hosted by www.Gradwell.com -< >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help >-< _______________________________________________ For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo -- CFDeveloper Sponsors:- >- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -< >- Lists hosted by www.Gradwell.com -< >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
