Professional Web Applications Themes

Import a CSV text file into SQL Server database andupdate it. - Coldfusion Database Access

: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 continued...

  1. #1

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

    :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

    Student-Kav Guest

  2. #2

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

    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.

    Dan Bracuk Guest

  3. #3

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

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

  4. #4

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

    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.

    mxstu Guest

  5. #5

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

    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.

    surenr Guest

  6. #6

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

    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.

    PaulH Guest

  7. #7

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

    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;
    Student-Kav Guest

  8. #8

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

    sure.


    PaulH Guest

  9. #9

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

    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)?

    mxstu Guest

  10. #10

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

    "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.


    PaulH Guest

  11. #11

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

    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.

    mxstu Guest

  12. #12

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

    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

    The ScareCrow Guest

  13. #13

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

    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?
    mxstu Guest

  14. #14

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

    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
    The ScareCrow Guest

  15. #15

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

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

  16. #16

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

    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.

    surenr Guest

  17. #17

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

    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.,

    Student-Kav Guest

  18. #18

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

    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()>

    surenr Guest

  19. #19

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

    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.??

    Student-Kav Guest

  20. #20

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

    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.

    surenr Guest

Page 1 of 2 12 LastLast

Similar Threads

  1. HELP! Can't find path to import text file
    By chrsjrngn in forum Macromedia Flash Actionscript
    Replies: 1
    Last Post: February 11th, 05:57 PM
  2. import Text file in MySQL table
    By Bob Bedford in forum PHP Programming
    Replies: 8
    Last Post: December 10th, 05:31 PM
  3. Import text file into database table
    By Marty in forum ASP Database
    Replies: 1
    Last Post: October 27th, 08:09 PM
  4. import a text file to database
    By atse in forum ASP
    Replies: 0
    Last Post: October 20th, 08:03 PM
  5. Import text file
    By PSH in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 8th, 01:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139