Parse files in directory/insert in database
Here 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开发者_如何学Go_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
Directory.GetFiles(path, searchPattern) will get you all the files in the folder. Set the searchPattern to *.txt.
This will return the list of files currently in the directory so you can be sure of only processing each one once. If more files could be added later simply move the files to a "processed" folder once the import is complete.
You'll then need to read each file a line at a time until you've read "Feat. Type..." then read each subsequent line, splitting the content and adding to the database.
You can read a line from a file by using StreamReader.ReadLine().
// Create an instance of StreamReader to read from a file.
// The using statement also closes the StreamReader.
using (StreamReader sr = new StreamReader("TestFile.txt"))
{
string line;
bool processLine = false;
// Read lines from the file until the end of the file is reached.
while ((line = sr.ReadLine()) != null)
{
if (processLine)
{
// Your processing here
}
// Check to see if we need to process the next lines
if (line.StartsWith("Feat. Type,"))
{
processLine = true;
}
}
加载中,请稍侯......
精彩评论