text file import after upload 2

[jhall01]

I use CFFile to allow my clients to upload a text file to a folder on the server. I then want to import the text file into an existing table.

I can't put inserts into loops and have several other restrictions on my environment.

The page process on the upload is this:

upload a file to server using cffile, import the text into the database, then delete the file from the server, display results of the import to the client that did the upload.

I have the upload, and delete page coded, all i need to figure out now is how to import the text file automatically into the database when the file name is unknown until the upload.

I have never coded a stored procedure or trigger so if this is the easiest way, please baby step me through it!

We run Coldfusion MX & SQL Server 2000

Thanks in advance!!!!

Jon

 

[philhege]

Jon,

Create a test text file on the server with the same format as the files you will receive. (Note that this format is static; if the formats change among clients, you'll have to write different packages for each.)

Write a Data Transformation Services (DTS) package that imports the contents of the text file into the target table.

Save the DTS package as a Basic (.BAS) file.

Create a new project in VB6. Import the DTS .BAS file. Tweak the code to accept a file name as a run parameter. Compile the code into an executable.

Now, your CF code can grab the uploaded file, put it in a processing folder, and call the executable with CFEXECUTE, passing the text file name to the VB program. The program will grab the file and use the DTS code to upload to SQL.

Please see SQL 2000 Books OnLine for details about DTS packages, and TechNet/MSDN for VB6 projects.

As you can tell, it's not possible to do this strictly in ColdFusion, given your constraints.

Good luck!

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....

 

[philhege]

Ah, heck. I was dealing with multiple file names when I wrote my upload project. If you can name the file the same every time, and be sure to avoid concurrency conflicts, you can take a slightly simpler approach.

Write the DTS package as before. Schedule the package to run as a job in Enterprise Manager, but don't assign any run times to it.

Write a CFQUERY tag in your upload process:

exec msdb.dbo.sp_start_job @job_name = 'MyUploadJobName'

(Make sure the CF user has rights to MSDB and the sp_jobserver-style SPs).

Voila.

For more info about job server SPs, look up sp_add_job in BOL.

And, again, good luck!





Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....

 

[jhall01]

Thanks for the quick response, but in our shared environment we are not allowed to use cfexecute...so now what can i do?

 

[ecobb]

As philhege pointed out, you can write a Stored Procedure on the SQL Server that will call the SQL Server's DTS package, then you can just call the Stored Procedure from CF using the <cfstoredproc> tag, and as far as ColdFusion (or your server) is concerned it will be pretty much just like using a SELECT query.



Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[jhall01]

Ok great!!! Thanks,

But now I don't have access to VB. I have VB at home but cannot use it at work.

Maybe I can use this as an excuse to get my boss to purchase it!

Any other way of doing this with this prohibitors?

 

[ecobb]

You don't need VB...all you need is CF and SQL Server.

Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[jhall01]

Yes i was still thinking that i had to setup the stored procedure to accept a parameter!

Additionally,

I have no control over my clients naming the files. I could try to force a file name but i have many clients that will access this page on the same day every week. If 2 were to connect at the same time, how would that affect the file if they all had the same name?

I know they delete the file off after processing but there is always a possibility of having 2 connections within a few seconds of each other.

 

[philhege]

I think you can have control over the uploaded file name. This is a necessity anyway, since the upload process creates a unique file name that you have to retrieve.

As far as concurrency is concerned, that can be a problem. You could write an SP to set a flag value in a marker table, and disallow any other processing while the flag is set.

As M. Cobb said, this is strictly SQL Server; no VB required.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....

 

[jhall01]

Ok well i have decided that inserting text files is a harder thing to do especially in my shared environment with all the restrictions.

Here is what i have come up with:

Upload the txt file

Parse it using <cfloop> to pull out each record and build an INSERT statement to cycle through in the CFQUERY. I can't do an INSERT inside of a loop so i have to build a variable that holds all my INSERT statements seperated by a semi-colon.

once the insert is complete delete the file.

I have all this working great, except for the blasted date conversion.

I get this error:

ODBC Error Code = 22008 (Datetime field overflow)

here is my SQL code that is generated:

SQL = "INSERT INTO temp_disp(ant,btn,disp,listname,disp_date,line_number) VALUES('79118','2315687953','calbac','02/22/04','pic-up 1',4 );"

This is actually a string I have built from the CFLOOP:

'79118','2315687953','calbac','02/22/04','pic-up 1',4 which is called variable "innervalues"

now I try to use convert ( datetime, '02/22/04') which would make my sql look like:

INSERT INTO temp_disp(ant,btn,disp,listname,disp_date,line_number) VALUES('79118','2315687953','calbac',convert(datetime, '02/22/04'),'pic-up 1',4 );

same error!

Any help would be great!!!!

 

[philhege]

jhall01,

Your data doesn't seem to match up with the fields you're trying to populate. If "disp_date" is a date field, you're attempting to put "pic-up 1" in there.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....

 

[jhall01]

HEHE....I found that out just before you responded. I have been staring at code way to long on a Monday!



Thanks for all your help!!!!

Star for you Phil