FTP transfers of XLS files

[kennyapril]

The user sends files to the server once in a week and there are about 5 files.
the format of these files is xls format.

And these files should be mapped and transformed as per the requirement and loaded in the DB.

Do I need to change the format to csv and use sequential file to get the data as a source?
or is there any other way to get the data from xls format?

Also to get the data from the files do I files need to be in the server where Information server is installed or Can I get the data from other server also?

Can anyone give me some suggestions or changes?

[chulett]

They're going to have native Windows Excel files sent to a UNIX server for processing? Not a good idea. Have "them" convert the files to csv format by doing a 'Save As' in Excel first and then transfer the csv files.

[kennyapril]

thanks chulett,

I asked the user to send the files in CSV format so that I can get the data from the files through sequential file stage.

My other issue is the files are sent to remote server.
I did a search and found that FTP stage should be used for reading a file from remote server.

I never used ftp stage,can you help me how to use the stage?

is it like FTP----->sequential------>transformer----->DB
and in the FTP for URI do I need to give the path of that remote server file
and any other options to be filled.
please let me know

[FranklinE]

I never used ftp stage,can you help me how to use the stage?

is it like FTP----->sequential------>transformer----->DB
and in the FTP for URI do I need to give the path of that remote server file
and any other options to be filled.
please let me know

The FTP stage can be used to stream your data directly into the job, no need to land the data to a file first (that requires two jobs, one to FTP to sequential, another to read sequential into transformer).

I'm using FTP Enterprise in 7x exactly that way. The challenge is with your operating environment and getting the stage settings correct. You will need advice from your environment support on that, since every shop can be different.

[kennyapril]

thanks,

so the FTP stage(enterprise or plug in) can be used directly to get read the data and transform the data and then load it.

something like FTP--->transformer------->DB
which one do I need to use?
Do I need to talk to the environment support about the permissions to that server?

or something else?

[FranklinE]

thanks,

so the FTP stage(enterprise or plug in) can be used directly to get read the data and transform the data and then load it.

something like FTP--->transformer------->DB
which one do I need to use?
Do I need to talk to the environment support about the permissions to that server?

or something else?

Yes, as you describe it is how I use it.

In our 7x environment, we avoid using the plug-in stage. I wasn't aware that it was being used in 8x. Also, I understand that the 8x FTP stage is SSHv2 compliant (SFTP), something you need to find out about in your shop.

Your env support should help you with all the parameters involved. Permissions on the server should be for the appid that owns your jobs. Those details should be provided by your Unix admins. Good luck.

[kennyapril]

Thank you franklin

So I will use the enterprise stage which allows me to read the data from the file.
In that stage properties URI ---is this the path of the file?

Ftp command---what is this?
username,password is the access to the server
and the transfer type is ASCII.

please verify my doubts in that stage so that I can ask the permissions from the admin.

[FranklinE]

Thank you franklin

So I will use the enterprise stage which allows me to read the data from the file.
In that stage properties URI ---is this the path of the file?

Ftp command---what is this?
username,password is the access to the server
and the transfer type is ASCII.

please verify my doubts in that stage so that I can ask the permissions from the admin.

You're welcome, Kenny. I can't honestly answer your detail question because it is just different between environments. We use location aliases, for example, which may or may not include the path as well as the server name. URI stands for Uniform Resource Identifier, the common format in the Internet behind URLs. It is highly variable. I have no idea what your operating system has along those lines.

Bring those same questions to your env support. They should both understand them and have the answers. If not... I don't think anyone here can help you without coming to your shop and seeing what you have.

[kennyapril]

sure I will make sure with my admin department. thanks

Coming to the format of the file the top row contains the names of the columns does the FTP stage reads the file in the same manner i.e take the first row as the column names and the rest of the records as data.

[chulett]

Only if you tell it to.

[kennyapril]

In the place of URI do I need to give the path of that specific file in FTP enterprise stage?
and in the columns do I need to manually enter the names of columns?

and I used the transfer protocol as SFTP but this is not asking me any password is that OK.


please let me know

[chulett]

Just like any other stage, you either need to import the metadata or enter it manually. And "secure" tranfers (like sftp or scp) don't use passwords in a non-interactive mode.

[kennyapril]

Thanks
In the properties tab the URI do I need to give the path for that file?

and I tried to import the metadata through sequential file definitions but cannot view the files to import Is there any change in the definitions from import

[kennyapril]

I used the path in uri of ftp enterprise as

ftp://dxter.coc-eft.org:22/applicationid.csv

and gave everything username,password, transfer mode
in the columns tab I entered the metadata also.....but I got the same error...

FTP_Enterprise_1: uri : ftp://dxter.coc-eft.org:22/applicationid.csv is not valid remote file.

this file is SFTP kind of file.........please suggest me to get rid of the error..

[ray.wurlod]

You're using port # 22, which is usually for SSH, not FTP or SFTP. Check the port number you should be using.