IMporting Data from Excel Into SQl Server

John Davison
John Davison
in AppWorks #1

I am needing to have a form that allows the user to browse to the location of the excel spreadsheet and then upload the data from one sheet into a sqlserver table..

 

Has anyone been here before? Any advice v.helpful as at the moment all I can see is a large chunck of code to try and emulate this.

 

Thanks

Tagged:

Comments

  • Ethan Beisher
    Ethan Beisher E Community Moderator
    #2

    I have not done this, but I would think one approach would be:

     

    - Use a standard Attachment Clip in a MBPM form

    - Have a Visual Script "When Action Completed" for the action that:

        - Uses the Document "Get Attachment" to save that attachment somewhere on the MBPM Server

        - Use a Commands "Code Activity" to have C# code insert that spreadsheet into the database. Here is an example.

  • John Davison
    John Davison
    #3

    Thanks for the link.... I couldn't see the option to copy from excel into the Database, so I wouldn't mind some steer there.

     

    Also in the examples you poimted me towards uses the using statement which I beleive cannot be used in the scripting screens.

     

     

  • Monaj Verma
    Monaj Verma
    #4

    Hello Everyone,

    I'm facing one problem since last night. Actually I want to import data in SQL Server from different file format such as csv, txt, sql etc. So I want to know whether any option in SQL Server to perform such kind of actions or I have to use any third party tool to resolve this problem. Please give me your valuable suggestion.

    Thanks in advance!

  • Tony Wheeler
    Tony Wheeler
    #5

    Hi JD, If you chose to go this route,  there wouldn't really be an option to just copy to the database. You'd be treating your user's newly uploaded xlsx file as a data source, selecting all the rows from it, then mass inserting the rows into your SQL database. This will require some code in a server side script. You would have to first write the file to a temp location from the database (after the upload) using GetAttachment.  Also, "using" statements are absolutely supported in server side scripts. Anything in the .Net 3.5 framework can be used as long as the supporting DLLs are in the proper folders. See "Custom DLLs" in the Designer User Guide.

  • Tony Wheeler
    Tony Wheeler
    #6

    Hi MonajVerma, In your case, I would recommend looking into SSIS... some people still refer to it as DTS.  This comes with SQL Server and allows for a semi- visual way to import data from many different sources and formats... Although, for one-off jobs with hairy insert/update logic, the path of least resistance is often to just write a quick little console app that selects your data table from one source and inserts the rows into another. I would check with your DBA as they may already have utilities/methods in place because pushing data around is a fairly common task.

  • Ethan Beisher
    Ethan Beisher E Community Moderator
    #7

    You can use the using statement. It may be easier to create a separate Server Script to do your coding in.

Categories

image
OpenText CE Products
AppWorks
Gateway & Platform