Can I create table with info from a place in the 1st row?

[#1]

Hello,

With a project I'm working on there's a program that puts out info into .csv files.

These files I'm able to load into the document.

However.

The first row of all these files is outfitted as such;

"Total info on these things from 12-04-2012 selected under this this and that"

It then starts with the CSV sort of style of the column-names and then the info. Which is able to be loaded perfectly.

However, this first row, which is skipped by the script, does contain the dates of the info.

currently the script looks like this;

Directory;
LOAD Group,
     Name,
     Number,
     Results,
    
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)))
));

My question now is;

In the first row as in the example, the date doesnt show up for the first 32 characters in it.

Is there a way to take the info from character-spot 33 to 42 and implement them as day, month and year  ?

[#2]

Hi Katri,

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

keepchar([Field],'123456789-')

Once you the number out you can then covert to date,day,month,year etc as you require

hope that helps

Joe

[#3]

The Mid() option does sound like it's a good idea.

However.

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.

So..

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.

[#4]

Hi Katri,

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

Joe

[MarcoWedel]

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

regards

Marco