DTW using ODBC and Excel

[Former Member]

Has anyone tried using ODBC to an Excel workbook as the input for DTW? I have gotten the ODBC process to work from Access but I can't figure what is needed for the "simple SQL" when using Excel. Any thoughts will be greatly appreciated.

Ross Unger

Business-First

[Former Member · Accepted solution]

Dear Ross,

For data records selected through ODBC, you can use SQL statements to

extract data records from the database.

To prepare data through ODBC, perform the following steps:

Choose Control Panel->Administrative Tools-> Data Sources (ODBC) to

create a Data Source Name (DSN).

Prepare object and sub-object data respectively for importing or

updating the data records selected through ODBC.

For example, when importing the Business Partner's object with the

Contact Person and Address, you separately select the data for Business

Partner and the data for Contact Person and Address through ODBC.

To Extract Data from Different Source, perform the following steps:

In the DTW Wizard Step 2, choose "ODBC" from the drop-down list "Source

Data Type".

Press Browser button, the Extract through ODBC dialog box will appear.

Select DSN from the drop-down list, then enter UserID and Password.

In the Simple Select Statement field, enter the SQL statement to extract

data through ODBC. To preview, press "Test" icon.

Press "Open" icon to specify the selection results to the object.

Note:

If the data files contain both header and item files, you must define

the primary keys, such as RecodKey. For example, for the SQL statement

"Select CardCode as RecordKey", * from OCRD can be applied to

BusinessPartners of the object oBusinessPartners. For the SQL statement

"Select CardCode as RecordKey", * from CRD1 can be applied to

BPAddresses.

If the data files contain only header files, you do not need to define

the primary keys. For example, you can retrieve data from table OACT

using the SQL statement "Select * from OACT" to apply to the object

oChartOfAccounts.

Regards,

Rakesh Pati

SAP Business One Forum Team

[Former Member · Accepted solution]

Rakesh -

Thanks for the response. I'm not sure I explained my problem correctly. Assume that I have put the data I want to import into one of the DTW templates and saved it as a standard .xls workbook. I now want to use the ODBC connection to this workbook as the input in DTW. What I am trying to do here is get around saving the Excel file as a comma/tab delimited file and use ODBC to grab the information from Excel directly.

Here is what happens:

• Set up the ODBC connection to the Excel workbook

• Enter the information into the Excel workbook

• Start DTW and select (for this example) Business Partners

• Select Data Type = ODBC

• Browse for DSN

• No user ID or Password is used on the Excel workbook

*Enter the "simple select statement." This is where I get stuck. I can't figure out how to create a select statement for Excel that the DTW can understand.

So is my assumption that I can use ODBC to get data from the Excel workbook incorrect? Or am I just missing something fundamental here. As I said, I have gotten this to work via Access, since Access understands the select statement.

Ross Unger

[Ezioze]

How i have done

[Ezioze]

Hi all.

I made a very simple select: SELECT * FROM [Sheet1$] and it works (Sheet1 is the name of the Sheet in Workbook)

[john_vichich]

The problem is the syntax for the select statement. It works for an Access database but the excel select does not appear to work. I tried quotes and other tricks. No success.