Import a CSV text file into SQL Server database andupdate it.

[Student-Kav]

:confused;

Hi Guys and Gals,

I need to update a SQl Server database from a CSV text file . This must
happens everytime. Someone dumps CVS text file and the user must submit this
file and on pressing submit it updates teh sql server database with the data
and also checks whether the data is duplicate or not and if it is than donot
update the databse and if there is a modification than update accordinly and if
it is new data than insert the new data in the table.

I have no idea how to do this...

help....

thanks.
kavin

[Dan Bracuk]

My approach would be to use a separate table on the database. If SQL server
allows temporary tables, create one in your cf template. Otherwise, create a
working table on your database, and, near the start of your cf template, run a
query to delete all the records.

Next, loop through your csv file and insert each line into the
temporary/working table. It might be easier to convert the file to a query
with cfhttp first, I don't work much with files.

Finally, it's two more cfqueries, one update and one insert.

[Student-Kav]

How would you put it up in Cold fsuion, I mean how would u wrote the query?

[mxstu]

Student-Kav,

Since you are using SQL Server, you may want to view this
[url]http://www.users.drew.edu/skass/sql/TextDriver.htm[/url]. It explains how to create
a linked server that will allow you to access the CSV files just as you would a
regular table in your database. This would avoid the need for reading in the
file and looping over it line by line. If you have any questions, let me know.

[surenr]

I had this same problem last summer, just give a look at the below link. It
will surely help you.


[url]http://cfregex.com/cfcomet/SQL_Server/index.cfm?ArticleID=A51CB8F7-C297-4E80-92C[/url]
C7A8907B0A5B2

The link will provide you with a code to enter a csv or text file into a
database.

[PaulH]

if this is a regular task, take the time to learn DTS. besides sql server's
books-on-line (BoL) there are pelnty of web resources:

[url]http://www.sqldts.com/[/url]

a bit of googling should reveal even more. this is something worth knowing.

[Student-Kav]

Thanks a lot guys for all your help.Really appreciate it.

Although I have one more question....can we also use DTS for excel files?

thanks,
kavin:cool;

[PaulH]

sure.

[mxstu]

PaulH,

Do you know if DTS + Excel suffer from the same datatype issues as cfquerying
and Excel file? (ie. ODBC does not always determine the correct datatype of a
column. For example if a column contains 3 text values ('abc', 'efg', 'hij')
and 9 numbers, it may determine the column is "numeric" and convert the text
values to null)?

[PaulH]

"sort of" but DTS will (i think) correctly ignore the bad data instead of
throwing an error. this is more of a procedural issue than technology. which
datatype is correct (though DTS does seem to have a bias towards numeric data)?
if you're in this kind of situation, you can and should program DTS to scrub
the data, count before/after, log results, force it all as varchar, etc as safe
guards.

[mxstu]

I agree about scrubbing the data. I just wasn't sure if you could force the
types for an excel file in DTS (ie. force it to handle a column as varchar if
DTS thinks it is numeric) Obviously casting after the fact won't do any good if
DTS has already dropped the varchar values because they are not numeric.

[The ScareCrow]

mxstu,

In a dts package this is done in the transformation.

Here is a really good tutorial explaining how to do this.


[url]http://www.sqlservercentral.com/columnists/JTallamraju/data_import_functionality[/url]
_using.asp

You may need to create an account to view.

Ken

[mxstu]

Ken,

Thanks, that looks like a good article. Maybe the eyes are just tired, but do see a "download sample files" option anywhere on that page?

[The ScareCrow]

It would appear that the download file is not available any more.

But the author's email address is in his bio, so you could email him to see if you can get it.

Ken

[mxstu]

Good idea. Drat! Here I was all excited and ready to try it out. Now I have to wait. I hate that ;-)

[surenr]

I had been using the code in the below link:

[url]http://cfregex.com/cfcomet/SQL_Server/index.cfm?ArticleID=A51CB8F7-C297-4E80-92C[/url]
C7A8907B0A5B2
and it was working fine with CF5.0 and SQL and CF running on the same server.
Now that I have installed CF7 and have CF7 and SQL on different servers I get
the following error:

An exception occurred when instantiating a Com object.
The cause of this exception was that:
coldfusion.runtime.com.ComObjectInstantiationExcep tion: An exception occurred
when instantiating a Com object..

Any suggestions would really help.

[Student-Kav]

HI surenr

was wondering if you have the code that u executed using this tag cause i am
not able to make out as to how to go ahead with that piece of code...Will it
update my master table from a text CSV file.

Also guys I have another problem, the data in the text file can be duplicate ,
hence what i want is that the data should be matched with the existing data in
the master table and if there are duplicates than it will omit those from
adding to the database and if they are not than it will add the data from the
text file to the database.

Thanks,
Kavin.,

[surenr]

Normally you cannot enter duplicate values in the SQL server, You have to write
a seperate procedure that would read the text file and enter only distinct
fields.With regards to the code, the best way around is to create a DTS
package. In the DTS package you need to add the SQL Server and Bulk insert.
Give your Excel file name and mention the table name. Remember you need upload
the Excel file on the SQL server. You can upload the file using the cffile
option if you have both the SQL and Coldfusion running on the same box. Else if
you have the SQL the Coldfusion on different server then you need to first
upload the file on coldfusion server then using cfftp you have to transfer the
file to the SQL server.
Now on your coldfusion site just add the below code:




<CFTRY>
<CFOBJECT TYPE="COM" NAME="objDTS" CLASS="DTS.Package" ACTION="CREATE">
<CFCATCH TYPE = "Object">
<CFSET error_message = "The DTS Package Object Could Not Be Created.">
</CFCATCH>
</CFTRY>

<CFTRY>
<CFSET r = objDTS.LoadfromSQLServer("SQL_SERVER_NAME","USERID for the DTS
Package","SQL_Password",0,"","","","DTS_Package_Na me","")>
<CFCATCH>
<CFSET error_message = "The DTS Package Could Not Be Loaded From the
SQL Server at this time.">
</CFCATCH>
</CFTRY>

<CFIF IsDefined("error_message")>
<CFOUTPUT>#error_message#</CFOUTPUT>
</CFIF>

<CFSET p = objDTS.Execute()>

[Student-Kav]

Hi,

Well i did get the first part that u sent, however I am confused about the
code, how will sql know from where to copy the data dn where to place it. I
mean in theexecute() fucntion what exactly happens. I hope ur getting what i am
saying.??

[surenr]

I am not sure to what you are referring to. In the DTS package you have to
mention the file path and the table where to store the text file.The code just
creates an instance of the DTS package and executes it.