Hi,

I realized now that columns with ListX are exported to CSV with the description instead of the key.
This is great but, is there any option to export the code instead of the description? :rolleyes:

I've also noticed that, Date fields displayed with YYYY-MM-DD format in the XTREE turn into DD/MM/YYYY in the CSV.
Is this conversion applied in A-Shell or is it the Excel itself?

Hi Jorge,

We could easily add a switch to cause the export operation to output the ListX code rather than the description. I'm not sure the best way to express it though - perhaps another advanced coldef option like:
ExportOptions=

where the initial options would be something like "Code", e.g.

ExportOptions=Code
or
ExportOptions=Descr

with Descr being the default.

The only reason not to do something like this is that it would probably only encourage you to come up with a bunch of additional export options! :rolleyes:

As for the date format, currently it doesn't appear to me to do any translation of the text that appears in the cell when exporting, so my guess is that Excel is reformatting it. (But you could confirm that just by using Notepad or VUE on the CSV.) If that doesn't seem to be the case, then perhaps we already need another ExportOption!

At the risk of encouraging this type of thing, I've posted a beta copy with the feature as described above if you want to play with it:

ash-6.5.1609.0-w32c-upd.zip

That was a perfect one, worked like a charm!
Thank you very much my friend.

hahahaha

You're right, I couldn't suggest a better one other than ExportOptions=, it fits perfectly in my thoughts, like:
ExportOptions=No , don't export the column
ExportOptions=All, export code=descr
ExportOptions=Code[x, y], export substring of Code
ExportOptions=Descr[x, y], export substring of Descr

On the fly I can't think in any more options

PS: By the way, you're right, Excel is doing the conversation for the Dates, thanks for the tip

That was a mercifully short list; either you're not trying very hard or you're just going easy, considering that the work day is hopefully over by now, even in Brasil.

At some point (not tonight!) we may want to consider an option to export directly to XLS instead of CSV, in which case probably we'll have no difficulty thinking of additional ExportOptions, starting with instructions to Excel as to how to format dates.

Again, you were right, I was finishing my shift

Hi Jack,

I was quite for half a year on this topic :p
Without any pressure, can you bring it upper on my wish list? :rolleyes:

I have a column with a combo list with a 4 digits code but I have to export only the last 2.
The perfect solution would be something like Exportoptions=code[3,4] but, maybe, an easier workaround would be:
1. add an additional hidden column with that part of the code
2. in the visible combo column set Exportoptions=No
3. the hidden columns are not exported, and well, but would be exported if explicitly set Exportoptions=Yes, what I could apply to my hidden column. This will be also useful when a form to export have a lot of columns that are not filled but must be displayed in XTREE only to be exported with fixed values or empty.

As you can see, nothing new, I'm just recalling and simplifying my predictions from the first request.

I guess your list has been in wish-list-purgatory long enough. :rolleyes:

But before we go off and create an entire ExportOptions scripting language, let's make sure we think this through.

The ExportOptions=Yes (force export of hidden column) and ExportOptions=No (disable export of a visible column) make reasonable sense. As does the ExportOptions=All )(for code=description). But I'm not 100% convinced on the substrings.

The fact that you want to have a visible column in the tree displaying something like ABCD, but you only want to export AB or CD, introduces some questions, in my mind, about just what kind of secret designs or hidden agenda you may have! If you want that kind of thing now, how do we know that tomorrow you won't want to export ABCD as [ABCD] or abcd or A/B/C/D or perhaps concatenate it with the next column? The substring syntax would fall way short of handing such complex conversions.

That may be an argument in favor of your workaround involving the hidden column. Or it may be an argument for some more flexible mask syntax. But our existing USING MASK isn't able to handle even the simple substring case.

Maybe some kind of regular expression replacement pattern, like you can use in APN search/replace, might make sense? But that would require two arguments, one for the search pattern and one for the replacement pattern. And because regular expressions can contain just about any character (fortunately tilde is not needed for any critical purpose), we'd probably have to separate ExportOptions into two parts:

ExportRegexSearch=
ExportRegexReplace=

In that case, ExportOptions=Code[3,4] would be expressed as...

ExportRegexSearch=..(..).*=~ExportRegexReplace=\1

... which unfortunately is a lot more complicated than ExportOptions=CODE[3,4].

But, you could also do things like converting ABCD to A-B-C/D ...

ExportRegexSearch=(.)(.)(.)(.)~ExportRegexReplace=\1-\2-\3/\4

Of course there are several reasons not to consider such a crazy idea:

1. It would seem somehow out-of-balance to bury such a powerful feature in such an obscure little nook. (Side note: there is nothing stopping anyone from creating an SOSFUNC: function for regex search and replace; that's the way you would do it if you wanted this kind of capability in an application.)

2. Even with all that power it still would handle simple tasks like folding or concatenating columns.

3. You might nevertheless give in to temptation and try to write an entire application using just XTREE Export options.

4. Then of course if you give a mouse a cookie... :rolleyes:

For example, how long before you'd ask for ExportRowPattern to specify the order of the columns, with possible concatenation, something like:

ExportRowPattern=$1,$4,$5-$6,ucs($9)

(to export just columns 1, 4, 5-6 together, and 9 folded to lower case).

But that would call into question the existing assumption that the user wants to export the columns in the current display order rather than the underlying definition order. Not to mention raising the question of which column numbering (physical or display) we are talking about in the ExportRowPattern. (Maybe we need an ExportOptions=Display vs Physical for the zero column?)

Going back to that side note under point 1 gives me another idea. How about:

ExportOptions=SBX:XXX

where the specified SBX was coded as a function that took a single argument (the column contents) and returned the string as you wanted to export it? (Talk about using a sledge hammer to swat a fly!)

Well, it's been nice thinking about all this, but now I've used up all my time and mental energy and need to move on to bug fixing! In the meantime: your move!

hahahaha!!! I love your brainstorms
But the curious here was that, today I choose this topic to entertain me during my jogging and, among other ideas, regular expressions crossed my mind
Yes, you're right, if we keep going deeper on this, we will end creating an Export'COLDEF with all the changes for each column in COLDEF to export their content and, I think, that would be a waste of resources and will never worth the time spent.
I will not even list here my "running thoughts", they were precious to help me forget the 31º Celsius but, just that.

The goal here is to improve the "Export CSV" feature with consensual basic options to increase the probability to use this embedded (great) feature.
If we make it too powerful but necessarily more complicated, for sure, it will be time to write an export'my'xtree() procedure instead of use the embedded one.

So, besides very tempting to start discussing the syntax for the new ExportOptions options, I'm convinced that we don't need more than add the ability to export hidden columns and not export visible ones.
In fact, if that had came first, we even wouldn't need the existing ones to export code or description because we can configure whatever we need on hidden columns, in extreme cases, we can have a duplicate of the visible columns in hidden columns and just export these.

I think this is the key, control the export for hidden and visible columns, with that, the sky is the limit.

Any way, allow me to add a simple one more option, ExportOptions=NoROW :p
When defined in a column and the value in a row is 1, don't export those rows.
Again, I'm trying to make it simple and assuming the risk for a very limiting rule, I'm thinking in a checkbox column to exclude rows by the user or by the program, obviously I can think on the opposite, where the column is to include instead of exclude or, complicating more, exclude the rows where the content of a column is empty or have some value, and go on, and go on.

In resume, three requests if everybody agrees:
1. ExportOptions=YES on hidden columns
2. ExportOptions=NO on visible columns
3. ExportOptions=NoROW to ignore rows with column value=1

And, thanks already for all the time spent on this, just discussing the topic

I had been looking forward to the day we go jogging together. But now, I'm not sure sure!
(I can imagine it will go like this: first you get me into an oxygen-deprived state, then you start talking about coldefs and regular expressions and who knows what else, and just before keeling over I end up agreeing to support entire AlphaBASIC programs embedded into a single cell!) :rolleyes:

But since I'm still fully-oxygenated in the comfort of my office, I guess I can keep negotiating.

Let's start with the NoRow idea. I like the idea of being able to filter the rows based on a checkbox column, but probably we should allow a column number (other than assuming the first column), and allow an option of including or excluding the checked rows. Perhaps something like:

ExportFilter=5# (export if column 5 # 0)
ExportFilter=3! (export if column 3 = 0)

(Maybe in the future we might expand the syntax to allow SQL, but for now this seems adequate.)

ExportOptions=No seems uncontroversial.

But:

ExportOptions=Yes could be a problem, because it only applies to hidden columns, which raises the question of the column order. As it stands, the export column order is equal to the display order (not the physical/defined order). But where does a hidden column appear in the display order?

That makes me think that perhaps we need another variation:

ExportColumns=3,5,1,7,10

If specified, it would determine the export order based on the physical/defined columns, without regard to visibility. And any columns not included in the list would not be included in the export.

Such a directive would eliminate the need for the Yes and No options, except for the default case of exporting based on the display order, because there is no way for the programmer to know that order in advance unless column reordering is disabled, in which the display order will always be the same as the physical/defined order.

But, maybe we could add a flag to indicate whether the column list was intended to be physical (in which case it also determined the order), or based on the display (in which case it only determined which physical columns would be included in the export, but not their order). Something like:

ExportColumns=PHY:9,2,13,7,4
or
ExportColumns=DIS:2,4,7,9,13

The first case (PHY) would determine the columns to include/exclude as well as the order (based on the physical column order). The second case (DIS) would only determine which columns to export (replacing the ExportOptions=YES/NO) but not the order (which would be determined by the current display order).

Excellent idea, put everything in a cell, that would become our milliDocker or milliContainer
(just trying to catch Herman's attention, I'm sure he has some alarm that is triggered when something with "milli" is mentioned on BBS )
As for jogging together, that will be a pleasure my friend but, the reason why I always go alone it's precisely to not talk, otherwise, I'm sure the non-oxygenated guy will be me, I still get surprised with the "talking able" runners who cross my way.

Now it's my turn, again, and start to clarify that my suggested ExportOptions=NoRow is not for "column 1", it can be defined in any column but restricted to consider value=1 in that column to exclude the row.
Your ExportFilter is more versatile regarding the value to filter considering that you allow 0 or non-zero but, in my opinion, more susceptible to mistakes if changes in coldef change the columns order.
Maybe we can join both, ExportOptions=NoRowIF# and ExportOptions=NoRowIF! ?

Regarding the column order, now I caught the issue, I didn't realize before that export follows the display order, which is very important and thank you for bring this to the discussion.

But, again, I insist in simplification

I think that, force to follow the physical column order while exporting is only wanted if the resulting file must respect a structure to import data in another system or to fill a form, otherwise what we want is to export according to the display order arranged by the user.
My present case is the former so, your warning was precious and I've already adjusted the program removing the XTF_REORD to not allow the user to move the columns.

Anyway, if we still want to allow users to play with the columns but export by the physical order, I suggest a global flag in row 0 like ExportOptions=Physical.

I'm not disregarding your ExportColumns option, it brings versatility but if complementary to the basic YES (for hidden) and NO (for visible).
Using column numbers always have the danger to forget to adjust when changes to the columns influence their order.

Thanks

I'm not sure who I should be more worried about - Herman, whose milli-alarm is probably keeping him up all night, or his office mates, who aren't going to have any idea what he's talking about when he comes in and demands all his SBXs be put into millicells.

As for simplification, after getting a little more oxygenation, I came around to your conclusion that, while flexible, having to keep track of the physical column numbers is not very programmer-friendly, compared to inserting the options with the individual column definitions.

So I accept:

ExportOptions=No
ExportOptions=NoRowIf# (col # 0)
ExportOptions=NoRowIf! (col = 0)

BUT: I'm still not sure what to do with ExportOptions=Yes unless we are using physical column order, since hidden columns aren't part of the display order sequence. Perhaps if the option was used without ExportOptions=PhyOrder, then we could just put any such forced-to-be-exported column at the end of the row?

So, just to clarify the non-closed options:

Hidden columns are exported if ExportOptions=Yes.

The order to export the columns is by default the display order, unless ExportOptions=PhyOrder is defined in the row zero of coldef making the export order to be the physical order.

If display order is in place, all the hidden columns set with ExportOptions=Yes are exported to the right of all visible columns.


Do we have a deal?

Off topic, somewhere above, you mentioned the possibility to export to XLS, if that's reasonable to consider for real, be my guest to add it when possible.


Many thanks for the healthy and oxygenated discussion and have a wonderful weekend.

At the risk of possibly not thinking clearly due to being under-caffeinated: it's a deal!

As for the direct export to XLS, that project still needs to ferment for awhile. The big issue there is the fact that the XLS interface is quite large (involves over a hundred A-ShellBASIC functions). It doesn't seem like a good idea to re-implement calls to all of those functions internally within XTREE, particularly when much of the logic is already contained in, or at least similar to the CSV2XL.SBX routine. So I'm thinking it would probably make more sense to implement the XTREE-to-XLS operation within a single SBX call. Besides simplifying the interface, it would allow most of the details to be handled in BASIC outside of the A-Shell executable, eliminating the need to for A-Shell updates for each refinement to the export-to-XLS logic. But for that to be successful, I really need to figure out how to package up all the details about the tree, not just the data but the structure and all of the row, column, and individual cell attributes.

It might even make sense to implement it entirely outside of XTREE, i.e. just give you an exitcode and rely on some fantastic SBX that takes the same parameters as XTREE but instead just does the export conversion. (Some downsides of this approach, aside from needing to essentially rewrite much of the code inside XTREE that currently processes the parameters, would be that it couldn't easily capture the current visual state of the tree after the user has manipulated it.)

Either way, it's a big project.

Depending on what the real objective is, it might make sense to implement a much simpler "lite" version which essentially just does the initial export-to-CSV and then calls CSV2XL.SBX to convert it to XLS. That wouldn't be able to capture all of the cell-level attributes like colors, fonts, etc., but it would give you an XLS (eliminating need for the user to do the import operation). And, we might even be able to do a little better than that by using some of the existing CSV2XL //XL directives to highlight the headers and perhaps emulate some of the column attributes.

But first let's return to our sheep and get the ExportOptions deal implemented!

Ok, I think most or all of the above is ready for BETA testing. (Please review the notes for details) ...

ash65notes.txt
ash-6.5.1626.0-el7-upd.tz
ash-6.5.1626.0-el5-upd.tz
ash-6.5.1626.0-w32-upd.zip
ash-6.5.1626.0-w32c-upd.zip
compil-6.5.850-w32.zip

Note one deviation from the above agreement is that in the case of exporting a hidden column, in display order, rather than appearing after all the visible columns, the hidden columns will appear in their physical order within the coldef. So for example, if column 3 was hidden but forced to be exported, it would show up as the third column in the export, regardless of any reordering of the visible columns.

Also note that the Export to XLS option is now implemented using the CSV2XL technique, which turns out to be fairly simple but also reasonably flexible, although it's not yet able to handle things like automatically adjusting font and color attributes to match the tree.

Hi Jack,
Let's start with the excellent news; everything is working perfectly and according to the deal, excellent one, by the way

Now the frustration; one of the customers who should benefit with this is the same which has the w2003 server ASHW32 launch error in windows server 2003 and, because of that, is stuck on the 6.4.1554.1 version

There are possible workarounds on my side, like launch this option with an updated ashw32.exe but, I'm waking this topic because the last comment was a "maybe" that was left in suspension regarding a possible workaround to the W2003 limitation.
So, before I start retailing my menu with options running different A-Shell versions and after your excellent work, apologize for my indecency to ask if you intend to investigate this in a short term, let's say days, couple weeks? :rolleyes:

Thanks

Ugh. :rolleyes: It seems a bit crazy that it would be so hard to resurrect XP- and WS2003- compatibility, but despite numerous efforts, I've so far been unsuccessful. I tried the patch that was referenced by the linked post, as well as the supposed recipe for targeting an older toolset with from the VS2013 development environment (which is itself starting to seem old). But for reasons I haven't been able to fathom, the result is an exe that just crashes under W2003.

There are still some things to try, like recreating my VS2010 environment from a year or two ago on a separate machine, but I'm a little concerned that even if I can make it work, it will require considerable effort to maintain the parallel environment, multiplying the amount of work it takes to release any updates. And given that Microsoft officially ended support for WS2003 in July 2015, I don't expect I'll be able to get much help with the project from them.

I hate to say this, but maybe it's time to consider charging the customer for the extra difficulty of maintaining an obsolete environment. Would that WS2003 server still seem so attractive if it cost an extra $1000/yr to get software updates? Maybe the upgrade to a new server would start to look more attractive?

Hi Jack,
Definitely the ball is on their side, I was just confirming this is a closed issue and, it is, considering all the effort to try to make it work that's completely unreasonable.

Thank you for take a look once more.

I may have spoken/written too soon. After digging around in the archives and applying more than a smidgen of voodoo, I may have succeeded in creating a WS2003-compatible version of the latest 6.4.1556.8 A-Shell/Windows. If you want to verify that, here's the link:

ash-6.4.1556.8-w32cx-upd.zip

Warnings/Notes:

- The "x" in "w32cx" is meant to be the XP (and WS2003) compatibility indicator.

- The exe is actually called ashw32cx_15568.exe just to avoid confusion with the "official" version.

- You can go ahead and rename it to ashw32.exe if you like. You will still be able to identify it via the exe properties, which show "(A-Shell (X/XP/WS2003 version)" for the File Description.

- I don't recommend using this version for any purpose other than "desperate retrofitting of obsolete systems", as I'm not really sure what all the ramifications and differences are between it and the standard version.

- I realize that this still doesn't attain your objective, since it is 6.4 and not 6.5 and thus is missing the latest XTREE enhancements which started this quest. (6.5 wasn't yet in existence at the time of the archive that I built this from, so I decided to keep it simple just to test the concept. If it seems viable, I can migrate it to 6.5.)

- Even if this works, it should be viewed as a temporary measure, since maintaining this version is not going to be very convenient. (Perhaps Ty will be able to point to an entry in the price list for "special builds".)

Here's the 6.5 version:

ash-6.5.1626.3-w32cx-upd.zip
ash65notes.txt

With that you spoiled me and I spoil my customers :p
It's in place and, by now, working like a charm with the convenient warning that it will not last for long so, they need to start thinking to move ahead on their server OS.

Thank you very much

Can I Hi-jack this thread with a simple export question?

I am adding $EXPTSV+ to an existing grid which defaults a named file called ashgrid.csv. If I just save this file, it does open Excel and grab the file, but is bypassing the formatting question where it uses the TAB as a delimiter and the columns are not breaking at the tabs. Conversely, if I save the file as ashgrid.tab and open Excel manually it will detect that there are embedded tabs and continues with the parse options.

Thanks for any assistance!

Franks that�s one reason I was interested in CSV2XL as Excel is OK auto opening CSV for the majority from XTree but at times not very clever with dates, values and all...

I didn't even realize that Excel expected tab-delimited files to have a .tab extension! (I had been using LibreOffice for testing, and it seems to happily figure out the delimiter and bring up the wizard dialog in any case.)

It may well be that using .TAB as the default extension for $EXPTSV would make more sense than .CSV, but I think it may require a little more research, as there are multiple reasons not to rely on Excel as "the standard". (One is that there are so many versions floating around, and I'm not sure it was always this way. And another is that there are many other spreadsheet programs floating around.) In any case, I'll review it.

BUT: even ignoring the new $EXPCSV2XL option, is there some reason not to use $EXPCSV instead of $EXPTSV? (Comma delimited seems like a more widely respected standard than TAB delimited.)

Sorry didn't mean to muddy the waters...

.TAB extension was just what i called it to keep track. It by no means is a standard to Excel.

Comma delimted won't work for me here because i have data with inherent commas ($values). I don't have to use the + option, just thought it would be cool to use the feature that opens the program automatically with the input file. I think i may have a windows association with .CSV so perhaps Excel thinks it's already "native". Let me check on that... BRB.

Ok here is what i discovered:

First a Disclaimer - i know just enough about Excel to get me in trouble... and probably less.

It's not the association, for if I remove the association the ashell won't open it at all. Or.. if i save the file as ashgrid.txt ASHELL opens it with the default associated app of Notepad. This makes sense.

Back to the .CSV - it seems to me in my limited knowledge that Excel sees the .CSV and assumes its comma delimted when it launches. Even if i open the .CSV file within Excel, it bypasses the "Import Wizard". Now if i rename it to a .TXT for example, then open it within Excel, it does trigger the Import wizard.

Ok based on all of this... is it possible for me to change the default name of "ashgrid.csv"? I can instruct the user to override this on the Save dialog, but nobody will remember this. Would be great to have xtree export these rows for me, but if i cannot easily create a tab delimited file then i will have to hand-code it.

(not sure i want to play too much with the CSV2XL option here... these are simple outputs for users that will want to change the layout to suit themselves)

WAIT! Values with embedded commas are NOT an obstacle to CSV format! (The export will quote them, and Excel will happily understand them on import.)

Hmmmmm :rolleyes:

BRB....

Well, I'll be dammed

(No comment Herman!)

Thanks Cap - guess i should end the week on a good note... Have a good one!

Hi,
In my current project using Export XLS, the generated sheet must have a specific name and be the first one, which is possible using the //XL directive:
//XL,AddSheet {,srcbook=xxx.xls} {,dstbook=yyy.xls} {,sheetname=zzz}{,index=1} {deletefirst}
The workbook also has to include a second sheet with instructions, which I could do exporting to an existing XLS file containing that sheet and inserting the data sheet with index=1.

The point is that, //XL directives are added to the CSV file but I'm not in control of it while exporting to XLS directly from XTREE.

I ask if you could add to the INI file one setting equivalent to this directive.
Also crossed my mind that //XL directives could be added to XTREE advanced coldef to be exported in XLS, no?

Well, not a big deal, all this can be manually adjusted by the user after generate the XLS file.

Thanks, have a nice weekend

PS: In advance I inform that this customer is using the CX version so, the former option above would only require an update of the SBX what you may prefer, I don't know, just saying.

While it might be nice to be able to embed /XL directives in the COLDEF, it would certainly be easier to add a new directive to the CSV2XL.INI file. (One complication is that in the case of the INI file, the information can be acted on immediately, while in the case of the COLDEF, we have to store the information in memory associated with each tree, and allow for a nearly unlimited expansion of that memory since who knows how many such /XL directives you might want.)

And since the $EXPCSV2XL command already allows for a custom INI file, the only real downside is that you might end up with multiple INI files that are nearly the same except for one directive. (Or possibly more, when you think of other quirky requirements.)

I'll look into that today...

Another option could be to store the /XL directives for xtree in an external temporary INI file, instead of loading in memory, that would be deleted together with the XTREE.

And we could do this in two possible ways:
a) define /XL directives in coldef and A-Shell create the temporary INI file and is responsible to delete it at the end
b) inform which INI file should be used in coldef, if it's a complement for the permanent CSV2XL.INI or should replace it and, if should be deleted at the end.

Is this reasonable?

Thanks

Option b could be implemented without any change to A-Shell or XTREE, provided that CSV2XL was enhanced to allow a ++include mechanism in the INI file. In that case, instead of specifying the main CSV2XL.INI file in your $EXPCSV2XL coldef option, you would specify an auxiliary INI containing something like:

I'm not sure there would be any need for XTREE or even CSV2XL to delete the auxiliary INI file, since it seems like more of an application issue. (After all, as it stands the user could execute the export operation multiple times within a single XTREE session, so any automatic deletion would have to take place on exit from XTREE, rather than within CSV2XL. Which means we're back to modifying A-Shell/XTREE.) That's not out of the question, but it seems advantageous to avoid that, since replacing just the CSV2XL.SBX would be a lot easier for deployment/updates.

I went ahead and implemented option b if you want to see if that's good enough. Here's a link to the updated csv2xl.sbx 2.1(218)

Note that I changed the syntax somewhat. Here's an example of an actual "auxiliary" INI file:

The [XL] section allows for up to 9 //XL directives to be inserted, just as you would in the CSV file except replacing the normal //XL, prefix with 1=, 2= etc. (required for the INI file syntax.)

If you specify the above INI file, e.g. $EXPCSV2XL:XTREEXL1.INI, then it will use the regular BAS:CSV2XL.INI file, except with the addition of the //XL,AddSheet,... directive.

Hi Jack,
First of all, apologize because I wasn't aware about the possibility to relate the INI file with the $EXPCSV2XL and, it was not because you didn't inform, it was me who didn't pay attention because I always use the global setting in the MIAME file.
Anyway, my ignorance was good to refine it and many thanks for doing that, I was able to completely achieve my goal

Just for the records, my goal was to update the first sheet in an existing workbook with two sheets so, I've adjusted coldef with the PopUpMenu option for $EXPCSV2XL:SOFIA:SIGOA.INI that, smartly, removed the same global setting from the menu avoiding duplication.

My SIGOA.INI has:

The result was exactly what I need

I'll probably regret saying this, but ...

That was almost too easy! :p

Excellent! I'm relieved