Remove words from array not ending with$

#1 · August 2, 2019, 1:44 pm

Hello All,

I need to use Excel files as an DataBase. All the files have only two sheets, but not always the sheets have the same name.

Using NeoDBpro to open an Excel file:

dbpOpenDatabase "excel" "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=[FameFile];Extended Properties=[#34]Excel 12.0 Xml;HDR=YES[#34];Persist Security Info=False"

Then I use the dbpGetTableNames to retrieve the "sheet names":

dbpGetTableNames "excel" ";" "[FameTables]"

The array variable [FameTables] holds after this something like:

A_1;A_4;A_5;cells_def_coding;cells_def_colour;cells_def_colour_marking;cells_def_connection_type;cells_def_mounting;cells_def_pole_number;cells_language;cells_options_leiste;Kodierung;Konfig;Konfigurator$;Konfigurator$_xlnm#Print_Area;selection_language;Stückliste_Leiste$;Stückliste_Leiste$_xlnm#_FilterDatabase;Stückliste_Leiste$_xlnm#Print_Area;

The real "sheet names" in the above variable are "Konfigurator$" and "Stückliste_Leiste$". These are the only two ending with the "$" as Excel names the sheets.

To open the "Excel sheets" as an table I need the correct sheet names out of this array, as below.

dbpExecSQL "excel" "SELECT TOP 50 * FROM [#91]Stückliste_Leiste$[#93] WHERE [#91]AMOUNT[#93] > 0" "BOM"

How can I change (modify) the array [FameTables] that only the two words ending with $ (sheet names) are kept in the array. Like below:

[FameTables] = "Konfigurator$;Stückliste_Leiste$"

Has anyone an Idea???

Best regards and thanks for the support,

Eric Beerhorst

#2 · August 3, 2019, 3:59 am

@ebear (Eric)

The array variable [FameTables] holds after this something like:

A_1;A_4;A_5;cells_def_coding;cells_def_colour;cells_def_colour_marking;cells_def_connection_type;cells_def_mounting;cells_def_pole_number;cells_language;cells_options_leiste;Kodierung;Konfig;Konfigurator$;Konfigurator$_xlnm#Print_Area;selection_language;Stückliste_Leiste$;Stückliste_Leiste$_xlnm#_FilterDatabase;Stückliste_Leiste$_xlnm#Print_Area;

The real "sheet names" in the above variable are "Konfigurator$" and "Stückliste_Leiste$". These are the only two ending with the "$" as Excel names the sheets.

1) That sounds like a weird response to the dbpGetTables command.

2) In order to guard against some of the items having semi-colons as part of their content, I suggest that you use a delimiter string that would not be something returned from Excel (say ;:; ) like so ...

dbpGetTableNames "excel" ";:;" "[FameTables]"

3) The next step is to parse the returned string into an array ...

StrParse "[FameTables]" ";:;" "[FametableItems]" "[ItemCount]"

4) Then, loop through the parsed items looking for values ending in $

Loop "1" "[ItemCount]" [ThisItemNumber]
   SetVar "[ThisItemText]" "[FameTableItems[ThisItemNumber]]"
   ... check if it ends in $
   SearchStr "!$" "[ThisItemText]" "[FoundPosition]" ""
   If "[FoundPostion]" "<>" "0"
      ... includes a $ sign
      StrLen "[Thisitem]" "[ThisItemLen]"
      If "[FoundPosition] "=" "[ThisItemLen]"
         ... $ sign is at the end of the string
         GoSub "SelectTop50"
      EndIf
   Endif
EndLoop

5) Your subroutine would look like ...

:SelectTop50
... [ThisItemText] will contain the name of the desired Table

dbpExecSQL "excel" "SELECT TOP 50 * FROM [#91][ThisItemText][#93] WHERE [#91]AMOUNT[#93] > 0" "BOM"
... etc. etc.

Return

Note that I have not verified the code (could be typos) but you should be able to get the overall idea.

#3 · August 8, 2019, 6:39 pm

Hello Gaev,

Sorry for the late reply, but I was very bussy.

Thanks a lot for your support, it works now perfect.

I did about what you where supposing above but I used one of the DecSoft's NeoPlugins to make the code a bit shorter.

dbpGetTableNames "excel" "#" "[ExcelTables]"

StrParse "[ExcelTables]" "#" "[ExcelTableItems]" "[ExcelTablesCount]"

Loop "1" "[ExcelTablesCount]" "[ExcelTableNumber]"
     npEndsStr "[ExcelTableItems[ExcelTableNumber]]" "$" "[EndsWith]"
     If "[EndsWith]" "=" "True"
        SetVar "[ExcelSheets]" "[ExcelTableItems[ExcelTableNumber]];[ExcelSheets]"
     EndIf
EndLoop

StrParse "[ExcelSheets]" ";" "[ExcelTable]" "[ExcelTableCount]"

The "npEndsStr" function from Davids npUtil plugin, looks if a text ends with an specific string. The result variable is True if the provided text ends with the specific string, or False if not. Above my solution for the problem, maybe for use to anyone.

Thanks a lot and best regards,

Eric Beerhorst

Remove words from array not ending with$ - Forum

Remove words from array not ending with$