After several trial, below code sucessfully import multiple CSVs through loop, and the format are correct.
But I still have two remain issue:
1. starting from second file, the headers are not exclude as column title, they are treated as data in the table, there are two rows of headers, first row is file name, second row are titles separted by space
2. I would like to add filename column so that i can analyze different files, but this is not like import mutiple file function that I can easily achieve it?
Names Default To Here( 1 );
dir = Pick Directory( "Select a directory" );
filelist = Files In Directory( dir );
mylist = {};
For( i = 1, i <= N Items( filelist ), i++,
file = dir || "/" || filelist[i];
str = Load Text File( file );
lines = Words(str, "\!N");
data = Transform Each({line}, lines,
line = Collapse Whitespace(Substitute(line, {"(", ")"}, ""))
);
modifiedstr = Concat Items(data, "\!N");
mylist[i] = modifiedstr;
);
dt = Open(
Char To Blob( Concat Items( mylist, "\!N" ) ),
Import Settings(
End Of Line( CRLF, CR, LF ),
End Of Field( Spaces, Space, CSV( 0 ) ),
Strip Quotes( 1 ),
Use Apostrophe as Quotation Mark( 0 ),
Use Regional Settings( 0 ),
Treat empty columns as numeric( 0 ),
Compress Numeric Columns( 0 ),
Compress Character Columns( 0 ),
Compress Allow List Check( 0 ),
Labels( 1 ),
Column Names Start( 2 ),
First Named Column( 1 ),
Data Starts( 3 ),
Lines To Read( "All" ),
Year Rule( "20xx" )
)
);