Skip to main content

FAQ

Tips

In addition to using Comext standalone, you have the possibility to display within an Excel Pivot Table any dataset, part of a dataset, or Dictionaries data from a Eurotrace domain using Microsoft 365 (Excel) and Power Query.

The pivot table will be generated in Excel by connecting the Eurotrace dataset to the Excel file through an SQL query connection to existing Access or SQL Server databases.

For more details, please refer to the following document: Connection to Eurotrace Database using M365.

In Windows 11, when the currency symbol “K” (e.g., Zambian Kwacha) is set in the system settings, the operating system automatically converts the string to a number, interpreting “K” as “thousand.”

This bug cannot easily be corrected, but a workaround is to change the currency symbol in the Windows system settings to another option (e.g., “$”).

Eurotrace management

Starting from version 4.3.7 and above, you will only have to store all the data files in a single directory in the new computer. When you will open the “.dom” file located in this directory the first time, all datasets will be retrieved.

The T-SQL code below can be customised to clean your SQL Server Database. 

Nevertheless, please be careful using this script, you can totally delete your database.

Declare @str nvarchar(max)
Set @str='';
select [TABLE_NAME],ROW_NUMBER()Over(Order By [TABLE_NAME]) As Rn
Into #tempTable 
From [INFORMATION_SCHEMA].[tables]
    WHERE [TABLE_TYPE]='BASE TABLE' AND ([TABLE_NAME] like N'%[_]DATA[_]%[_]20__[_]__')
Declare @COunt int
Set @COunt=0;
Select @COunt=COUNT(*) from #tempTable
Declare @TableName Varchar(50)
While @COunt>0
Begin
        Select @TableName=[TABLE_NAME] From #tempTable where Rn=@COunt
        Set @str='Drop Table ['+@TableName+'];'
        print @str
        exec sp_executesql @str
        Set @COunt=@COunt-1;
End
Drop table #tempTable

Useful table patterns you can change (in Yellow in the SQL Script):

  • If you want to remove all table starting by XX_ you can use: XX[_]%
  • In you want to delete all tables that contains _DATA_  and end by _20??_?? : the final expression is : %[_]DATA[_]%[_]20__[_]__

The patterns reference for Like operator in SQL can be found there: LIKE (Transact-SQL) - SQL Server | Microsoft Learn

The main tokens to remember:

  • For none or any character: %
  • For exactly 1 character: _
  • For 1 character from a list: […] (can be used to select token _ or %)

The N before the expression is to managed Unicode characters (like Cyrillic or Arabic characters).

To use this script (compatible with SQL Server 2008), just open Management Studio and Create a New Query. Then just select to target database and type the Table pattern you want to Clean.

MSSQL Code

 

Tips: if comment the exec line (using - -), the script will just display the list of table that would be deleted.

Invalidate deletion for testing