Parse filename, insert to SQL
- by jakesankey
Thanks to Code Poet, I am now working off of this code to parse all .txt files in a directory and store them in a database. I need a bit more help though... The file names are R303717COMP_148A2075_20100520.txt (the middle section is unique per file). I would like to add something to code so that it can parse out the R303717COMP and put that in the left column of the database such as: (this is not the only R number we have)
R303717COMP data data data
R303717COMP data data data
R303717COMP data data data
etc
Lastly, I would like to have it store each full file name into another table that gets checked so that it doesn't get processed twice.. Any Help is appreciated.
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();
}
}
}
}
