IIS日志导入Sql数据库
1. 建立表结构SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE .(
(255) NULL,
(255) NULL,
(255) NULL,
(255) NULL,
(255) NULL,
(1000) NULL,
(255) NULL,
(255) NULL,
(255) NULL,
(1000) NULL,
(1000) NULL,
(255) NULL,
(255) NULL,
(255) NULL,
(255) NULL
) ON
GO
SET ANSI_PADDING OFF
GO
注：a. 由于数据量比较大，所以分表保存，上面表名是7月份的。
b. 表的结构要根据你日志保存的字段来创建哦。
2. 使用bcp导入数据
bcp .dbo. in "g:\iislog\log.txt" -w -t"*" -r"\r" -U"sa" -P"123456" -S"UKEASSERVER\SQLEXPRESS"
-t:列分隔符
-r:行分隔符
-w:使用unicode编码。如果你日志里包含有中文的用-w,如果没有，用-c就好了，-w会使你的数据库文件变很大的。
log.txt是日志文件，如果有中文，要保存为unicode编码，不要用utf8啦，不然bcp的时候都是乱码了，就是说bcp不支持utf8的。没有中文的话，ANSI就ok了。
注：出现乱码很恶心的，经常说你的表结构有问题啊，什么字段不够长啊，数据字符串被截断了啊。
我的IIS日志文件都是*.log文件，而且每天一个文件，不是txt文件啊，怎么办呢？
自己写代码啊，把每个月的数据文件读出来，去掉没有用的行，再保存到log.txt文件里就好了，这个处理过程比bcp时间快多了。
IIS日志转换
1 using System;
2 using System.Collections.Generic;
3 using System.Text;
4 using System.IO;
5 using System.Text.RegularExpressions;
6
7 namespace Log2Sql
8 {
9 public class FormatLog
10 {
11 private string inputDirectory;
12 private string outputFileName;
13 private string errorFileName;
14 private string fieldSeparator = "*";
15 private string rowSeparator = "\r";
16 private int fieldLength = 15;
17
18 public FormatLog(string inputDirectory, string outputFileName, string errorFileName)
19 {
20 this.inputDirectory = inputDirectory;
21 this.outputFileName = outputFileName;
22 this.errorFileName = errorFileName;
23 }
24
25 public void Execute()
26 {
27 Regex reg = new Regex("u_ex(\\d{6})\\.log");
28 string[] files = Directory.GetFiles(inputDirectory);
29 List<string> temp = new List<string>();
30 foreach(string f in files){
31 Match match = reg.Match(f);
32 string date = match.Groups.Value;
33 if(date.StartsWith("1011")){
34 temp.Add(f);
35 }
36 }
37 files = temp.ToArray();
38 using(FileStream fs = new FileStream(outputFileName, FileMode.Create)) {
39 using(StreamWriter sw = new StreamWriter(fs, Encoding.Unicode)) {
40 foreach(string f in files) {
41 Save(GetRecordLineForFile(f), sw);
42 }
43 }
44 }
45 }
46
47 public void WriteInvalidFields()
48 {
49 using(FileStream fs = new FileStream(errorFileName, FileMode.Create)){
50 using(StreamWriter sw = new StreamWriter(fs, Encoding.UTF8)){
51 using(FileStream fs2 = new FileStream(outputFileName, FileMode.Open)) {
52 using(StreamReader sr = new StreamReader(fs2, Encoding.UTF8)) {
53 string lineData = sr.ReadLine();
54 while(!string.IsNullOrEmpty(lineData)) {
55 List<string> fields = GetInvalidFields(lineData);
56 foreach(string f in fields) {
57 sw.WriteLine(string.Format("{0} {1}", f.Length, f));
58 }
59 lineData = sr.ReadLine();
60 }
61 }
62 }
63 }
64 }
65 }
66
67 private void Save(List<string> datas, StreamWriter sw)
68 {
69 foreach(string s in datas) {
70 sw.Write(s);
71 sw.Write(rowSeparator);
72 }
73 }
74
75 private List<string> GetRecordLineForFile(string fileName)
76 {
77 List<string> list = new List<string>();
78 try{
79 using(FileStream fs = new FileStream(fileName, FileMode.Open)){
80 using(StreamReader sr = new StreamReader(fs, Encoding.UTF8)){
81 string lineData = sr.ReadLine();
82 while(!string.IsNullOrEmpty(lineData)) {
83 if(!lineData.StartsWith("#")) {
84 lineData = lineData.Replace(" ", fieldSeparator);
85 if(lineData.Split(fieldSeparator.ToCharArray()).Length == fieldLength){
86 list.Add(lineData);
87 }
88 }
89 lineData = sr.ReadLine();
90 }
91 }
92 }
93 }catch(Exception e){
94 throw e;
95 }
96 return list;
97 }
98
99 private List<string> GetInvalidFields(string linedata)
100 {
101 string[] fields = new string[] { "date", "time", "s-ip", "cs-method", "cs-uri-stem", "cs-uri-query", "s-port", "cs-username", "c-ip", "cs(User-Agent)", "cs(Referer)", "sc-status", "sc-substatus", "sc-win32-status", "time-taken" };
102 string[] datas = linedata.Split(fieldSeparator.ToCharArray());
103
104 List<string> temp = new List<string>();
105 for(int i=0; i<datas.Length; i++) {
106 if(datas.Length > 255 && i != 10) {
107 temp.Add(string.Format("{0} {1}",fields, datas));
108 }
109 }
110 return temp;
111 }
112
113 public string FormatEncoding(string lineData)
114 {
115 string t = "";
116 Regex reg = new Regex("[\u4e00-\u9fa5]+");
117 MatchCollection matches = reg.Matches(lineData);
118 for(int i = 0; i < matches.Count; i++ ) {
119 t += string.Format("{0},",matches.Value);
120 }
121 return t.TrimEnd(',');
122 }
123 }
124 }
125
下面是调用：
日志文件转换
1 namespace Log2Sql
2 {
3 class Program
4 {
5 static void Main(string[] args)
6 {
7 string inputDirectory = @"g:\iislog\logs";
8 string outputFileName = @"g:\iislog\log.txt";
9 string errorFileName = @"g:\iislog\error.txt";
10 try{
11 FormatLog log = new FormatLog(inputDirectory, outputFileName, errorFileName);
12 log.Execute();
13 //log.WriteInvalidFields();
14 }
15 catch(Exception e){
16 Console.Write(e.Message);
17 }
18 Console.Write("success");
19 Console.Read();
20 }
21 }
22 }
页:
[1]