BCP and Date Problems

Hi,

We have a job that, every night, imports mail logs into SQL Server for processing. We receive these mail logs from the mail group, so we have no say in how the log should be formatted, basically we are given what we are given.

Our problem started occurring on Oct-01, and has occured every day since then.

The log file may contain rows with dates such as follows (don't ask me why there isn't a consistant format)

1) 2003-10-01 00:18:5
2) 2003-9-01 00:8:6
3) 2003-9-1 00:9:6
4) 2003-9-1 0:09:6

5) 2003-10-1 00:18:6
6) 2003-10-01 0:19:6
7) 2003-10-01 00:8:6

Now, 1-4 import. 5-7 don't.

From what I can see.

If the month is a single digit month, i.e. September above, then it will import basically all formats (e.g. 1 digit for the hour, 1 digit for the minute or 1 digit for the day).

If the month is a double digit month, i.e. October above, then it will not import unless the date is perfectly formatted (e.g. 2 digits for the hour AND 2 digits for the minute AND 2 digits for day).

An excerpt from the format file we are using is listed below.

Why does SQL Server allow 'bad' dates with single digit months, but not double digit months? It's really confusing me. I know the obvious solution, tell the Mail guys to properly format their files, but are there any other solutions?

Thanks heaps. Hope I've asked this clearly.

Format File
----
8.0
11
1 SQLCHAR 0 0 "" 0 TBL_MAIL_LOG_ID ""
2 SQLCHAR 0 0 "" 0 LOG_FILE_ID ""
3 SQLCHAR 0 1 " " 3 DATE ""

The last line reads (broken into individual lines below)

3
SQLCHAR
0
1
" " <-- there is a space in there
3
DATE
""Howdy

Sadly it appears its a quirk in SQL - unless a '1' is an '01' it seems to get its knickers in a knot, especially at the start of a column of information.

Try importing the raw data into another table that uses CHAR instead of datetime for the dates, then add the '0' to any dates required, then transfer across to final table. Its time consuming, but in the long run more robust.

Cheers

SG

Basic steps as how to web usage mine in sql server 2005

Hi there,

I am doing a project on web usage mining of my universities server logs and im just wondering how i go about mining them in sql server 2005?

Do i mine them in one table? do i normalise the web log data? what algorithms will i use on them as im trying to get usage patterns from the users and also where most of the users come from.

Thanks in advance

Gary

Hi

Here’re some thoughts that might help you design your Data Mining for weblogs:

1. You can put the data in one or more tables as per the semantics of the data. Data which is an entity by itself should be put in a flat table with one key per entry (user sessions on the web server for example), whereas some data naturally will have a many to one relation with the primary data (page visits per session for example) and can be put in a separate table with primary/foreign key relationship. SQL Server 2005 will model them as case table/nested table for the purpose of mining.

2. Normalize: Depends on what your data looks like. If you want to run clustering algorithm and your data has two attributes, A and B and A is 10 times more important than B, you should normalize accordingly. If the actual values of A are in order of thousand and actual values ob B are in order of tens and they are equally important, you should again normalize. However, if you want to use the as-is value without a weight, you do not have to normalize the data.

3. Usage Patterns, like a sequence of page visits can be modeled using sequence clustering algorithm. User categorization based on attributes might be a clustering algorithm. If you have more information on what you want to find out, I might be able to suggest more specific choices.

Hope this helps

Shuvro