           BASIC HELP : MS4BI by MS4script   
   2022 version 1.10

Chapter 4 : DATABASES

KEYWORD : IMPORT NEW DB

Definition
- Imports a file or table from an ADO, ODBC, DSN, or SAP data source
- Creates and adds a table to an MS4DB database

Syntax

  		 
			DBconnect{Driver}( NameDB1  );
			  IMPORT : NEW MS4DB   
		      BEGIN  
        		 	  DBconnect {Ms4DB} (DB Name MS4BI )  , 
		    		  Create:Table(Table Name XXXX MS4BI) 
			  END 
				(" SELECT   * from	tableName / NameDB1     " ) ;

Example

  		   
 ** <Local_PC >
  text NewDb :="#WWWMS4BISERVER+\Myms4bi.db";
   DBDelete{MS4DB}(NewDb);
    Display "------------------------------------------------------------------------- " ;
	Display "    Create DB                        " ;
	Display "------------------------------------------------------------------------- " ;
	 
			DBconnect{Ado:"xlsx"}("#DRIVERSYS+\MandragorePlanete\MS4BI\02-DEMO-DESKTOP-BI-EXCELandSTANDARD\1-IMPORT-MAKE\example.xlsx");
			  IMPORT : New Ms4Db   
		      BEGIN  
        		 	  DBconnect {Ms4DB} (NewDb)  , 
		    		  Create:Table("[mytable]") 
			  END 
				(" SELECT  who$.firstname, who$.name,  age$.age,age$.sex  " +
				 " from  [who$]  " +
				 " INNER join  [age$] ON who$.name =  age$.name  "  );

  		  	  
  Visualise Example : DB Connect Ado + MS4DB  

  Visualise Example : DB Connect Ado + MS4DB simple

Remarks
Excel : sheet : [NameSheet$] : the $ is required to be able to access the sheet

See also
DBCONNECT, CREATEDB ,TABLEEXIST , DBDELETE,DBBACKUP, MS4_SQL, Sql_DestroyEnv

KEYWORD : IMPORT NEW DB CONSOLIDATION

Definition
- Imports and consolidates
several files or tables
from ADO, ODBC, DSN, SAP data sources
- Creates and inserts all of the files or tables into a single table in a MS4DB database.

Syntax

  		 
			DBconnect{Driver}(Consolidated Table Name);
			  IMPORT : NEW MS4DB   
		      BEGIN  
        		 	  DBconnect {Ms4DB} (DB Name)  , 
		    		  Create:Table(Table Name ) 
			  END 
				(" SELECT   * from	      " ) ;

Example

  		   
BEGIN  CONSOLIDATION  tables : japanese and chinese -> into  Excel table temp  * 
 SetUniCode(1);
** sheet 1 
 	DBconnect{Ado:"xls"}( LFile1Excel);
 	display "insert Table Japanese : CONSOLIDATION 1  ";
  	Ms4_SQL{Ado:"xls"}(" SELECT  * into " + " %s " + "  from [中国最大の都市のリスト$]", Table1SheetJapanese) return x ;
  
** sheet 2 
 	DBconnect{Ado:"xls"}(Lfile2Excel);
	display "insert Table Chinese : CONSOLIDATION 2   ";
	Ms4_SQL{Ado:"xls"}(" SELECT  * into  %s " + " from [Sheet1$]",Table1SheetChinese) return x;
  	
END ;
  BEGIN ** Create a new table in MS4 DB
 ** *******************************************************************	
 ** INSERT a FILE xls, xlsx, txt ..etc into MS4 DB
 ** Create Cube : ms4LangConsolidation Example
 ** *******************************************************************	
			DBconnect{Ado:"xls"}(Table1);  /* Consolidation table!! lang1$ + lang2$ */
			  IMPORT : New Ms4Db   
		      BEGIN  
        		 	  DBconnect {Ms4DB} (Lcurrentdb)  , 
		    		  Create:Table("[ms4LangConsolidation]") 
			  END  
				(" SELECT   a.SNO , 都市名 as CityName, 英語 as CityEnglish, POPULATION ,"+
				 " 備考 as proforma, CITY  , 城市 As CityChinese, "+
				 " PROVINCE, PREFECTURE  , TYPELevel , FOUNDED ,b.sno as snoduplicate" +
				 " from  [lang1$] as a  ,[lang2$] as b   " +
				        " where  a.英語 = b.city order by   b.city  "  );
    END ;

Visualise Example : Import Consolidation DB
Visualise Example : Create View DB

Prerequisites
- Create a single EXCEL file where each sheet is the table copied by the instruction Ms4_SQL{Ado:"xls"}(" SELECT * into ......... file 1 or table 1
- When importing into the MS4DB database, create the relationship(s) with each sheets using the instruction WHERE a = b and b =c ...

Remarks
Excel : sheet : [NameSheet$] : The $ is required to access the sheet
ALL Examples Imports/connections :

driver::\MandragorePlanete\MS4BI\zConnectorsx-Import\Examples

See also
DBCONNECT, CREATEDB, TABLEEXIST, DBDELETE,DBBACKUP,Sql_DestroyEnv

Interested in joining us? contact@mgplanete.com
The Mg Planete team

the official sites :
mgplanete.com
getms4bi.com
demos.ms4bi.com
help.ms4bi.com
ms4script.com

Download

official site :
getms4bi.com

Our purpose : Keep it simple !

written with ms4script

help 01.010.2022.8

MS4BI HELP 2022

Designer

BASIC HELP : MS4BI by MS4script

Chapter 4 : DATABASES

KEYWORD : IMPORT NEW DB

Definition

Syntax

Example

Remarks

See also

KEYWORD : IMPORT NEW DB CONSOLIDATION

Definition

several files or tables

Syntax

Example

Prerequisites

Remarks

ALL Examples Imports/connections :

driver::\MandragorePlanete\MS4BI\zConnectorsx-Import\Examples

See also

Copyright 2022 by Mandragore Planete

Download

Our purpose : Keep it simple !

MS4BI in 5 minutes