blocks|key|3815837|text|我通常使用的处理BULK+INSERT和不规则数据的方法是将传入数据放入一个只有一个VARCHAR(MAX)列的临时临时表中。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3815838|一旦有了它，我就可以使用更灵活的决策工具，如SQL查询和字符串函数来决定从临时表中选择哪些行并将其带入我的主表中。这也很有帮助，因为BULK+INSERT对特定文件失败的原因和原因的解释可能令人抓狂。|3815839|我能想到的唯一其他选择是在进行批量插入之前，使用预上载脚本来修剪注释和其他不符合表格条件的行。|3815840|entityMap^0|8|B|16|C|0|1U|B|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@$9|M|A|N|B|C]|$9|O|A|P|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|T|8|@]|D|@]|E|$]]|$1|J|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|K|$]]

The approach I generally use with <code>BULK INSERT</code> and irregular data is to push the incoming data into a temporary staging table with a single <code>VARCHAR(MAX)</code> column.

Once it's in there, I can use more flexible decision-making tools like SQL queries and string functions to decide which rows I want to select out of the staging table and bring into my main tables. This is also helpful because <code>BULK INSERT</code> can be maddeningly cryptic about the why and how of why it fails on a specific file.

The only other option I can think of is using pre-upload scripting to trim comments and other lines that don't fit your tabular criteria before you do your bulk insert.

blocks|key|3815863|text|我推荐使用logparser.exe。LogParser有一些漂亮的neat+capabilities+on+its+own，但也可以用来格式化IIS日志，以便由SQL+Server正确地导入。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3815864|entityMap|0|LINK|mutability|MUTABLE|url|http://www.microsoft.com/en-us/download/details.aspx?id=24659|1|http://mlichtenberg.wordpress.com/2011/02/03/log-parser-rocks-more-than-50-examples/^0|5|D|0|Y|S|1|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]|$A|R|B|S|1|T]]|C|$]]|$1|D|3|-4|5|6|7|U|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]|L|$5|G|H|I|C|$J|M]]]]

I recommend using <a href="http://www.microsoft.com/en-us/download/details.aspx?id=24659" rel="nofollow">logparser.exe</a> instead. LogParser has some pretty <a href="http://mlichtenberg.wordpress.com/2011/02/03/log-parser-rocks-more-than-50-examples/" rel="nofollow">neat capabilities on its own</a>, but it can also be used to format the IIS log to be properly imported by SQL Server.

blocks|key|3727007|text|微软有一个名为"PrepWebLog“http://support.microsoft.com/kb/296093的工具，它可以剔除这些散列/井号字符，但是我现在运行它(使用PowerShell脚本处理多个文件)，发现它的性能慢得让人无法忍受。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3727008|我想如果我写一个C#程序(或者甚至是宏)会更快。|3727009|3727010|更新:+PrepWebLog刚刚在我身上崩溃了。我会避免的。|3727011|3727012|更新#2，我查看了PowerShell的Get-Content和Set-Content命令，但不喜欢其语法和可能的性能。所以我写了这个小的C#控制台应用：|3727013|++++++++if+(args.Length+==+2)
++++++++{
++++++++++++string+path+=+args[0];
++++++++++++string+outPath+=+args[1];

++++++++++++Regex+hashString+=+new+Regex("%5E#.%2B\r\n",+RegexOptions.Multiline+%7C+RegexOptions.Compiled);
++++++++++++foreach+(string+file+in+Directory.GetFiles(path,+"*.log"))
++++++++++++{
++++++++++++++++string+data;
++++++++++++++++using+(StreamReader+sr+=+new+StreamReader(file))
++++++++++++++++{
++++++++++++++++++++data+=+sr.ReadToEnd();
++++++++++++++++}

++++++++++++++++string+output+=+hashString.Replace(data,+string.Empty);
++++++++++++++++using+(StreamWriter+sw+=+new+StreamWriter(Path.Combine(outPath,+new+FileInfo(file).Name),+false))
++++++++++++++++{
++++++++++++++++++++sw.Write(output);
++++++++++++++++}
++++++++++++}
++++++++}
++++++++else
++++++++{
++++++++++++Console.WriteLine("Source+and+Destination+Log+Path+required+or+too+many+arguments");
++++++++}|code-block|syntax|javascript|3727014|这很快。|3727015|3727016|entityMap|0|LINK|mutability|MUTABLE|url|http://support.microsoft.com/kb/296093^0|J|12|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|11|8|@]|9|@$A|12|B|13|1|14]]|C|$]]|$1|D|3|E|5|6|7|15|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|16|8|@]|9|@]|C|$]]|$1|G|3|H|5|6|7|17|8|@]|9|@]|C|$]]|$1|I|3|-4|5|6|7|18|8|@]|9|@]|C|$]]|$1|J|3|K|5|6|7|19|8|@]|9|@]|C|$]]|$1|L|3|M|5|N|7|1A|8|@]|9|@]|C|$O|P]]|$1|Q|3|R|5|6|7|1B|8|@]|9|@]|C|$]]|$1|S|3|-4|5|6|7|1C|8|@]|9|@]|C|$]]|$1|T|3|-4|5|6|7|1D|8|@]|9|@]|C|$]]]|U|$V|$5|W|X|Y|C|$Z|10]]]]

Microsoft has a tool called "PrepWebLog" <a href="http://support.microsoft.com/kb/296093" rel="nofollow">http://support.microsoft.com/kb/296093</a> - which strips-out these hash/pound characters, however I'm running it now (using a PowerShell script for multiple files) and am finding its performance intolerably slow.

I think it'd be faster if I wrote a C# program (or maybe even a macro).

<hr>

Update: PrepWebLog just crashed on me. I'd avoid it. 

<hr>

Update #2, I looked at PowerShell's Get-Content and Set-Content commands but didn't like the syntax and possible performance. So I wrote this little C# console app:

<pre><code> if (args.Length == 2)
 {
 string path = args[0];
 string outPath = args[1];

 Regex hashString = new Regex("^#.+\r\n", RegexOptions.Multiline | RegexOptions.Compiled);
 foreach (string file in Directory.GetFiles(path, "*.log"))
 {
 string data;
 using (StreamReader sr = new StreamReader(file))
 {
 data = sr.ReadToEnd();
 }

 string output = hashString.Replace(data, string.Empty);
 using (StreamWriter sw = new StreamWriter(Path.Combine(outPath, new FileInfo(file).Name), false))
 {
 sw.Write(output);
 }
 }
 }
 else
 {
 Console.WriteLine("Source and Destination Log Path required or too many arguments");
 }
</code></pre>

It's pretty quick.

<hr>

blocks|key|2099623|text|根据PeterX写的内容，我修改了应用程序以处理大型日志文件，因为任何足够大的文件都会产生内存不足异常。此外，由于我们只对一行的第一个字符是否以散列开头感兴趣，因此我们可以只对读取操作使用StartsWith()方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2099624|class+Program
{
++++static+void+Main(string[]+args)
++++{
++++++++if+(args.Length+==+2)
++++++++{
++++++++++++string+path+=+args[0];
++++++++++++string+outPath+=+args[1];
++++++++++++string+line;

++++++++++++foreach+(string+file+in+Directory.GetFiles(path,+"*.log"))
++++++++++++{
++++++++++++++++using+(StreamReader+sr+=+new+StreamReader(file))
++++++++++++++++{
++++++++++++++++++++using+(StreamWriter+sw+=+new+StreamWriter(Path.Combine(outPath,+new+FileInfo(file).Name),+false))
++++++++++++++++++++{
++++++++++++++++++++++++while+((line+=+sr.ReadLine())+!=+null)
++++++++++++++++++++++++{
++++++++++++++++++++++++++++if(!line.StartsWith("#"))
++++++++++++++++++++++++++++{
++++++++++++++++++++++++++++++++sw.WriteLine(line);
++++++++++++++++++++++++++++}
++++++++++++++++++++++++}++++++++++++++++++++++++++
++++++++++++++++++++}
++++++++++++++++}

++++++++++++}
++++++++}
++++++++else
++++++++{
++++++++++++Console.WriteLine("Source+and+Destination+Log+Path+required+or+too+many+arguments");
++++++++}
++++}
}|code-block|syntax|javascript|2099625|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Following up on what PeterX wrote, I modified the application to handle large log files since anything sufficiently large would create an out-of-memory exception. Also, since we're only interested in whether or not the first character of a line starts with a hash, we can just use StartsWith() method on the read operation.

<pre><code>class Program
{
 static void Main(string[] args)
 {
 if (args.Length == 2)
 {
 string path = args[0];
 string outPath = args[1];
 string line;

 foreach (string file in Directory.GetFiles(path, "*.log"))
 {
 using (StreamReader sr = new StreamReader(file))
 {
 using (StreamWriter sw = new StreamWriter(Path.Combine(outPath, new FileInfo(file).Name), false))
 {
 while ((line = sr.ReadLine()) != null)
 {
 if(!line.StartsWith("#"))
 {
 sw.WriteLine(line);
 }
 } 
 }
 }

 }
 }
 else
 {
 Console.WriteLine("Source and Destination Log Path required or too many arguments");
 }
 }
}
</code></pre>

I want to import my IIS logs into SQL for reporting using Bulk Insert, but the comment lines - the ones that start with a # - cause a problem becasue those lines do not have the same number f fields as the data lines.

If I manually deleted the comments, I can perform a bulk insert.

Is there a way to perform a bulk insert while excluding lines based on a match such as : any line that beings with a "#".

Thanks.

Excluding Content From SQL Bulk Insert

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我希望将IIS日志导入到SQL中，以便使用Bulk Insert进行报告，但是注释行(以#开头的那些行)会导致问题，因为这些行的字段数与数据行的字段数不同。如果我手动删除注释，我就可以执行大容量插入。是否有一种方法可以在排除基于匹配的行的同时执行批量插入，例如:任何以"#“开头的行。谢谢。

问从SQL大容量插入中排除内容
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从SQL大容量插入中排除内容EN