merging files

walk545 — Mon, 19 Mar 2018 14:49:58 GMT

im trying to merge text files and turn them into excel files.

i found to scripts one which merges files and a second which cleans it ( delets empty columns and such).

when i combine the two scripts i get this message:

"send expects scriptable object in access or evaluation of 'send' "

the script fails at: Set Selected()

can someone please help me solve the problem.

here is how i combined the two:

names default to here (1);
Clear Symbols();
//set up variables with starting values
filelist={}; //empty list
//launch dialog to get the file location
(
prefilepath = munger(Pick Directory( "Browse to the Directory of the .txt / .csv files " ),1,"/","");
filepath = Convert File Path( prefilepath, Windows );
prefilelist = Files In Directory( filepath );
n2=nitems(prefilelist);
//filter out any non-txt or csv files
For( i2 = 1, n2 >= i2, i2++,
file=(prefilelist[i2]);
// changed from item 2 to item -1
If( Item( -1, prefilelist[i2], "." ) == "txt" | Item( -1, prefilelist[i2], "." ) == "csv",
Insert Into( filelist,file),
show(file)
)
);
/*
//filter out any non-Excel files for the file list. Commented out
For( i2 = 1, n2 >= i2, i2++,
file=(prefilelist[i2]);
If( Item( -1, prefilelist[i2], "." ) == "xls" | Item( -1, prefilelist[i2], "." ) == "xlsx",
Insert Into( filelist,file),
show(file)
)
);
*/
nf=nitems(filelist); //number of items in the working list
cctable= New Table( "Combined data table ");//make an empty table
cctable<<New Column( "Source", Character, Nominal );
For( iii = 1 , iii <= nf, iii++, //this starts the first loop
filenow = ( filelist[iii] );
fileopen=(filepath||filenow);
dt=open(fileopen,private);
New Column( "Source", Character, Nominal );
:Source << set each value( filenow );
//dt<<new column("Source", character, nominal)<<set each value(9999);
dt << Run Formulas();
//add the current table to the bottom of the combined data table
cctable << Concatenate( Data Table( dt ), Append to first table );
//don't use "Create Source Column" argument
Close( dt, NoSave );//after concatenating the table, close it and move on
);//end of the first for loop

(
//Select analytics file "DT_Import" and open
DT_Import = cctable;
DT_Import << Minimize Window;

//Get data table name and set operating directory as opened file location
TableName = DT_Import << Get Name();
Directory = prefilepath);
//Remove extraneous rows
Rows_to_delete = DT_Import << Get rows where(Is Missing(:WLD Filename) > 0|
Contains(:WLD Filename, "----") > 0|
Contains(:WLD Filename,"Reprocess Started:")|
Contains(:WLD Filename,"Reprocess Completed:")|
Contains(:WLD Filename,"Comments:")
);
If(ANY(Rows_to_delete) == 1,
DT_Import << Delete Rows(Rows_to_delete);
);
//Find locations of misaligned headers for cleanup
Row_Clip = DT_Import << Get rows where(Contains(:WLD Filename, "WLD Filename") > 0);
//If cleanup needed, continue to concatenation of displaced headers
If(N Rows(Row_Clip) > 0,
//Add End Row to Row List
End_Row = N Rows(DT_Import);
Row_Clip_End = VConcat(Row_Clip,End_Row);
Total_Clips = N Rows(Row_Clip)+1;
//Create invisible repair table from initial rows
DT_Import << Select Rows(Index(1, Row_Clip[1]));
DT_Wafer = DT_Import << Subset( Selected Rows , Output Table Name(TableName||"_Repair"), invisible);
DT_Import << Clear Select;
DT_Wafer << select where(Contains(:WLD Filename, "WLD Filename") > 0);
DCheck = DT_Wafer << Get selected rows;
If(ANY(DCheck) == 1,
DT_Wafer << Delete Rows;
);
DT_Wafer << Begin Data Update;
//Concatenation Loop
For (n=1, n < Total_Clips, n++,
DT_Import << Select Rows(Index(Row_Clip[n], Row_Clip_End[n+1]));
DT_temp = DT_import << Subset(Selected Rows, invisible, Output Table("Temp"));
DT_Import << Clear Select;
For (i=1, i < N Cols()+1, i++,
col = Column(i);
col << Set Name("Col"||Char(i));
);
For (j=1, j < N Cols()+1, j++,
col = Column(j);
col << Set Name(col[1]);
);
DT_temp << select rows (1);
DCheck = DT_temp << Get selected rows;
If(ANY(DCheck) == 1,
DT_temp << delete rows;
);
DT_temp << select where(Contains(:WLD Filename, "WLD Filename") > 0);
DCheck = DT_temp << Get selected rows;
If(ANY(DCheck) == 1,
DT_temp << delete rows;
);
Data Table(TableName||"_Repair") << Concatenate(Data Table("Temp"), invisible, Append to first table);
Close("Temp");
Cleanup_Processing=Round(100*(n+1)/(Total_Clips),1);
wait(0);
Completion_Monitor << Bring Window to Front;
);
DT_Wafer << End Data Update;
//Close(DT_Import);

//If no Cleanup needed
,DT_Wafer = DT_Import;
Cleanup_Processing=100;
);
//Remove any of the unnamed import artifact columns with no data
CName = DT_Wafer << Get Column Names();
CCount = N Items(CName);
for(i=1,i<=CCount,i++,
CTest = Column(CName[i]);
If(Contains(CName[i],"Col") > 0 & Is Missing(CTest[1]),
CName[i]<<Set Selected(1));
);
DCheck = DT_Wafer << Get selected columns;
If(Is Empty(DCheck) == 0,
DT_Wafer << delete columns;
);
DT_Wafer << Clear Select;
DT_Wafer << Clear Column Selection;
//Set text export preferences
Preferences(Export Settings(Export Table Headers));
Preferences(Export Settings(End of Field(Tab)));
DT_Wafer << Show Window(1);
(
DT_Wafer << Save(Directory||TableName||"_attempt.csv", Text);
);
)

attached are the original scripts (i did not write them).

Re: merging files

txnelson — Mon, 19 Mar 2018 14:49:19 GMT

A quick scan of your code......I assume that CName[i] is not being interpreted as a column in your << Set Selected. use a Column() function to insure it points to being a column reference.

Column(CName[i]) << Set Selected( 1 );

topic Re: merging files in Discussions

merging files

Re: merging files