if you know the character is always in the same position then you can use mid() to pull out the required numbers yes.
If the values potentially change, then maybe have a look at using keepchar() instead
Once you the number out you can then covert to date,day,month,year etc as you require
hope that helps
The Mid() option does sound like it's a good idea.
My first row is just 1 field and is completely skipped by the script thus far.
However, my problem might be a bit different than I thought.
Because it's probably handy to tie all the info from the CSV files to that date in that first row.
Maybe what I'm actually asking is;
Is there a way to have the info from the CSV files loaded as they are now.
But have it not skip the first row, and instead extract the date from it with a Mid() for example. And then basically put that in its own collumn on all the rest of the lines of that file?
As to have all the info from it tied to a date.
I reckon that if that is possible, then afterwards we can sort on days, months and years.
you would need to split your load into two to achieve this I think.
The first would load all your data but skip the first row (you can set that up in the transformation tab).
Then the second would do a left join on your table, to bring in the date as a new field for you to use (can use the 'first 1' option to just load that row). So you then have a new field in your date with the date (and/or day,month,year).
Looking at what you are doing at present, "*.csv" won't lend itself to doing this, so you might need to setup a for loop on each csv instead of the wildcard load like that, and auto concatenate as you go.
If you have any sample data and app you can upload, could knock something up if that's helpful.
hope that helps
Table1: LOAD Group, Name, Number, Results, FileBaseName() as FileBaseName FROM C:\ProgramName\Files\*.csv (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines, filters( Remove(Row, RowCnd(CellValue, 3, StrCnd(longer, 2))) )); Left Join (Table1) LOAD Date#(Mid([@1:n],33,10),'DD-MM-YYYY') as date, FileBaseName() as FileBaseName FROM [C:\ProgramName\Files\*.csv] (fix, codepage is 1252) Where RecNo()=1;
hope this helps