Cannot provide the data file table in File Adapter

[former_member655824]

Dear support,

We would like to read the file from the file server by using the file adapter. Here is the steps of my development.

1. Install the DPAgent tool to the side of the file server and registered the agent for file server.

2. Create the data file (MyData.txt) and config file (MyData.txt.cfg)in the side of file server as the following

eg. C:\DemoFileServer\SDI\data

C:\DemoFileServer\SDI\conf

3. The file adapter is created on my local pc and access to read file server.

Result: The connection test is successful. But the data file table (MyData.txt) cannot be seen in the adapter folder.

Instead of it, the config file table (MyData.txt.cfg) is provided by the adapter.

Although the root directory path and file format definition path are correct, what wrong with it?

Here is the content of file format.

FORMAT=CSV TEXT_QUOTES=" TEXT_QUOTES_ESCAPE_CHAR=" ESCAPE_CHAR=\ QUOTED_TEXT_CONTAIN_ROW_DELIMITER=false SKIP_HEADER_LINES=1 FORCE_FILENAME_PATTERN=MyData.txt FORCE_DIRECTORY_PATTERN=C:\DemoFileServer\SDI\data CODEPAGE= LOCALE=ja_JP COLUMN_DELIMITER=, ROW_DELIMITER=\r\n ERROR_ON_COLUMNCOUNT=false COLUMN=ORDER_ID;VARCHAR(10) COLUMN=CUS_ID;VARCHAR(10) COLUMN=SUP_ID;VARCHAR(10) COLUMN=PRD_ID;VARCHAR(40)

Best regards,

Nyein.

[werner_daehn]

In the agent config tool, the preferences of the adapters, the file adapter has a root folder configuration.

That is for security reasons, otherwise you could create a remote source with the path e.g. /windows and overwrite system files. My guess is, this value is either default or the setting is the cfg directory.

See the first screen here (I was using the graphical agentconfig tool at the time)

https://blogs.sap.com/2016/01/06/hana-smart-data-integration-the-file-adapter/

[former_member655824]

Hi wdaehn,

Thanks for your reply.

>>In the agent config tool, the preferences of the adapters, the file adapter has a root folder configuration.

I already defined the same root directory of the file adapter in the agent config tool liked as the RS root directory.

please look the following img.

[werner_daehn]

The first screenshot, the directory paths look odd. If they are invalid, the file adapter prints a warning into the log (I believe) and ignores it.

[former_member655824]

Hi wdaehn,

The directory paths in the first screenshot are normal because now i ' m using the jp keyboard and so

it is equal to C:\DemoFileServer\FileShare\SDI\conf. I'm not understanding why the file adapter cannot find the data file?

C:\DemoFileServer\FileShare\SDI folder can be accessed with read and write when we access to SFTP.

Best regards,

Nyein.

[werner_daehn]

@nyein pyaesone Wait a second..... I am running completely into the wrong direction.

Are you saying browsing is the problem or reading? Because for browsing, it is showing you the available structures which is the cfg file! Imagine you have 100 files of same structure - do you want to see the 100 files or just the one cfg file capable of reading the file?

When executing the select statement, then the individual files are read.

Or in short: Import the shown virtual table and execute a select statement against it - it will show the data.

[former_member655824]

Hi wdaehn,

I really not understand what is the problem browsing or reading?

In fact, I have been read the file from my local folder "D:\_XXX" successfully using the file adapter.

At that time , we saw the following structure.

Then MyData.txt is created as a virtual table and it showed the data successfully.

Second, i would like to read the file from file server using with the dpagent of the side of the file server.

At that time we see the following structure.

MyData.txt.cfg is created as a virtual table and it does not show the data and the following error occurs.

Error: (dberror) 403 - SQL error, server error code: 403. internal error: Error opening the cursor for the remote database Cannot find data file :C:\DemoFileServer\SDI\data\MyData.txt

I would like to know

1. why the file structure of the first and second time is different? why cannot read the MyData.txt file as the first time?

2. why the cannot find data file error occurs although the data file is located the root directory and the directory configuration is ok. what should i do?

Best regards,

Nyein.

[werner_daehn]

It has to be something obvious, we are just missing the point, I feel.

Is it possible on the server the file is called MyData.txt.cfg.cfg?

e.g. die File Explorer is set to hide extensions on the server?

How about proofing the files exist via a cmd.exe window?

[former_member655824]

Hi wdaehn,

Yes, you are right that the file extensions on the server is hidden and repaired the file extensions according the cfg file.

And then the file adapter rs is executed again but the following structure is still seen.

It is different from your structure (see "Using the Adapters" 2nd screenshot) the follows. You can see .txt instead of config at that screen shot.

https://blogs.sap.com/2014/12/08/hana-sps09-smart-data-integration-adapters/

why the .txt file cannot find in the structure?

Best regards,

Nyein

[werner_daehn]

@nyeinpyaesone72

If I remember correctly, I named all config files like "us_county_census.txt.cfg" and used that format definition to read the us census files for the various county and year files. The file name pattern read according the format was us_county_census*.txt in order to read all files matching the pattern.

But the logic is clear, isn't it? A xyz.cfg file is turned into a remote object named xyz. This is imported as virtual table of arbitrary name, e.g. v_xyz and the settings are copied into the virtual table properties.

When executing a select statement against the virtual table, you can add a where clause or a data provisioning parameter to specify the exact file to be read or read all files the virtual table definition matches.

[former_member655824]

Hi wdaehn ,

>>But the logic is clear, isn't it? A xyz.cfg file is turned into a remote object named xyz. This is imported as virtual table of arbitrary >>name, e.g. v_xyz and the settings are copied into the virtual table properties.When executing a select statement against the >>virtual table,

My config file name is config.cfg and so as you said that it seems to be turned into a remote object named config.

Then it is imported into my demo schema as virtual table as the following.

And execute a select statement against virtual table but no data found in the result.

Best regards,

Nyein.