Error when DS loads Excel data into table using FTP

[venkataramana_paidi]

Hi ,

I want to load excel file from the FTP. I have created one folder FTP called test  and kept the excel file .

But when I am trying load excel file into table  or view the data from excel file  it is throwing error like

FTP could not transfer file </TEST/EMP.XLSX> from host <www.0catch.com>: <>. Ensure that the FTP relative path for the SAP server working directory is set correctly such that the data file is accessible to the FTP program.  Also, ensure that the target path, where the transferred file is written to, is correct.

I am using DS 4.1 SP2.

Please help me how to resolve this issue.

Thanks & Regards,

Ramana.

[Former Member]

Hi Venkata,

There is 3 main steps while transferring xls files from FTP server to your database;

1- Getting file from FTP server to your application server

     - You have to have an writable temporary directory on your application server to put your file on that.

     - You need to specify your worksheet name.

    

    

2- You have to add BOExceladaptor on your Job server .

     -That BOExceladaptor will open and read file(xls) on your temporary directory

Basicly you can do it :

You can only configure one Excel adapter per Job Server. Use the following options:

• On the Status tab, click the job server adapter at right to configure.

• On the Adapter Configuration tab of Adapter Instances page, click Add.

• On the Adapter Configuration tab, enter the Adapter instance name. Type BOExcelAdapter

(required and case sensitive).

for more you can read it on ;

DS Designer Guide - 6.8.1 To create a Microsoft Excel workbook file format on UNIX )

3- Insertin data to your tables

It works only for XLS not for XLSX.

Best Regards

[venkataramana_paidi]

Hi Ali,

Thanks for your explanation .  I am using SAP DS 4.2 SP03. There is no excel adapter in this version .

I am following same steps provided by you except excel adapter.

But I am getting the same error.

Please correct if  I did any mistake.

Thanks & Regards,

Ramana.

[Former Member]

Hi Ramana,

Could you show me your configuration on SAP ds management console management screen ;

Thanks,

[venkataramana_paidi]

Hi Ali,

Please find my management console screenshots.

Thanks & Regards,

Ramana.

[Former Member]

Hi ramana,

Could you click to JS_LOCAL on SAP ds management console, if you dont have BOExcelAdapter please add this.

Then try again.

Best Regards,

[venkataramana_paidi]

Hi Ali,

Thanks for your response . For adding Excel Adapters, there is no BOExcelAdapter in the Adapters list. In my second sheet contains the complete list of Adapters shipped with software. I am using DS 4.2.3.

Thanks & Regards,

Ramana.

[Former Member]

Hi Ramana,

Please Follow these steps for adding BOExcelAdapter ;

Language is Turkish So i make markers for all screen shots

1-SAP ds management console - Administrator and click your "Adapter";

2- Click "Adapter Configuration"

3- Click to "Add"

4-Click to the "MSExcelAdaptor" from list;

5-Now give name  "BOExcelAdapter" . Write the same word "BOExcelAdapter" , Then choose SSL protocol "false" then save that configuration.

6.Then go  Adapter Screen again and select your new "BOExcelAdaptor" and click run. You need to see green checked sign .

Thanks,

[venkataramana_paidi]

Thanks Adil for your continuous support with screenshot.

But I couldn't found the MSExcelAdapter in the Adapter's list. Please find the below screenshot.

I have searched in the technical manual also but I couldn't found anything about MSExcelAdapter.

Thanks & Regards,

Ramana.

[Former Member]

Hi Ramana,

I see that you installed you Data services on a windows machine thats why there is no MSExcelAdapter I think. I am working on Linux machine. Maybe you dont need an excel adaptor for windows machine.

So we can focus the first part. You need a temporary path on your server. (not FTP server).

then it will have to be writable.

Is it ok ?

Thanks,

[venkataramana_paidi]

Hi Ali,

What is the mean of temporary path on server.(Not FTP server). Could you please elaborate this sentence.

Thanks & Regards,

Ramana.

[Former Member]

Hi Ramana,

Bacisly system works like that ;

Data services works on an application server and Ftp is an external source for that.

Data services Job server only gets (copy) xls file to temp_folder on your application server.

Data services Opens xls file (on temp folder) to read rows.
inserts to db or where did you want.

Regards,

[venkataramana_paidi]

Hi Adil ,

Thanks for your prompt support and helping me to understand ftp file reading .

I dont know what is going to wrong , still I am facing the same issue.

I will explain the step by step and let me know where I have to correct myself.

Step 1: I have one free ftp site and I created temp folder and kept the emp.xls and emp.xlsx files .

2. I changed the folder permissions 777 in ftp.

3. I have created the temp folder in my laptop D drive .

4.  I executed the job then I am getting the below error.

5. I have tried to copy same emp file in the temp folder of my D drive  and executing the job . After executing the job emp file is not available .

Thanks & Regards,

Ramana.

[former_member200473]

Hi Ramana,

Just a guess:

1- Hope there should not be any issue with firewall or anti-virus which may block the connection.

2- can we pass the IP addess of Server instead of name.

3-do we need to change any configuration at CMC level for user access rights to access ftp file.

Thanks

Shiva Sahu

[Rishabh_Awasthi]

Hi Venkata,

After placing the file on FTP right click the file and give the access rights permission.

Regards,

Rishabh

[venkataramana_paidi]

Hi Risabh,

I gave the full permissions for the file . Please find the below screen. I can download file from ftp to my local system.

Thanks & Regards,

Ramana.

[Rishabh_Awasthi]

Please refer,

FTP Data Transport logs and errors

Error when Job Server loads Excel data into DS (FTP)

Regards,

Rishabh

[former_member136842]

Hi,

the Username looks strange. Are you sure this one is correct?

Can you pass the URL into a Browser please and check if you are able to download the file?!

Regards

-Seb.

[venkataramana_paidi]

Hi Sebastian ,

Yes  I uploaded the file using filezilla . I can import from ftp also.

Please find the below screenshot.

Thanks & Regards,

Ramana

[former_member136842]

Hi,

just in case - try to write the Filename in the exact case as we can se it here in Data Services. So not "EMP.XLSX" but "EMP.xslx"

Regards

-Seb.

[venkataramana_paidi]

Hi Sebastian ,

I have changed extension from XLSX to xlsx  in the data access tab of the excel format.But it is throwing the same error. Please find the below screenshot for error.

Thanks & Regards,

Ramana.