Import data from Excel

[Former Member]

Hi

I'm having real problems trying to read in data from an excel spreadsheet.

I created an ODBC system DSN pointing to my spreadsheet on the MII server, called myDSN.

I then created a new IDBC data server with the following settings

JDBCDriver : sun.jdbc.odbc.JdbcOdbcDriver

ServerURL : jdbc:odbc:myDSN

Username, password and validation query I've left blank.

When I try to use this server I get an error message "Cannot create a connection to the database".

Any ideas?

[jcgood25 · Accepted solution]

Two questions come to mind.

1. Why do you want to connect to a spreadsheet....

2. Why are you using the Sun jdbc odbc bridge instead of the OleDB UDS?

Regards,

Jeremy

[Former Member · Accepted solution]

Jeremy

1. That's the requirement I'm afraid. Although I understand and share your concern, the fact is that Excel is the tool of choice for the vast majority of users in my company, and I suspect most other companies too.

2. I opened help, typed in "excel", and was given the "IDBC Connector" page, I quote: "SAP xMII can use a JDBC/ODBC bridge to access local or remote ODBC data sources such as Microsoft Access and Microsoft Excel", though of course it doesn't tell you how to do it, hence my question. I admit that it does give you a warning that this can be dodgy, but again, doesn't give you any other ideas.

From trawling through this forum I had tried the OLEDB route, but again, the help doesn't tell you what to do, and I couldn't get it to work either, error message "Connection Refused: connect".

Help please!!!

[Former Member · Accepted solution]

Hi Nick:

Using the OLEDB UDS which ships with the UDS 4.0 is the preferred method for connecting to Excel.

Based on an earlier response, I assume you have downloaded the latest version of UDS 4.0. If not, that would be your first step. UDS 4.0 comes with its own help file which is included in the UDS 4.0 installation package. You can access the help from the Help menu on the the UDS Admin Console. The help includes instructions for connecting to Excel, Access, OSISoft PI, and GE iHistorian (Proficy). You can find the instructions for Excel under SAP xMII OLEDB UDS>Data Provider Configuration>Microsoft Excel."

The instructions will guide you through configuring both the UDS instance an the OLEDB connector in MII. The steps are very straignt forward, but be sure to do every step. There are a couple of settings which can cause the UDS instance to behave oddly if not set correctly.

The one caveat you should be aware of, is that OLEDB is unable to share an Excel worksheet. So OLEDB can not open a spreadsheet if someone else has it open, and users can not open spreadsheets already opened by OLEDB.

Regards

Christopher

[Former Member · Accepted solution]

Thanks Christopher.

At the risk of appearing really stupid, how do I get in to the UDS Admin Console?

[Former Member · Accepted solution]

Hi Nick:

The Admin Console is where you create and configure the UDSes. A short cut should have been created in the Start Menu. Assuming you installed using default values, "Start>All Programs>SAP xMII>UDS>Admin Console"

Best Regards

Christopher

[Former Member · Accepted solution]

Christopher

As I can't find any links to it anywhere I am presuming I don't have it installed (I certainly didn't install it myself).

So a few more questions

1) Where do I get it?

2) Is it a server install?

Thanks again

Nick

[Former Member · Accepted solution]

You can find it on SAP Marketplace, Download SAP xMII UDS 4.0

It includes the "OLE DB UDS"

[jcgood25 · Accepted solution]

Nick,

Here are some additional links to installation and help documentation:

http://service.sap.com/~form/sapnet?_SHORTKEY=01100035870000705232&_SCENARIO=01100035870000000202&;

http://help.sap.com/saphelp_xmiiuds40/helpdata/en/index.htm

Regards,

Jeremy

[Former Member · Accepted solution]

One step closer!

UDS is now installed, and I followed the instructions to create the connection string, and successfully tested it by clicking "Test Connection" button. Then got an error message when I clicked "OK" - "There was an error with the string. The value will be cleared. Unspecified error".

Any ideas?

I also tried to enter a connection string manually, but that didn't seem to work either

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\upload\btp.xls;Extended Properties="Excel 8.0;HDR=Yes"

Again, anything obvious wrong with that? It saves OK, but when I try to connect to it from xmii I just get an error "Connection refused: connect". I've entered "localhost" in the IP address and I chose port 9000 at random.

Thanks for all your help so far.

Nick

[Former Member · Accepted solution]

Hi Nick:

> UDS is now installed, and I followed the instructions to create the connection string, and successfully tested it by clicking "Test Connection" button. Then got an error message when I clicked "OK" - "There was an error with the string. The value will be cleared. Unspecified error".

That particular error usually occurs when there is a problem with the Access Permission settings. On the Data Link Properties dialog box go to the Advanced tab. Select the Read option and clear all other options. Configure everything else as normal, and you should be able to successfully save.

> I also tried to enter a connection string manually, but that didn't seem to work either

The connection string must be built through the Data Link Properties dialog box, because the connection string is encoded.

> Again, anything obvious wrong with that? It saves OK, but when I try to connect to it from xmii I just get an error "Connection refused: connect".

No need to go to MII; if the UDS instance won't start, that is an indication that your configuration is bad. The UDS instance must be running before MII can connect to it, otherwise you will get this error.

> I've entered "localhost" in the IP address and I chose port 9000 at random.

When you configure your UDS instance, be sure to choose a port number that is not in use by another application. The UDS Help has a section on selecting port numbers including methods to see which ports are in use. This is the port number you will use in you MII connector configuration.

As an aside, the UDS help includes advice on debugging and troubleshooting. Since you are new to the UDS, I would suggest you take a look at them especially the OLEDB specific sections. There are a couple of quirks with Excel that you should be aware of.

Best Regards

Christopher

[former_member209705]

Hi Rick,

Little thought, it seems you're pretty much doing all your validations in SAP through RFC. And using MII for just picking up files.

So why dont you directly upload your excel file to SAP through FTP and get it done (provided your source system is in the same network).

Please ignore me if am wrong. Couldn't keep myself away from writing this.

Regards,

Ravi shankar

[Former Member]

Hi Nick,

Do you want to read the data in Excel sheet and insert it into any Database(Oracle) ?

Regards,

Anil

[Former Member]

No.

When it is finished my system will take a list of materials from the user (in a spreadsheet), validate it against various ztables in R3 (e.g. a list of valid UOM codes), then convert it into a pipe delimited format and FTP to SAP, where it will be picked up as part of our standard PDM (Teamcentre) interface.

The spreadsheet will have 3 tabs; Material, MPN (Manufacturer part numbers) and BOM. The MPN and BOM inputs will be validated against the Material tab.

Everything else is more or less done, it's just the initial inut of the spreadsheet that is tripping me up.

Thanks

Nick