Example of using CSVIImportFile function

[Dung]

I have got my custom CSV Export using JScript-CSV in EAScriptLib working. I need an example of how to use CSVIImportFile function in the same library to import the CSV file created by my CSV Export back to EA with updated value similar to the standard CSV Import/Export.

Thank you in advance.

Dung

[Aaron B]

The following script should import the CSV created by the export example I gave earlier:
http://www.sparxsystems.com/cgi-bin/yabb/YaBB.cgi?num=1396310555/1#1

Script does not handle FKs because there is not enough information about the relationship in the CSV.

Code: [Select]

!INC Local Scripts.EAConstants-JScript
!INC EAScriptLib.JScript-CSV
!INC EAScriptLib.JScript-Dialog

/*
 * Script Name: Import Tables from CSV
 * Author: Aaron Bell
 * Purpose: For use as a Project Browser script type.  Imports tables
 *   and columns from a CSV file into a new package below the currently
 *   selected package.
 * Date:
 */

//Expected columns in CSV:
//      columns[0] = "TableName";
//      columns[1] = "Column";
//      columns[2] = "Type";
//      columns[3] = "Length";
//      columns[4] = "PrimaryKey";
//      columns[5] = "ForeignKey";

////////////////////////////////////////////
// External Options
//

//columns are comma-delimited
CSV_DELIMITER = ",";


////////////////////////////////////////////
// Local Options
//

// Set default database type here.  All tables created by this script will use this type by default.
var DEFAULT_DATABASE_TYPE = "SQL Server 2008";

////////////////////////////////////////////
// Global variables
//

var rowCount = 0;
var attCount = 0;
var targetPackage as EA.Package;
var currentTable as EA.Element;

////////////////////////////////////////////
// Main Code
//

function main()
{
      var filePath = OpenCSVFileDialog();
      var fileName = GetFilenameFromPath(filePath);
      
      //abort if no file was specified
      if ( filePath == null || filePath == "" )
            return;
      
      //create a new package below the currently selected package in the Project Browser
      targetPackage = Repository.GetTreeSelectedPackage().Packages.AddNew(fileName, "");
      targetPackage.Update();
      
      Repository.EnsureOutputVisible("Script");
      Repository.ClearOutput("Script");
      
      Session.Output("Importing file: " + filePath);

      Repository.BatchAppend = true;
      Repository.EnableUIUpdates = false;
      
      var firstRowContainsHeadings = true;

      CSVIImportFile( filePath , firstRowContainsHeadings );
      
      Repository.BatchAppend = false;
      Repository.EnableUIUpdates = true;
      
      Session.Output("Done.  " + rowCount + " rows imported.");

      Repository.RefreshModelView(targetPackage.PackageID);

}

//Callback function
// * The user defined OnRowImported() function can query for information about the current
// * row through the functions CSVIContainsColumn(), CSVIGetColumnValueByName()
// * and CSVIGetColumnValueByNumber().
function OnRowImported()
{
      rowCount++;

      // get all values for this row
      var TableName = CSVIGetColumnValueByName("TableName");
      var ColumnName = CSVIGetColumnValueByName("Column");
      var ColumnType = CSVIGetColumnValueByName("Type");
      var ColumnLength = CSVIGetColumnValueByName("Length");
      var ColumnIsPK = CSVIGetColumnValueByName("PrimaryKey");
      var ColumnIsFK = CSVIGetColumnValueByName("ForeignKey");

      Session.Output("  Importing: " + TableName + "." + ColumnName);
      
      if (currentTable == null)
      {
            currentTable = targetPackage.Elements.AddNew(TableName, "Class");
            currentTable.StereotypeEx = "table";
            currentTable.Gentype = DEFAULT_DATABASE_TYPE;
            currentTable.Update();
            attCount = 0;
      }
      else
      {
            if (currentTable.Name != TableName)
            {
                  // table name different to previous row.  create new table.
                  currentTable = targetPackage.Elements.AddNew(TableName, "Class");
                  currentTable.StereotypeEx = "table";
                  currentTable.Gentype = DEFAULT_DATABASE_TYPE;
                  currentTable.Update();
                  attCount = 0;
            }
      }

      var attribute as EA.Attribute;
      
      attribute = currentTable.Attributes.AddNew(ColumnName, ColumnType);
      attribute.StereotypeEx = "column";
      attribute.Pos = attCount;
      if (ColumnLength != "")
      {
            attribute.Length = parseInt(ColumnLength);
      }
      attribute.IsOrdered = (ColumnIsPK == "true");
      //TODO: Setting Foreign Key will require further handling.  Insufficient information in CSV to know which table/column it refers to.
      // attribute.IsCollection = (ColumnIsFK == "true");
      attribute.Update();
      
      // keep count of how many attributes in the current table.  Used for setting attribute.Pos
      attCount++;
      
}

function OpenCSVFileDialog()
{
      var Project;
      var Filename, FilterString, Filterindex, Flags, InitialDirectory, OpenorSave, filepath;

      Filename = "";
      FilterString = "CSV Files (*.csv)|*.csv|All Files (*.*)|*.*||";
      Filterindex = 1;
      Flags = 0;
      InitialDirectory = "";
      OpenorSave = 0;
      
      Project = Repository.GetProjectInterface();
      filepath = Project.GetFileNameDialog(Filename, FilterString, Filterindex, Flags, InitialDirectory, OpenorSave);
      
      return filepath;
}

function GetFilenameFromPath(filePath)
{
      var bsindex, fileName;
      
      // find the last backspace in the file path
      bsindex = filePath.lastIndexOf("\\");

      if (bsindex > 0)
      {
            // get the name of the file only - minus the directory path
            fileName = filePath.substring(bsindex+1, filePath.length);
      }
      else
      {
            fileName = filePath;
      }
      
      return fileName;
}

main();

[Derek Penning]

Hi Aaron, have you got experience of importing infrastructure models using csv?

Kind regards

Derek Penning