Import Excel 2003,2007, 2010 directly into Progress

I have Progress v9.1E running a custom application.
I have had much luck exporting into Excel and PDF files.

Now, I'd like to import an Excel Spreadsheet into some temp table(s).

I have found how to open and display an existing Excel file.
I need to know how to step through the cells and assign them to the table/fields so I can work with the imported data in Progress.

I estimate that I will need to create some data ahead of time, and I can do that.

Let's say I have a temptable called tt-rfqitemd with the fields, item, description, qty, delivery, target-price, note.
Let's say I have a spreadsheet with the same fields.

1: Is it possible to run through the columns until I find the field named "item" for example and import it into the tt-rfqitemd.item field? Same for the balance fields, so I don't need to adjust the placement of the fields on the Excel?
2: What would the best commands be for the actual importing? Are there special considerations for the different datatypes?

I'd really appreciate the ideas of an expert. I am sure that once you start doing it all of the time, it will be as intuitive as exporting into Excel but at this point, I can only import csv files.

Thanks!!

JLovegren said:

I have Progress v9.1E running a custom application.
I have had much luck exporting into Excel and PDF files.

Now, I'd like to import an Excel Spreadsheet into some temp table(s).

I have found how to open and display an existing Excel file.
I need to know how to step through the cells and assign them to the table/fields so I can work with the imported data in Progress.

I estimate that I will need to create some data ahead of time, and I can do that.

Let's say I have a temptable called tt-rfqitemd with the fields, item, description, qty, delivery, target-price, note.

Click to expand...

What are the temp-table fields' data types? And which ones are you importing from Excel?

Let's say I have a spreadsheet with the same fields.

Click to expand...

How exactly do you define "field" in the context of a worksheet? And don't say "it's a cell".

1: Is it possible to run through the columns until I find the field named "item" for example and import it into the tt-rfqitemd.item field?

Click to expand...

It's possible to do pretty much anything, given a sufficiently lucid and detailed functional requirement, and a reasonably predictable data source. Again, "until I find the field named item" needs clarification.

Same for the balance fields, so I don't need to adjust the placement of the fields on the Excel?

Click to expand...

Does this mean you don't know the format of the worksheet, or the placement of the data in it, at design time? Are you receiving this workbook from an outside source, with a design you can't influence or anticipate? Are you saying that the "Balance" values could be in column A in one worksheet and in column J in a different worksheet?

2: What would the best commands be for the actual importing?

Click to expand...

That could be something as simple as "assign cVariable = <com-handle>:RANGE( <some range> ):VALUE." If your worksheet contains contiguous tabular data, with each "field" in a separate column and each line item in a separate row, then you could iterate over it in a repeat loop using the Excel OFFSET function (which returns a RANGE object) with index variables to change your selections of row and column.
You are also going to have to think about how to exit your import loop. How do you know when you have reached the end of the data to be imported? Is it when you hit the first blank row after a contiguous range of data? Is it when you hit row 1048576? Is it when you hit magic value "ZZZ" in column "foo"?

Are there special considerations for the different datatypes?

Click to expand...

I don't think you would want to bring data directly into your temp-table, as it is not sanitized. If you have an integer or decimal field, there is no guarantee that the data coming from a given cell in a worksheet is numeric, as worksheet cells are not strongly-typed. You could roll your own library of validation functions and run your imported values through them to ensure they conform to given ABL data types, and then output from those functions into your temp-table fields. That part is not particular to working with Excel. You would do the same thing if your data source was an unstructured text file or a socket.

Dear Rob:
Thank you for your contribution.
1. The temp-table fields will be as follows:
item x(30)
description x(30)
qty >>9
delivery MM/DD/YYYY
target-price decimal
note x(40)
--------------------------------
2. In the context of my spreadsheet, a field is an Excel Column, while a record is an Excel Row.
--------------------------------
3. For importing a CSV, I use the below code. So, I am thinking about the Excel version of this technique.
FileLoop:
REPEAT:
IMPORT STREAM Infile DELIMITER ","
v-input
.
v-records = v-records + 1.
IF v-records = 1 THEN DO:
DO v-column = 1 TO 15:
CASE v-input[v-column]:
WHEN "Item" THEN v-item-col = v-column.
WHEN "Description" THEN v-desc-col = v-column.
WHEN "Qty" THEN v-qty-col = v-column.
WHEN "Delivery" THEN v-del-col = v-column.
WHEN "Target-price" THEN v-target-col = v-column.
WHEN "Note" THEN v-note-col = v-column.
END CASE.
END.
----------------------------------
While I can anticipate the file's format I cannot be sure about the placement of fields. My parent company often throws in extra fields for their own purposes and with files coming in from various departments, all I know for sure is that the basic minimum information needed to price an item, the placement could be jumbled.
----------------------------------
As far as... "That could be something as simple as "assign cVariable = <com-handle>:RANGE" -- I like this approach. I do need guidance.

The end of the loop would be encountering empty row(s). If I could set it at 1 or 2 rows, that would work out.
-----------------------------
Finally, your recommendation to "condition" the data prior to import into the temp-table sounds like a good idea. I was thinking about importing everything as character into the tt and then conditioning/converting prior to appending into our live pricing table(s). Would this be best done as variables?

- I want to thank you for your insights!

You will find that reading data from Excel using an API is very slow. We import from xls files, but we use the API to save each sheet as csv and import the csv. It's much, much quicker.

JLovegren said:

While I can anticipate the file's format I cannot be sure about the placement of fields. My parent company often throws in extra fields for their own purposes and with files coming in from various departments, all I know for sure is that the basic minimum information needed to price an item, the placement could be jumbled.

Click to expand...

So the question is, how will you know (i.e. how will your ABL code know) which column is which, if their order or placement is not consistent?

Would you have something like this?

And so you know not to pull data from columns B and F because of their "headers", i.e. the values in row 1? And you know which columns are supposed to correspond to which temp-table fields because the column headers have certain exact "magic" values (item, description, etc.)? And you can guarantee that the "extra" column headers will never duplicate these values?

Rob Fitzpatrick said:

So the question is, how will you know (i.e. how will your ABL code know) which column is which, if their order or placement is not consistent?

Would you have something like this?

item	foo	description	quantity	delivery	bar	target price	note
a	x	item a	1	03/22/2012	blah	1.99	hello
b	y	item b	2	03/22/2012	blah	2.99	world
c	z	item c	3	03/22/2012	blah	3.99

And so you know not to pull data from columns B and F because of their "headers", i.e. the values in row 1? And you know which columns are supposed to correspond to which temp-table fields because the column headers have certain exact "magic" values (item, description, etc.)? And you can guarantee that the "extra" column headers will never duplicate these values?

Click to expand...

A: Yes. I expct to receive data very much like the above. In most cases, the columns B & F contain Japanese - I need to ignore them. The "magic" column heading values are pretty consistent and I have never seen them duplicated.

The typical spreadsheet includes 20 ~ 50 records. If requied data is missing, I'll report an error and suggest the user type the data into our application - which is pretty much how it is done now.

Thanks!

I'm looking at this a bit in my spare time.

In the meantime if you are going to take this approach it would benefit you to learn some VBA, and to get acquainted with the Excel Object Model. It will help you to write COM code in ABL. A simple way to get started is to use the Macro Recorder in Excel, and then make various changes in the application and look at the generated code. You can open the VBA IDE (Alt+F11) while you're doing this, and you will see the code module being updated in real time as you make changes in Excel. The generated code isn't necessarily what an experienced VBA coder would write, but it's still a useful exercise .

The object model is pretty well documented, although the helpfulness of the online help in Excel varies by version, and by whether you use the online or offline help. Here is the Excel 2010 Developer Reference; the earlier versions are online as well. Also, in the IDE in Excel you can hit F2 for the Object Viewer to get more info and help on objects. And for a Progress-centric view, you can use the Progress COM Object Viewer (proobjvw.exe). Be advised that Microsoft and Progress don't always agree on interfaces, so you will be pulling out some amount of hair figuring things out. For example, proobjvw.exe says that the Address property of the Range object has five arguments, three mandatory and two optional. Whereas the MS reference says it has five arguments, all optional. And the 4GL compiler doesn't catch this at compile time, so if you reference it with no arguments (as MS says you can, and as Excel lets you), the AVM will throw a run-time error. Fun times.

Rob Fitzpatrick said:

I'm looking at this a bit in my spare time.

In the meantime if you are going to take this approach it would benefit you to learn some VBA, and to get acquainted with the Excel Object Model. It will help you to write COM code in ABL. A simple way to get started is to use the Macro Recorder in Excel, and then make various changes in the application and look at the generated code. You can open the VBA IDE (Alt+F11) while you're doing this, and you will see the code module being updated in real time as you make changes in Excel. The generated code isn't necessarily what an experienced VBA coder would write, but it's still a useful exercise .

The object model is pretty well documented, although the helpfulness of the online help in Excel varies by version, and by whether you use the online or offline help. Here is the Excel 2010 Developer Reference; the earlier versions are online as well. Also, in the IDE in Excel you can hit F2 for the Object Viewer to get more info and help on objects. And for a Progress-centric view, you can use the Progress COM Object Viewer (proobjvw.exe). Be advised that Microsoft and Progress don't always agree on interfaces, so you will be pulling out some amount of hair figuring things out. For example, proobjvw.exe says that the Address property of the Range object has five arguments, three mandatory and two optional. Whereas the MS reference says it has five arguments, all optional. And the 4GL compiler doesn't catch this at compile time, so if you reference it with no arguments (as MS says you can, and as Excel lets you), the AVM will throw a run-time error. Fun times.

Click to expand...

-------------
Thank you, Rob.
I understand your comments, I am pretty proficient with Exporting to Excel files and I know there are some qwerks not to mention the perfomance issues related to navigating/populating a spreadsheet cell-at-a-time. Nevertheless, importing from Excel is foreign to me.

As far as... "That could be something as simple as "assign cVariable = <com-handle>:RANGE" -- I like this approach. I do need guidance.

Click to expand...

Please refer to the attached. Please do not take the attached as an object lesson in how to write code. I'm not a developer. I know just enough ABL to be dangerous. This code is not elegant, fast, OO, error-proof, thoroughly debugged, or above all, production-ready. But it may give you an idea of how to accomplish what you want, as described in your earlier posts. It seems to work. Give it a try. YMMV.

Also, I have left the sanitizing functions discussed earlier and general error-handling as an exercise for you. The sample code loads data straight into the temp-table. Obviously, don't do that. The zip file also contains a small sample spreadsheet that I tested with.

Rob Fitzpatrick said:

Please refer to the attached. Please do not take the attached as an object lesson in how to write code. I'm not a developer. I know just enough ABL to be dangerous. This code is not elegant, fast, OO, error-proof, thoroughly debugged, or above all, production-ready. But it may give you an idea of how to accomplish what you want, as described in your earlier posts. It seems to work. Give it a try. YMMV.

Also, I have left the sanitizing functions discussed earlier and general error-handling as an exercise for you. The sample code loads data straight into the temp-table. Obviously, don't do that. The zip file also contains a small sample spreadsheet that I tested with.

Click to expand...

THANK YOU. I will give it a try and let you know how it goes. I really do appreciate your effort!