File Upload

[Former Member]

Hello Gurus, this thread might be a tough one

We need to upload Excel data onto SQLServer in a MII environment .  As being stated in many threads , there is no way that MII imports data from an Excel Sheet because there is no a 64 bits driver released by Microsoft yet .  Using the UDS installed at the user computer is not a practical way to solve our problem because the Excel name can change over time .

You may wonder why the end user can not capture the data directly using the MII igrid . Well ,  we already did that .  The user claims that the material balance they do every early morning  is created in Excel and entereing the results of the material balance in that grid is a duplicated effort.  So we were planning to let them use a MII page to upload the Excel file into the the MII server file system as a first step, and then importing into the SQLServer data base as a second step . Then , we discovered the problem stated in the first paragraph .

So,  now our planning heads to use our old xMII server to do the second step .  The reason of this thread is knowing how to do the first step. To upload the Excel File from a MII page into the xMII server file system  . I discovered that I need to be fluent in building Servlet and also in Java . 

Someone of you can tell me how to do it ??  Thanks a lot for any help .

Best regards,

[Former Member · Accepted solution]

Thanks to all of you .

Rajib:
is it a good practice to installe POE at a NW Server ¡? By the way what is POE ?

Arnaud and Christian :

What do you mean by "Upload the data directly to MII through code "

Best regards,

[former_member185280 · Accepted solution]

Write some VB code in your excel file to send the data to MII via whichever service suits your needs.

google for something like "excel http post" etc. There are plenty of examples out there that you can modify to fit your needs.

[Former Member]

Hello Fernando,

I found 1 solution for this. But try and see how far you are able to solve your issue.

1) Save your Excel Sheet as a Tab Delimited Text File (.txt).

2) Now Open the Generated new txt file and you can see all the values are separated with Tab( approx 8 Spaces ) as separation.

3) Now Copy a Single Tab and Replace entire document with Comma(,) in place of Tab.

Sample data looks like this

Id,Name

1,Name1

2,Name2

3,Name3

4) In MII 14.0 There is a Advanced Flat File Parser which generates XML using data generated in Step3.

5) After getting a proper XML you can play with it.

If needed , i can send you a recording of this process.

Regards,

Sriram

[Former Member]

If possible.. we can create xml file while data is loaded in excel sheet.

Once saved, create xml file(any name) and save it somewhere ..from where MII can pick it up(check directory and pick file).

Regards

Ruchir

[former_member185280]

- Create a VBA script in a template Excel which they would use to enter the data and then upload the data directly to MII through the code.

Based on my experience with this kind of requirement this is the solution I would recommend.

[Former Member]

Hi Fernando,

It is not easy to convince the user to upload the XL file to certain location. Then convince the security to give open the server to upload file using external custom code. PCO also can not dynamically change the user specific file name in runtime.

So what you can do is download POE from apache server. Keep it in NW server and write a JSP in MII to give the uploading file functionality to user. This is little performant but works.

If you want little better performance then please use web Dynpro Java with POE. That works too.

User is happy and you too.

ps: POE supports .xl and .xls as well

Regards,

Ccreativeweblogo

[Former Member]

Hello Fernando, have you also considered the following option which would enable to remain on the same server:

- Ask the user to save the Excel document into an XML format and upload the document to MII.

- Create a VBA script in a template Excel which they would use to enter the data and then upload the data directly to MII through the code.

- Use an OPC Server for Excel - which enables you to interact with Excel as a OPC data source and then use PCO.

for example: http://www.matrikonopc.com/opc-drivers/839/index.aspx

- Best of all, query the datasource directly... using PCO or else and actually providing an alternative to manual entry.

And I would be missing other options, too...

Hope it helps,

Cheers,

Arnaud