Setting Table Structure Prior to Import
Strata can work directly with fixed-length or delimited text files. Let's say we need to change the structure of a text-delimited file — say, making a field with smaller to truncate unnecessary values and reduce our files size. We can do this using two methods, either by manually changing our values or by changing the values programmatically prior to import. (As an example, we may have wanted to reduce our web log field width size in this example).
Changing the Structure Manually
A quick way to handle the structure is to simply open the text file and then change the structure width manually. This will reduce the field width down to whatever is necessary for your analysis (note that when you reduce the field width, it will cut off any text that expands beyond the width, so proceed with caution). To change the structure, do the following:
- Open the file via File > Open (i.e. don't use the import wizard). Note that for most purposes, after opening a CSV file, you can use the file as is without having to import it into the project. Sorting, filtering, etc. all work. For more involved analysis, such as relationships and the like, importing is recommended. You can import either with the Import Wizard, or the “Save As” technique, the latter of which we will use here.
- Toggle on the protractor icon on the upper right a couple times (or select View > Design View) until you come to the table structure page.
- Enter in the desired fixed field widths.
- Click on the icon again to get back to the table view.
- Select File > Save As and type in the name of a project table. The file will be imported with the field specs/definitions you specified.
Changing the Structure Programmatically
Let's suppose, instead, that we already have a table definition keyed in and want to define our fields directly in a script. Here's a script template that you can use to do this:
// make a definition
var def =
{
type: "delimited",
delimiters: ",",
line_delimiters: "\n",
text_qualifiers: "\""",
fields:
[
{ name: "item_no", type: DbType.Character, width: 10, scale: 0 },
{ name: "amount", type: DbType.Numeric, width: 20, scale: 2 },
{ name: "name", type: DbType.Character, width: 34, scale: 0 },
{ name: "ranking", type: DbType.Double, width: 6, scale: 0 },
{ name: "value", type: DbType.Character, width: 224, scale: 0 }
]
};
// assign the definition to a specific file
HostServices.data.assignDefinition("c:\\myfile.csv", def);
// import the file
var sql = "";
print("Importing...");
sql += "DROP TABLE IF EXISTS project_table;";
sql += "SELECT * INTO project_table FROM file://c:/myfile.csv";
HostApp.getDatabase().execute(sql);
// refresh project tree to show the new table
HostApp.refresh();
print("Done.");
This script will assign an import definition to the text file so that you don't have to define it manually in the interface (note: the template above assumes a comma-delimited file with quotation marks as the text-qualifier). The last step of the script will import the file into the system.
Got any questions on this or other importing issues? Please let us know in the comments below.
