Accepted Solutions
@mlipsitz, there are multiple problems with this script. I think you need to do more work learning basic JSL syntax. Yes, we all learn a language by taking already written code and modifying it, but you need some basics. For example this portion of your script looks like you were trying to rename table dt. There is no place in your code where there is a column ::TTV0.000000 is defined, and this transpose would not do what I think you want done.
//Transpose header data into new columns and create "Transposed Header Table"
dt<<( "Header table" ) << Transpose(
columns( :TTV0.000000 ),
Label( :Name( "Test Name:" ) ),
Output Table( "Transposed Header table" )
);Also, when the code below is run, dt is the table of raw data values before the cartesian join and dtjoined is the table of values with the labels. You are overwriting tables (name collisions).
//add the current table to the bottom of the combined data table
dtjoined << Concatenate( Data Table( dt ), Append to first table );Here are a couple suggestions:
- Do not make tables private until your script is written and running properly.
- dt is just shorthand for a table reference. It is okay to reuse (re-purpose) a variable name, but I do not recommend it for a table until the earlier table is closed. Use variable names that make the code more readable, for example dthdr for the header table and dtx for the transposed table.
- Close the table as soon as they are no longer needed.
- Do you really want to join files or concatenate files? If each file has the same metrics, then I think you want concatenate.
- Test your script with a couple files. In other words, learn how to get a base hit, before swinging for the fences.
Your read statements did not make sense for your sample table. Are you sure each of the files have the same format? Or do you need to search for a pattern to determine where the raw data starts? This script will work of the file formats are fixed. The number of raw data rows can change. Note, I hardcoded the list of files to test. The code to get the files looks okay.
Names default to Here(1);
filelist = {"example1_20190214.txt", "example2_20190214.txt"};
filepath = "c:\temp\";
df = nitems(filelist);
for(iii=1, iii<=df, iii++,
filenow = filelist[iii];
fileopen=(filepath||filenow);
dthdr=open(fileopen,//private,
Import Settings(
End Of Line( CRLF, CR, LF ),
End Of Field( Tab, Comma, CSV( 0 ) ),
Strip Quotes( 1 ),
Use Apostrophe as Quotation Mark( 0 ),
Use Regional Settings( 0 ),
Scan Whole File( 1 ),
Treat empty columns as numeric( 0 ),
CompressNumericColumns( 0 ),
CompressCharacterColumns( 0 ),
CompressAllowListCheck( 0 ),
Labels(0 ),
Column Names Start(0 ),
Data Starts( 1 ),
Lines To Read( 3 ),
Year Rule( "20xx" )
)
);
nc = ncol(dthdr);
dthdr << Combine Columns( Delimiter(" "), Columns(2::nc), Column Name("Info") );
dthdr << delete columns(3::nc);
//Transpose header data into new columns and create "Transposed Header Table"
dthdr<< set name( "Header table" );
dtx = dthdr << Transpose(
columns( :Info ),
Label( Column(1) ),
Output Table( "Transposed Header table" )
);
dtx << delete column (:Label);
//Close Header table
Close(dthdr,Nosave);
//New Column( "Source", Character, Nominal );
//:Source << set each value( filenow );
dt=open(fileopen,//private,
Import Settings(
End Of Line( CRLF, CR, LF ),
End Of Field( Tab, Comma, CSV( 0 ) ),
Strip Quotes( 1 ),
Use Apostrophe as Quotation Mark( 0 ),
Use Regional Settings( 0 ),
Scan Whole File( 1 ),
Treat empty columns as numeric( 0 ),
CompressNumericColumns( 0 ),
CompressCharacterColumns( 0 ),
CompressAllowListCheck( 0 ),
Labels( 1 ),
Column Names Start( 4 ),
Data Starts( 5 ),
Lines To Read( "All" ),
Year Rule( "20xx" )
)
);
New Column( "Source", Character, Nominal );
:Source << set each value( filenow );
//Merge Transposed Header table, dtx, with raw data table, dt
dtjoined=dt<< Join(
With( dtx ),
Cartesian Join
);
//close Transposed Header table
Close(dtx, NoSave);
//close raw data table, dt
Close(dt, NoSave);
//For the first file, keep it, it will be used to append other file
//For the other files, append then close
If (iii==1 , dtfinal = dtjoined,
//else
current data table(dtfinal);
dtfinal << Concatenate( dtjoined , Append to first table(1));
wait(0);
close(dtjoined, NoSave)
);
//no need for Run Formulas() there are no formulas in the table
); //end the for iii loop
//Give the final table a name
dtfinal << set name( "Combined Data Table"); I tested this script using the file you displayed and a modification of that file. Here is the result using the script above.
@mlipsitz, there are multiple problems with this script. I think you need to do more work learning basic JSL syntax. Yes, we all learn a language by taking already written code and modifying it, but you need some basics. For example this portion of your script looks like you were trying to rename table dt. There is no place in your code where there is a column ::TTV0.000000 is defined, and this transpose would not do what I think you want done.
//Transpose header data into new columns and create "Transposed Header Table"
dt<<( "Header table" ) << Transpose(
columns( :TTV0.000000 ),
Label( :Name( "Test Name:" ) ),
Output Table( "Transposed Header table" )
);Also, when the code below is run, dt is the table of raw data values before the cartesian join and dtjoined is the table of values with the labels. You are overwriting tables (name collisions).
//add the current table to the bottom of the combined data table
dtjoined << Concatenate( Data Table( dt ), Append to first table );Here are a couple suggestions:
- Do not make tables private until your script is written and running properly.
- dt is just shorthand for a table reference. It is okay to reuse (re-purpose) a variable name, but I do not recommend it for a table until the earlier table is closed. Use variable names that make the code more readable, for example dthdr for the header table and dtx for the transposed table.
- Close the table as soon as they are no longer needed.
- Do you really want to join files or concatenate files? If each file has the same metrics, then I think you want concatenate.
- Test your script with a couple files. In other words, learn how to get a base hit, before swinging for the fences.
Your read statements did not make sense for your sample table. Are you sure each of the files have the same format? Or do you need to search for a pattern to determine where the raw data starts? This script will work of the file formats are fixed. The number of raw data rows can change. Note, I hardcoded the list of files to test. The code to get the files looks okay.
Names default to Here(1);
filelist = {"example1_20190214.txt", "example2_20190214.txt"};
filepath = "c:\temp\";
df = nitems(filelist);
for(iii=1, iii<=df, iii++,
filenow = filelist[iii];
fileopen=(filepath||filenow);
dthdr=open(fileopen,//private,
Import Settings(
End Of Line( CRLF, CR, LF ),
End Of Field( Tab, Comma, CSV( 0 ) ),
Strip Quotes( 1 ),
Use Apostrophe as Quotation Mark( 0 ),
Use Regional Settings( 0 ),
Scan Whole File( 1 ),
Treat empty columns as numeric( 0 ),
CompressNumericColumns( 0 ),
CompressCharacterColumns( 0 ),
CompressAllowListCheck( 0 ),
Labels(0 ),
Column Names Start(0 ),
Data Starts( 1 ),
Lines To Read( 3 ),
Year Rule( "20xx" )
)
);
nc = ncol(dthdr);
dthdr << Combine Columns( Delimiter(" "), Columns(2::nc), Column Name("Info") );
dthdr << delete columns(3::nc);
//Transpose header data into new columns and create "Transposed Header Table"
dthdr<< set name( "Header table" );
dtx = dthdr << Transpose(
columns( :Info ),
Label( Column(1) ),
Output Table( "Transposed Header table" )
);
dtx << delete column (:Label);
//Close Header table
Close(dthdr,Nosave);
//New Column( "Source", Character, Nominal );
//:Source << set each value( filenow );
dt=open(fileopen,//private,
Import Settings(
End Of Line( CRLF, CR, LF ),
End Of Field( Tab, Comma, CSV( 0 ) ),
Strip Quotes( 1 ),
Use Apostrophe as Quotation Mark( 0 ),
Use Regional Settings( 0 ),
Scan Whole File( 1 ),
Treat empty columns as numeric( 0 ),
CompressNumericColumns( 0 ),
CompressCharacterColumns( 0 ),
CompressAllowListCheck( 0 ),
Labels( 1 ),
Column Names Start( 4 ),
Data Starts( 5 ),
Lines To Read( "All" ),
Year Rule( "20xx" )
)
);
New Column( "Source", Character, Nominal );
:Source << set each value( filenow );
//Merge Transposed Header table, dtx, with raw data table, dt
dtjoined=dt<< Join(
With( dtx ),
Cartesian Join
);
//close Transposed Header table
Close(dtx, NoSave);
//close raw data table, dt
Close(dt, NoSave);
//For the first file, keep it, it will be used to append other file
//For the other files, append then close
If (iii==1 , dtfinal = dtjoined,
//else
current data table(dtfinal);
dtfinal << Concatenate( dtjoined , Append to first table(1));
wait(0);
close(dtjoined, NoSave)
);
//no need for Run Formulas() there are no formulas in the table
); //end the for iii loop
//Give the final table a name
dtfinal << set name( "Combined Data Table"); I tested this script using the file you displayed and a modification of that file. Here is the result using the script above.
