Re: Exporting Excel to Dataset
One option would be to use the open source POI libraries from Java, called from COBOL via JNI. Warning, JNI is not for the faint of heart. Jonathan Eosze | Sr Computer Sys Engr | IT Operations Mainframe Management 1 (IMS), Information Technology, USAA -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Ron Thomas Sent: Monday, August 03, 2015 12:20 PM To: IBM-MAIN@LISTSERV.UA.EDU Subject: EXTERNAL: Exporting Excel to Dataset 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 -- 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
Re: Exporting Excel to Dataset
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
Re: Exporting Excel to Dataset
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? If you have MS SQL Server at your installation, your MSSQL DBA's should be able to develop a SQL Server Integration Services (SSIS) application to transform your Excel worksheet into an XML file, which could then be FTP'ed to the mainframe PDS. You could then write COBOL application programs to do the XML PARSE to extract the information you need for your business logic. You may also be able to do something similar with MS Access. John -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Exporting Excel to Dataset
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 -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Exporting Excel to Dataset
I think that the open document format for spreadsheet files (*.ods) is an XML file in reality, you might want to investigate converting the Excel template to that format first. Excel since the 2007 version can save files in that format, using SaveAs. HTH Peter -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Ron Thomas Sent: Monday, August 03, 2015 1:20 PM To: IBM-MAIN@LISTSERV.UA.EDU Subject: Exporting Excel to Dataset 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 -- This message and any attachments are intended only for the use of the addressee and may contain information that is privileged and confidential. If the reader of the message is not the intended recipient or an authorized representative of the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail and delete the message and any attachments from your system. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Exporting Excel to Dataset
Several years ago, I developed an API which allows COBOL, ASM, etc. to read and write native excel files. To read, the unmodified Excel files is binary uploaded to sequential file or PDS member. JCL allocates sequential file or PDS member via JCL. COBOL program accesses sequential file or PDS member via API. Let me know if you are interested in additional details. Sam On Mon, Aug 3, 2015 at 10:36 AM, Farley, Peter x23353 peter.far...@broadridge.com wrote: I think that the open document format for spreadsheet files (*.ods) is an XML file in reality, you might want to investigate converting the Excel template to that format first. Excel since the 2007 version can save files in that format, using SaveAs. HTH Peter -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Ron Thomas Sent: Monday, August 03, 2015 1:20 PM To: IBM-MAIN@LISTSERV.UA.EDU Subject: Exporting Excel to Dataset 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 -- This message and any attachments are intended only for the use of the addressee and may contain information that is privileged and confidential. If the reader of the message is not the intended recipient or an authorized representative of the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail and delete the message and any attachments from your system. -- 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
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
