Spreadsheet conversion

[Gali]

Hey Programmers!

 

I haven't done any programming in several years, so I'm definitely rusty. I have to figure out how to convert an excel spreadsheet into mainframe data - specifically Passport - by macro or, if easier, another method.

 

As long as I can get a starting point I may be able to figure it out along the way. Unfortunately I only have experience in old-school HTML and some C#, so the knowledge I have is limited to begin with.

 

If anyone could help, I'd be most grateful.

 

Thanks y'all!

[Nuluvius]

You are going to have to be a lot more specific than that...

15 minutes ago, Gali said:

...convert an excel spreadsheet into mainframe data - specifically Passport...

Examples of the protocol/API and/or links? What are you hoping to accomplish exactly?

[Gali]

I have a large amount of data prepared in spreadsheets that I need to input into a mainframe. The task is time-intensive and I'm trying to create a speed-route to having this manual process automated.

 

I've prepped a test spreadsheet to work with my sandbox when I have the right program to move the data over.

[Nuluvius]

51 minutes ago, Gali said:

I have a large amount of data prepared in spreadsheets that I need to input into a mainframe.

Again that doesn't really elaborate very much...

51 minutes ago, Gali said:

The task is time-intensive and I'm trying to create a speed-route to having this manual process automated.

 

I've prepped a test spreadsheet to work with my sandbox when I have the right program to move the data over.

So if we are talking about Excel spreadsheets here then you have three options:

1. Write a macro
2. Use the COM automation interface, here's an overview
3. Work with the .xls/.xlsx zip file itself - there is at least one good library for doing this (it's not free)

If I were to approach this then I certainly would not be choosing option #1. #2 is fine but your solution will rely on the presence of Excel on it's target system as a prerequisite. #3 is better but potentially more time consuming/expensive.

 

In any event this kind of problem lends itself to a multi threaded solution. Depending on the server architecture you may effectively load and process multiple data files at once/in batch.

[Gali]

7 minutes ago, Nuluvius said:

Again that doesn't really elaborate very much...

So if we are talking about Excel spreadsheets here you have three options:

1. Write a macro
2. Use the COM automation interface, here's an overview
3. Work with the .xls/.xlsx zip file itself - there is at least one good library for doing this (it's not free)

If I were to approach this then I certainly would not be choosing option #1. #2 is fine but your solution will rely on the presence of Excel on it's target system as a prerequisite. #3 is better but potentially more time consuming/expensive.

 

In any event this kind of problem lends itself to a multi threaded solution. Depending on the server architecture you may effectively load and process multiple data files at once/in batch.

Thank you for the help Nuluvius. I know I didn't give much to work off of, but I don't have a whole lot to start with. I'll check out option #2 then go into #3.

 

What's the issue with writing a macro to do this conversion?

[Nuluvius]

18 minutes ago, Gali said:

What's the issue with writing a macro to do this conversion?

Wile you could accomplish it that way it wouldn't be nearly as clean or as easy in my opinion; see here for the discussion about multi threading in VBA and its various workarounds.

 

It would be far better to write this as a separate C# library thus having access to all of the latest conveniences of .net 4.6.n. Specifically of benefit in your case: TPL, async/await and LINQ. Moreover if you define a clean public interface/API it will be possible for you to then write tests (TDD) and pull in or integrate with other libraries/modules thereby keeping your solution well encapsulated, reusable and extensible - all of which are good software engineering principals.

[Gali]

What about converting it into a CSV to be called by UNIX (BASH)? Could that work?

 

The conversion to CSV from .xlsx could be automated, so in theory the whole process would be pretty quick.

[Nuluvius]

1 minute ago, Gali said:

What about converting it into a CSV to be called by UNIX (BASH)? Could that work?

 

The conversion to CSV from .xlsx could be automated, so in theory the whole process would be pretty quick.

Sure that way all you are dealing with is effectively text.

[Gali]

There's no formatting since it's in a mainframe (Can't wait to get rid of the bloody things), pure text is preferred in this case.

 

I just don't know the process(es) needed to do the conversions from Excel into Mainframe. I can manually enter and save and manupulate the data in the mainframe, but I'm going for automation to conserve on my own labor and time .
 

One of the support team members from my workplace uses Informatica as an ETL to load the Excel files into Database Tables. Is this plausible as well? Pull the data into the mainframe from Access?

 

This seems to be the definition of "More than 1 way to skin a cat"...

[Nuluvius]

39 minutes ago, Gali said:

There's no formatting since it's in a mainframe (Can't wait to get rid of the bloody things), pure text is preferred in this case.

 

I just don't know the process(es) needed to do the conversions from Excel into Mainframe. I can manually enter and save and manupulate the data in the mainframe, but I'm going for automation to conserve on my own labor and time .
 

One of the support team members from my workplace uses Informatica as an ETL to load the Excel files into Database Tables. Is this plausible as well? Pull the data into the mainframe from Access?

 

This seems to be the definition of "More than 1 way to skin a cat"...

I'm afraid that you are wondering off into the land of ambiguity once more... What you need to do is to isolate exactly what is available to you as an endpoint and provide an example of that API. Where does it live and what does it require in what kind of format and sequence. You have thus far eluded to text as a starting point.