Loading data multiple files question

[#1]

Hi,

I'm loading multiple files into my Load Script by means of using an * after my file names.

I'm having a problem with some of my fields.

I want to break down Year, Month, Day, Time into separate fields from another field but my script is only doing this for the first file.

Does anyone know how I can do this to all files? I think I need to somehow implement the Year[DateTimeNoSec] into the actual LOAD area and not the LOAD* area but can't figure out exactly how to do it correctly and make my filters not show duplicate information.

LOAD*,

  daystart(DateTime) as Date,

  mid(RequestURI, index(RequestURI, ' ', 1), index(RequestURI, ' ', 2) - index(RequestURI, ' ', 1)) as URL,

  mid(RequestURI, index(RequestURI, ' ', 2)) as Protocol,

  if(index(Track, '/', 3) > 0, mid(Track, index(Track, '/', 2) + 1, (index(Track, '/', 3) - index(Track, '/', 2)) - 1), '') as ReferrerSite;

LOAD*,

Year([DateTimeNoSec]) as Year,

Month([DateTimeNoSec]) as Month,

Day([DateTimeNoSec]) as Day,

Time([DateTimeNoSec]) as Time;

LOAD

  filename() as DateHour,

  @1 as RemoteHost,

  date(date#(mid(@4, 2, 21), 'DD/MMM/YYY:hh:mm:ss'), 'DD MMM YYYY hh:mm:ss') as DateTime,

  date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'DD/MMM/YYYY hh:mm') as DateTimeNoSec,

    replace(@5, ']', '') as TimeZone,

    @2 as FromIdentifier,

    @3 as RemoteUser,

    @4 as RequestTime,

    mid(@5, 1, index(@5, ' ', 1)) as Method,

    @5 as RequestURI,

    @6 as HTTPstatus,

    @7 as Bytes,

    @8 as ForwardedIP,

    @9 as Track,

    @10 as RemoteHostIP,

    @11 as ResponseTimeinMS

FROM

.\ProcessedData_oAuth_*.txt

(txt, codepage is 1252, no labels, delimiter is spaces, msq);

[chiru_thota]

You can do it some thing like this.

Step1:

Table1:

LOAD

  filename() as DateHour,

  @1 as RemoteHost,

  date(date#(mid(@4, 2, 21), 'DD/MMM/YYY:hh:mm:ss'), 'DD MMM YYYY hh:mm:ss') as DateTime,

  date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'DD/MMM/YYYY hh:mm') as DateTimeNoSec

  /* Other fields */

FROM.\ProcessedData_oAuth_*.txt

(txt, codepage is 1252, no labels, delimiter is spaces, msq);

Table 2:

Load

LOAD*,

Year([DateTimeNoSec]) as Year,

Month([DateTimeNoSec]) as Month,

Day([DateTimeNoSec]) as Day,

Time([DateTimeNoSec]) as Time from resident  Table1:

drop table Table1;

[#3]

Thank you for your help. Unfortunately I don't follow your instructions.

[#4]

I put in the following code...

date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'DD') as Day,

This works but when I create a List Box, I get 60 entries for the same value. For example, for the date of March 20th, my List box has 60 fields of the number 20.

[chiru_thota]

The reason is your taking time stamp. even though date is same time stamp will be different.

may be you can try below.

Timestamp(mid(@4, 2, 17),'MM/DD/YYYY') as Day,

Otherwise up load your file with some data.I can do it for you

[#6]

Thank you so much.

I would really like to send you the information because it's work related (sensitive?) and risk getting in trouble.

I arranged my LOAD script as per below.

Everything looks okay but as you mentioned, my List Boxes for Year, Month, and Day show 60 times for the same value. Time is okay since there are 60 minutes in each hour, although it would be even better to split it up by Hour and Minute.

LOAD

  filename() as DateHour,

  @1 as RemoteHost,

  date(date#(mid(@4, 2, 21), 'DD/MMM/YYY:hh:mm:ss'), 'DD MMM YYYY hh:mm:ss') as DateTime,

  date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'DD/MMM/YYYY hh:mm') as DateTimeNoSec,

  date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'YYYY') as Year,

  date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'MMM') as Month,

  date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'DD') as Day,

  date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'hh:mm') as Time,

    replace(@5, ']', '') as TimeZone,

    @2 as FromIdentifier,

    @3 as RemoteUser,

    @4 as RequestTime,

    mid(@5, 1, index(@5, ' ', 1)) as Method,

    @5 as RequestURI,

    @6 as HTTPstatus,

    @7 as Bytes,

    @8 as ForwardedIP,

    @9 as Track,

    @10 as RemoteHostIP,

    @11 as ResponseTimeinMS

FROM

.\ProcessedData_oAuth_*.txt

[#7 · Accepted solution]

I did it! Thank you so much for your tips.

Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'YYYY')) as Year,

  Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'MMM')) as Month,

  Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'DD')) as Day,

  Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'hh')) as Hour,

  Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'mm')) as Minute,