解析目录中的文件/插入数据库 [英] Parse files in directory/insert in database
本文介绍了解析目录中的文件/插入数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这是我的难题...我有一个目录，里面全是 .txt 逗号分隔的文件，排列如下所示.我想要做的是将其中的每一个导入到 SQL 或 SQLite 数据库中，将每个附加到最后一个下面.(1 个表)...我对 C# 或 VB 脚本持开放态度，只是不知道如何实现这一点.我只想提取和导入从Feat.txt"开始的数据.类型，壮举.名称等行.
这些存储在我的网络驱动器上的 \mynetwork\directory\stats 文件夹中.理想情况下，我将能够添加功能，使软件/脚本知道一旦它已经这样做了，就不要将文件重新添加到数据库中.
感谢任何指导或提示！
$$ SAMPLE=$$ 固定装置=-$$ 操作员=-$$ 检查过程=CMM #4$$ 过程操作=-$$ 过程序列=-$$ 试用=-壮举.类型，壮举.名称、值、实际值、名义值、偏差、Tol-、Tol+、超出 Tol.、注释点,_FF_PLN_A_1,X,-17.445,-17.445,0.000,-999.000,999.000,,点,_FF_PLN_A_1,Y,-195.502,-195.502,0.000,-999.000,999.000,,点,_FF_PLN_A_1,Z,32.867,33.500,-0.633,-0.800,0.800,,点,_FF_PLN_A_2,X,-73.908,-73.908,0.000,-999.000,999.000,,点,_FF_PLN_A_2,Y,-157.957,-157.957,0.000,-999.000,999.000,,点,_FF_PLN_A_2,Z,32.792,33.500,-0.708,-0.800,0.800,,点,_FF_PLN_A_3,X,-100.180,-100.180,0.000,-999.000,999.000,,点,_FF_PLN_A_3,Y,-142.797,-142.797,0.000,-999.000,999.000,,点,_FF_PLN_A_3,Z,32.768,33.500,-0.732,-0.800,0.800,,点,_FF_PLN_A_4,X,-160.945,-160.945,0.000,-999.000,999.000,,点,_FF_PLN_A_4,Y,-112.705,-112.705,0.000,-999.000,999.000,,点,_FF_PLN_A_4,Z,32.719,33.500,-0.781,-0.800,0.800,,点,_FF_PLN_A_5,X,-158.096,-158.096,0.000,-999.000,999.000,,点,_FF_PLN_A_5,Y,-73.821,-73.821,0.000,-999.000,999.000,,点,_FF_PLN_A_5,Z,32.756,33.500,-0.744,-0.800,0.800,,点,_FF_PLN_A_6,X,-195.670,-195.670,0.000,-999.000,999.000,,点,_FF_PLN_A_6,Y,-17.375,-17.375,0.000,-999.000,999.000,,点,_FF_PLN_A_6,Z,32.767,33.500,-0.733,-0.800,0.800,,点,_FF_PLN_A_7,X,-173.759,-173.759,0.000,-999.000,999.000,,点,_FF_PLN_A_7,Y,14.876,14.876,0.000,-999.000,999.000,, 解决方案
using System;使用 System.Data;使用 System.Data.SQLite；使用 System.IO；命名空间 CSV 导入{内部类程序{私有静态无效主(字符串 [] args){使用 (SQLiteConnection con = new SQLiteConnection("data source=data.db3")){if (!File.Exists("data.db3")){con.Open();使用 (SQLiteCommand cmd = con.CreateCommand()){cmd.CommandText =@"创建表 [导入] ([RowId] 整数 PRIMARY KEY AUTOINCREMENT NOT NULL,[FeatType] varchar,[FeatName] varchar，[值] varchar,[实际]十进制，[名义] 十进制，[Dev] 十进制，[TolMin] 十进制，[TolPlus] 十进制，[OutOfTol] 十进制，[注释] nvarchar);";cmd.ExecuteNonQuery();}关闭()；}con.Open();使用 (SQLiteCommand insertCommand = con.CreateCommand()){插入命令.命令文本 =@"INSERT INTO 导入(FeatType、FeatName、Value、Actual、Nominal、Dev、TolMin、TolPlus、OutOfTol、Comment)值(@FeatType、@FeatName、@Value、@Actual、@Nominal、@Dev、@TolMin、@TolPlus、@OutOfTol、@Comment)；";insertCommand.Parameters.Add(new SQLiteParameter("@FeatType", DbType.String));insertCommand.Parameters.Add(new SQLiteParameter("@FeatName", DbType.String));insertCommand.Parameters.Add(new SQLiteParameter("@Value", DbType.String));insertCommand.Parameters.Add(new SQLiteParameter("@Actual", DbType.Decimal));insertCommand.Parameters.Add(new SQLiteParameter("@Nominal", DbType.Decimal));insertCommand.Parameters.Add(new SQLiteParameter("@Dev", DbType.Decimal));insertCommand.Parameters.Add(new SQLiteParameter("@TolMin", DbType.Decimal));insertCommand.Parameters.Add(new SQLiteParameter("@TolPlus", DbType.Decimal));insertCommand.Parameters.Add(new SQLiteParameter("@OutOfTol", DbType.Decimal));insertCommand.Parameters.Add(new SQLiteParameter("@Comment", DbType.String));string[] files = Directory.GetFiles(Environment.CurrentDirectory, "TextFile*.*");foreach(文件中的字符串文件){string[] 行 = File.ReadAllLines(file);布尔解析=假；foreach(行中的字符串 tmpLine){字符串行 = tmpLine.Trim();if (!parse && line.StartsWith("Feat.Type,")){解析 = 真；继续;}if (!parse || string.IsNullOrEmpty(line)){继续;}foreach(insertCommand.Parameters 中的 SQLiteParameter 参数){参数值=空；}string[] values = line.Split(new[] {','});for (int i = 0; i < values.Length - 1; i++){SQLiteParameter param = insertCommand.Parameters[i];if (param.DbType == DbType.Decimal){十进制值；param.Value = decimal.TryParse(values[i], out value) ?值:0；}别的{param.Value = values[i];}}insertCommand.ExecuteNonQuery();}}}关闭()；}}}}结果:
<前>1 点 _FF_PLN_A_1 X -17.445 -17.445 0 -999 999 0 NULL2 点 _FF_PLN_A_1 Y -195.502 -195.502 0 -999 999 0 NULL3 点 _FF_PLN_A_1 Z 32.867 33.5 -0.633 -0.8 0.8 0 NULL4 点 _FF_PLN_A_2 X -73.908 -73.908 0 -999 999 0 NULL5 点 _FF_PLN_A_2 Y -157.957 -157.957 0 -999 999 0 NULL6 点 _FF_PLN_A_2 Z 32.792 33.5 -0.708 -0.8 0.8 0 NULL7 点 _FF_PLN_A_3 X -100.18 -100.18 0 -999 999 0 NULL8 点 _FF_PLN_A_3 Y -142.797 -142.797 0 -999 999 0 NULL9 点 _FF_PLN_A_3 Z 32.768 33.5 -0.732 -0.8 0.8 0 NULL10 点 _FF_PLN_A_4 X -160.945 -160.945 0 -999 999 0 NULL11 点 _FF_PLN_A_4 Y -112.705 -112.705 0 -999 999 0 NULL12 点 _FF_PLN_A_4 Z 32.719 33.5 -0.781 -0.8 0.8 0 NULL13 点 _FF_PLN_A_5 X -158.096 -158.096 0 -999 999 0 NULL14 点 _FF_PLN_A_5 Y -73.821 -73.821 0 -999 999 0 NULL15 点 _FF_PLN_A_5 Z 32.756 33.5 -0.744 -0.8 0.8 0 NULL16 点 _FF_PLN_A_6 X -195.67 -195.67 0 -999 999 0 NULL17 点 _FF_PLN_A_6 Y -17.375 -17.375 0 -999 999 0 NULL18 点 _FF_PLN_A_6 Z 32.767 33.5 -0.733 -0.8 0.8 0 NULL19 点 _FF_PLN_A_7 X -173.759 -173.759 0 -999 999 0 NULL20 点 _FF_PLN_A_1 X -17.445 -17.445 0 -999 999 0 NULL21 点 _FF_PLN_A_1 Y -195.502 -195.502 0 -999 999 0 NULL22 点 _FF_PLN_A_1 Z 32.867 33.5 -0.633 -0.8 0.8 0 NULL23 点 _FF_PLN_A_2 X -73.908 -73.908 0 -999 999 0 NULL24 点 _FF_PLN_A_2 Y -157.957 -157.957 0 -999 999 0 NULL25 点 _FF_PLN_A_2 Z 32.792 33.5 -0.708 -0.8 0.8 0 NULL26 点 _FF_PLN_A_3 X -100.18 -100.18 0 -999 999 0 NULL27 点 _FF_PLN_A_3 Y -142.797 -142.797 0 -999 999 0 NULL28 点 _FF_PLN_A_3 Z 32.768 33.5 -0.732 -0.8 0.8 0 NULL29 点 _FF_PLN_A_4 X -160.945 -160.945 0 -999 999 0 NULL30 点 _FF_PLN_A_4 Y -112.705 -112.705 0 -999 999 0 NULL31 点 _FF_PLN_A_4 Z 32.719 33.5 -0.781 -0.8 0.8 0 NULL32 点 _FF_PLN_A_5 X -158.096 -158.096 0 -999 999 0 NULL33 点 _FF_PLN_A_5 Y -73.821 -73.821 0 -999 999 0 NULL34 点 _FF_PLN_A_5 Z 32.756 33.5 -0.744 -0.8 0.8 0 NULL35 点 _FF_PLN_A_6 X -195.67 -195.67 0 -999 999 0 NULL36 点 _FF_PLN_A_6 Y -17.375 -17.375 0 -999 999 0 NULL37 点 _FF_PLN_A_6 Z 32.767 33.5 -0.733 -0.8 0.8 0 NULL38 点 _FF_PLN_A_7 X -173.759 -173.759 0 -999 999 0 NULLNULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULLHere is my dillema... I have a directory full of .txt comma delimited files arranged as shown below. What I want to do is to import each of these into a SQL or SQLite database, appending each one below the last. (1 table)... I am open to C# or VB scripting and just not sure how to accomplish this. I want to only extract and import the data starting BELOW the 'Feat. Type,Feat. Name, etc' line.
These are stored in a \mynetwork\directory\stats folder on my network drive. Ideally I will be able to add functionality that will make the software/script know not to re-add the file to the database once it has already done so as well.
Any guidance or tips is appreciated!
$$ SAMPLE=
$$ FIXTURE=-
$$ OPERATOR=-
$$ INSPECTION PROCESS=CMM #4
$$ PROCESS OPERATION=-
$$ PROCESS SEQUENCE=-
$$ TRIAL=-
Feat. Type,Feat. Name,Value,Actual,Nominal,Dev.,Tol-,Tol+,Out of Tol.,Comment
Point,_FF_PLN_A_1,X,-17.445,-17.445,0.000,-999.000,999.000,,
Point,_FF_PLN_A_1,Y,-195.502,-195.502,0.000,-999.000,999.000,,
Point,_FF_PLN_A_1,Z,32.867,33.500,-0.633,-0.800,0.800,,
Point,_FF_PLN_A_2,X,-73.908,-73.908,0.000,-999.000,999.000,,
Point,_FF_PLN_A_2,Y,-157.957,-157.957,0.000,-999.000,999.000,,
Point,_FF_PLN_A_2,Z,32.792,33.500,-0.708,-0.800,0.800,,
Point,_FF_PLN_A_3,X,-100.180,-100.180,0.000,-999.000,999.000,,
Point,_FF_PLN_A_3,Y,-142.797,-142.797,0.000,-999.000,999.000,,
Point,_FF_PLN_A_3,Z,32.768,33.500,-0.732,-0.800,0.800,,
Point,_FF_PLN_A_4,X,-160.945,-160.945,0.000,-999.000,999.000,,
Point,_FF_PLN_A_4,Y,-112.705,-112.705,0.000,-999.000,999.000,,
Point,_FF_PLN_A_4,Z,32.719,33.500,-0.781,-0.800,0.800,,
Point,_FF_PLN_A_5,X,-158.096,-158.096,0.000,-999.000,999.000,,
Point,_FF_PLN_A_5,Y,-73.821,-73.821,0.000,-999.000,999.000,,
Point,_FF_PLN_A_5,Z,32.756,33.500,-0.744,-0.800,0.800,,
Point,_FF_PLN_A_6,X,-195.670,-195.670,0.000,-999.000,999.000,,
Point,_FF_PLN_A_6,Y,-17.375,-17.375,0.000,-999.000,999.000,,
Point,_FF_PLN_A_6,Z,32.767,33.500,-0.733,-0.800,0.800,,
Point,_FF_PLN_A_7,X,-173.759,-173.759,0.000,-999.000,999.000,,
Point,_FF_PLN_A_7,Y,14.876,14.876,0.000,-999.000,999.000,,
解决方案
using System;
using System.Data;
using System.Data.SQLite;
using System.IO;
namespace CSVImport
{
internal class Program
{
private static void Main(string[] args)
{
using (SQLiteConnection con = new SQLiteConnection("data source=data.db3"))
{
if (!File.Exists("data.db3"))
{
con.Open();
using (SQLiteCommand cmd = con.CreateCommand())
{
cmd.CommandText =
@"
CREATE TABLE [Import] (
[RowId] integer PRIMARY KEY AUTOINCREMENT NOT NULL,
[FeatType] varchar,
[FeatName] varchar,
[Value] varchar,
[Actual] decimal,
[Nominal] decimal,
[Dev] decimal,
[TolMin] decimal,
[TolPlus] decimal,
[OutOfTol] decimal,
[Comment] nvarchar);";
cmd.ExecuteNonQuery();
}
con.Close();
}
con.Open();
using (SQLiteCommand insertCommand = con.CreateCommand())
{
insertCommand.CommandText =
@"
INSERT INTO Import (FeatType, FeatName, Value, Actual, Nominal, Dev, TolMin, TolPlus, OutOfTol, Comment)
VALUES (@FeatType, @FeatName, @Value, @Actual, @Nominal, @Dev, @TolMin, @TolPlus, @OutOfTol, @Comment);";
insertCommand.Parameters.Add(new SQLiteParameter("@FeatType", DbType.String));
insertCommand.Parameters.Add(new SQLiteParameter("@FeatName", DbType.String));
insertCommand.Parameters.Add(new SQLiteParameter("@Value", DbType.String));
insertCommand.Parameters.Add(new SQLiteParameter("@Actual", DbType.Decimal));
insertCommand.Parameters.Add(new SQLiteParameter("@Nominal", DbType.Decimal));
insertCommand.Parameters.Add(new SQLiteParameter("@Dev", DbType.Decimal));
insertCommand.Parameters.Add(new SQLiteParameter("@TolMin", DbType.Decimal));
insertCommand.Parameters.Add(new SQLiteParameter("@TolPlus", DbType.Decimal));
insertCommand.Parameters.Add(new SQLiteParameter("@OutOfTol", DbType.Decimal));
insertCommand.Parameters.Add(new SQLiteParameter("@Comment", DbType.String));
string[] files = Directory.GetFiles(Environment.CurrentDirectory, "TextFile*.*");
foreach (string file in files)
{
string[] lines = File.ReadAllLines(file);
bool parse = false;
foreach (string tmpLine in lines)
{
string line = tmpLine.Trim();
if (!parse && line.StartsWith("Feat. Type,"))
{
parse = true;
continue;
}
if (!parse || string.IsNullOrEmpty(line))
{
continue;
}
foreach (SQLiteParameter parameter in insertCommand.Parameters)
{
parameter.Value = null;
}
string[] values = line.Split(new[] {','});
for (int i = 0; i < values.Length - 1; i++)
{
SQLiteParameter param = insertCommand.Parameters[i];
if (param.DbType == DbType.Decimal)
{
decimal value;
param.Value = decimal.TryParse(values[i], out value) ? value : 0;
}
else
{
param.Value = values[i];
}
}
insertCommand.ExecuteNonQuery();
}
}
}
con.Close();
}
}
}
}
results:
1 Point _FF_PLN_A_1 X -17.445 -17.445 0 -999 999 0 NULL 2 Point _FF_PLN_A_1 Y -195.502 -195.502 0 -999 999 0 NULL 3 Point _FF_PLN_A_1 Z 32.867 33.5 -0.633 -0.8 0.8 0 NULL 4 Point _FF_PLN_A_2 X -73.908 -73.908 0 -999 999 0 NULL 5 Point _FF_PLN_A_2 Y -157.957 -157.957 0 -999 999 0 NULL 6 Point _FF_PLN_A_2 Z 32.792 33.5 -0.708 -0.8 0.8 0 NULL 7 Point _FF_PLN_A_3 X -100.18 -100.18 0 -999 999 0 NULL 8 Point _FF_PLN_A_3 Y -142.797 -142.797 0 -999 999 0 NULL 9 Point _FF_PLN_A_3 Z 32.768 33.5 -0.732 -0.8 0.8 0 NULL 10 Point _FF_PLN_A_4 X -160.945 -160.945 0 -999 999 0 NULL 11 Point _FF_PLN_A_4 Y -112.705 -112.705 0 -999 999 0 NULL 12 Point _FF_PLN_A_4 Z 32.719 33.5 -0.781 -0.8 0.8 0 NULL 13 Point _FF_PLN_A_5 X -158.096 -158.096 0 -999 999 0 NULL 14 Point _FF_PLN_A_5 Y -73.821 -73.821 0 -999 999 0 NULL 15 Point _FF_PLN_A_5 Z 32.756 33.5 -0.744 -0.8 0.8 0 NULL 16 Point _FF_PLN_A_6 X -195.67 -195.67 0 -999 999 0 NULL 17 Point _FF_PLN_A_6 Y -17.375 -17.375 0 -999 999 0 NULL 18 Point _FF_PLN_A_6 Z 32.767 33.5 -0.733 -0.8 0.8 0 NULL 19 Point _FF_PLN_A_7 X -173.759 -173.759 0 -999 999 0 NULL 20 Point _FF_PLN_A_1 X -17.445 -17.445 0 -999 999 0 NULL 21 Point _FF_PLN_A_1 Y -195.502 -195.502 0 -999 999 0 NULL 22 Point _FF_PLN_A_1 Z 32.867 33.5 -0.633 -0.8 0.8 0 NULL 23 Point _FF_PLN_A_2 X -73.908 -73.908 0 -999 999 0 NULL 24 Point _FF_PLN_A_2 Y -157.957 -157.957 0 -999 999 0 NULL 25 Point _FF_PLN_A_2 Z 32.792 33.5 -0.708 -0.8 0.8 0 NULL 26 Point _FF_PLN_A_3 X -100.18 -100.18 0 -999 999 0 NULL 27 Point _FF_PLN_A_3 Y -142.797 -142.797 0 -999 999 0 NULL 28 Point _FF_PLN_A_3 Z 32.768 33.5 -0.732 -0.8 0.8 0 NULL 29 Point _FF_PLN_A_4 X -160.945 -160.945 0 -999 999 0 NULL 30 Point _FF_PLN_A_4 Y -112.705 -112.705 0 -999 999 0 NULL 31 Point _FF_PLN_A_4 Z 32.719 33.5 -0.781 -0.8 0.8 0 NULL 32 Point _FF_PLN_A_5 X -158.096 -158.096 0 -999 999 0 NULL 33 Point _FF_PLN_A_5 Y -73.821 -73.821 0 -999 999 0 NULL 34 Point _FF_PLN_A_5 Z 32.756 33.5 -0.744 -0.8 0.8 0 NULL 35 Point _FF_PLN_A_6 X -195.67 -195.67 0 -999 999 0 NULL 36 Point _FF_PLN_A_6 Y -17.375 -17.375 0 -999 999 0 NULL 37 Point _FF_PLN_A_6 Z 32.767 33.5 -0.733 -0.8 0.8 0 NULL 38 Point _FF_PLN_A_7 X -173.759 -173.759 0 -999 999 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
这篇关于解析目录中的文件/插入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
