You can use a VBS macro to save each sheet as a .cvs and then ftp the .cvs
file(s)
Sub export_cvs(sheet_to_export,export_file_name)
Sheets(sheet_to_export).Select ' select the sheet to export
Application.DisplayAlerts = False 'suppress error prompts
ActiveWorkbook.SaveAs _
Filename:="C:\export\path" & export_file_name", _ 'drive and export path
could also be parms
FileFormat:=xlCSV, _ 'as .cvs
CreateBackup:=False
Application.DisplayAlerts = True 'turn error prompts back on
Sheets(export_file_name).Name = sheet_to_export 'SaveAs renames the
sheet and the work book
End Sub
Dennis Roach, CISSP, PMP
IT Security Administration Senior Analyst
2727 Allen Parkway, Wortham Building 3rd Floor, Houston, TX 77019
Work: 713-831-8799
Cell: 713-591-1059
Email: dennis.ro...@aig.com
Report information security incidents to: aiglr_security_incide...@aig.com and
(818) 673-4030
All opinions expressed by me are mine and may not agree with my employer or any
person, company, or thing, living or dead, on or near this or any other planet,
moon, asteroid, or other spatial object, natural or manufactured, since the
beginning of time.
-----Original Message-----
From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf
Of John McKown
Sent: Monday, August 03, 2015 1:03 PM
To: IBM-MAIN@LISTSERV.UA.EDU
Subject: Re: Exporting Excel to Dataset
On Mon, Aug 3, 2015 at 12:19 PM, Ron Thomas <ron5...@gmail.com> wrote:
> We have a requirement that looks for exporting an Excel template with
> data in a tabular format from an FTP folder location to a mainframe
> PDS which later needs to be accessed via COBOL for some business processing
> logic.
>
> We know that this works fine if the input Excel file is in .CSV format.
> What we are looking for is, if anyone is aware of any such
> functionalities where we can accept and export the Excel template as
> such to a PDS that is readable by COBOL without being converted to a .CSV
> file?
>
> Thanks
> Ron T
>
>
I'm a bit unclear about what you mean by "... without being converted to a
.CSV file". Do you mean that you want to directly transfer the actual .xlsx (or
.xls) file into a member of a PDS without
any intermediate file on the sending side? I don't know of an FTP client which
can do that.
Disclaimer: I am not knowledgeable about either option. And I'm a Linux person,
not a Windows person.
My first thought is, assuming this is on Windows, is to write a PowerShell
which runs a program, say in .NET, to access the data in the Excel spreadsheet,
have it create a CVS export, then ftp that.
Now, depending on what you can do on the sending side, there are Python
libraries which can read an Excel spreadsheet, http://www.python-excel.org/.
Or you can set you the Excel spreadsheet to be an ODBC source and read it in
Python using https://wiki.python.org/moin/ODBC .
Also, there is an ftp package for Python as well (
https://docs.python.org/2/library/ftplib.html and
https://docs.python.org/3/library/ftplib.html). This solution would require
a bit of "glue" logic in Python to do. Likewise, there are similar libraries in
the R language. But R is a bit more specialized and so would, IMO, take a
person who does not have any knowledge of either language to learn R rather
than Python. Also, there are more examples on the Web in Python to use as a
starting point.
I am assuming that you are on Windows. You can get Python for Windows here:
https://www.python.org/downloads/windows/ There is also a version of Python for
Mac and one for Linux.
The R language for Windows can be gotten here:
https://cran.r-project.org/bin/windows/base/
You'll also need the openxlsx (or XLConnect) and RCurl packages from:
https://cran.r-project.org/. Or, like with Python, you could use ODBC in R
using RODBC from the above site.
Curiosity: Why no .CSV file? I am guessing that you have an end user updating
some Excel spreadsheet and you don't want them to have to save it as CSV. In
which case, perhaps the PowerShell approach is best. If you really don't have
the disk space for the CSV, then I guess the Python or R solution would be, at
least, decent.
--
Schrodinger's backup: The condition of any backup is unknown until a restore is
attempted.
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
----------------------------------------------------------------------
For IBM-MAIN subscribe / signoff / archive access instructions, send email to
lists...@listserv.ua.edu with the message: INFO IBM-MAIN
----------------------------------------------------------------------
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
