MVSFORUMS.com

monaco · Beginner Joined: 20 May 2004 Posts: 77 Topics: 31

Hi all,

i'm trying to create a JCL of FTP from local to HOST.

Is there a way to do it if the file i'd like to import is a excell file(.xls)?
If there isn't the way, how can i convert to .txt file and could you help to find the JCL to import form .txt TO host?

Kind regards.

superk · Advanced Joined: 19 Dec 2002 Posts: 684 Topics: 5

To answer the first part of your question, FTP doesn't care what the format of the data is, so yes, you could FTP an Excel .xls file to the host (in BINARY transfer mode, of course). The question is, what would you do with it from there? After all, it is in a format that only an application such as Excel can read.

For the second question, Excel allows you do do a "Save As" for any file. You would probably want to "Save As" either "Text (Tab Delimited)" or "CSV (Comma Delimited)", or as pure un-delimited text, after which the file can be FTP'd in the ASCII transfer mode, and will be able to be easily processed by a host-based application.

monaco · Beginner Joined: 20 May 2004 Posts: 77 Topics: 31

Thanks for your quick reply. The problem we have is twofold:

If we export a .xls file to an un-delimited .txt file, the text file automatically breaks the rows after 241 characters. Which means that the file we want to create - which has about 1530 charachters per line - is not exported in a way that the host can read properly.

If we export a .xls to any other delimited .txt file (tab), the host cannot read this file (characters are distorted, unevenly spaced,...).

So, what we are looking for is a solution to export a .xls file to a proper host file, that can not only be read but uploaded into a DB2 table as is.

Do you have any ideas, how we could best do this?

Again, thanks a lot for your support!

superk · Advanced Joined: 19 Dec 2002 Posts: 684 Topics: 5

I like to use either the comma-delimited (CSV) or XML formats, as many of the data-integration and EDI mapping tools are designed to deal with these particular data formats.

kolusu · Posted: Mon Nov 15, 2004 10:50 am Post subject:

Monaco,

You can retain the spaces in a excel file to load the data to the mainframe.

For ex: if you have column in db2 which 10 bytes long. And your excel file has just 'abc' as a value for this column. Now when you upload this excel file you will loose the trailing blanks and the data gets distorted, unevenly spaced).

To avoid this and retain the trailing spaces code this formula in excel

Cogito-Ergo-Sum · Posted: Wed Nov 17, 2004 11:34 am Post subject:

When saved as CSV and exported to mainframe, the DB2 upload utility (forgot the name, sorry) can upload a delimited dataset. This is true for the latest version, though.
_________________
ALL opinions are welcome.

Debugging tip:
When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
-- Sherlock Holmes.