How to build a temp table which reflects any Spreadsheet

[polar_action]

Hi Progress Talkers,

I'm dealing with a heap of spreadsheets (just flat files using CSVs).

All have differing headings, number of columns and rows.

What I'm looking to do is import the header line (which contains the column's header name) and use by creating and adding them as fields to a dynamic temp-table.

Then I'm wanting to use that temp-table definition to populate, and thereafter manipulate the temp-table / spreadsheet contents.

Anyone know of how to do this? I had a good search and tried a few things but I'm new to dynamic temp-tables.

Many thanks

Dave Walker

 

[Stefan]

Yes you can do this, but there is one major snag. Although you can create your temp-tables dynamically, there is no dynamic import method - so you will have to roll your own - dealing with quoting / line breaks etc.

The basic dynamic temp-table looks like this:

DEFINE VARIABLE htt  AS HANDLE      NO-UNDO.
DEFINE VARIABLE hb   AS HANDLE      NO-UNDO.

CREATE TEMP-TABLE htt.
htt:ADD-NEW-FIELD( "cc":U, "CHARACTER":U ).
htt:TEMP-TABLE-PREPARE( "myfirsttt":U ).

hb = htt:DEFAULT-BUFFER-HANDLE.

hb:BUFFER-CREATE().
hb::cc = "myfirstfield":U.

DELETE OBJECT htt.

Not sure what your data is, but you will also probably need to be able to determine the data type of the column.

 

[GregTomkins]

I have done this kind of thing, I don't have code for you, but here are a couple of gotchas:

1. Column labels in the CSV with problematic syntax, such as embedded spaces.

2. Arrays.

3. Data type problems. It's easy to assume that column always contains a numeric ... then, whoops, along comes something else.

4. Also, importing dates into Excel can be a nightmare as it wants to make assumptions that may be totally wrong, and there is sometimes no easy way to tell Excel otherwise. I'm not sure if the reverse problem happens, but it might. I'm also not sure about other spreadsheet apps. Wait a minute, ARE there other spreadsheet apps?