|
View previous topic :: View next topic
|
| Author |
Message |
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
Write an Excel macro to output the data exactly as you want in on the mainframe. I know it is VB, but you get way more control over your output format than you do when using the standard Excel exports.
The following will loop through the data and put it out in the following format:
A1|B1|C1|D1
A2|B2|C2|D2
etc...
| Code: |
Sub WriteDataToFile()
Dim outfile As String
Dim RowCounter, ColCounter As Integer
Dim ColumnSeperator As String
Dim outline As String
ColumnSeperator = "|"
outfile = "C:\outputfile.txt"
outline = ""
' Set fs = CreateObject("Scripting.FileSystemObject")
Open outfile For Output As #1
For RowCounter = 1 To ActiveCell.SpecialCells(xlCellTypeLastCell).Row
For ColCounter = 1 To ActiveCell.SpecialCells(xlCellTypeLastCell).Column
outline = outline & CStr(Cells(RowCounter, ColCounter).Value) & ColumnSeperator
Next ColCounter
outline = Left(outline, Len(outline) - 1)
Print #1, outline
outline = ""
Next RowCounter
Close #1
End Sub
|
|
|
| Back to top |
|
 |
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
Disregard this line:
' Set fs = CreateObject("Scripting.FileSystemObject")
It is left over from my testing. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Yes, many of these things might be done - but if it were my requirement i would manipulate the data in my own mainframe code. When/if the requirement changes, i'd only have one place to modify.
As this is something that will happen repeatedly, i would surely not recommend a solution that required manual work each time at either end. The more things done manually, the longer it takes and the greater the chance of error.
Sounds like *.prn won't be much help due to the length restriction - i suppose that limit is because the intent of the .prn is a print file? |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
| You can't read the contents of an Excel file (stupid proprietary stuff) on the mainframe so the process will always require some kind of manual intervention, whether it is a Save As... or something else. |
|
| Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006
Posts: 1042
Location: Richmond, Virginia
|
|
|
|
| You can knock "proprietary stuff" all you want, and I'm certainly in favor of standards and cooperation, but without proprietary stuff, we'd be living in either the stone age or a communist society, assuming they're different. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
I'm with Phil - proprietary is not a 4-letter word. |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
Proprietary formats have advanced us significantly. But they have brought us to the point we are today, lack of interoperation and manual conversions to use the data on our system of choice.
...You make communisim sound like a bad thing. |
|
| Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006
Posts: 1042
Location: Richmond, Virginia
|
|
|
|
| Not hard to do. |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
Anyway back to the point at hand.
The code I presented was robust enough that it will handle anywhere from a 1x1 spreadsheet to the maximum available size in Excel. It will pipe delimit the columns and put each row out to its own line in a text file. Once it gets down to the mainframe you could unstring it using |. I think this is way better than the comma because like the OP said, there are commas in his actual data. Pipes are far less likely to occur in the data being received in the Excel file.
Another alternate is to export the data from Excel as XML document and then parse that with Cobol. |
|
| Back to top |
|
|
|
|
