Parse files in directory/insert in database

Question

Hey there, 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_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,,

Answer 1

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;
        }
    }

Answer 2

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