Parse filename, insert to SQL

Question

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

UPDATE: I Tried this, as suggested below, but no go.. the column in the db just fills with random words from the text files.

    string RNumber = Regex.Match(filename, @"(R.*)_.*_\.txt").Groups[1].Value;
 insertCommand.Parameters.Add(new SQLiteParameter("@FileName", RNumber));
 insertCommand.CommandText = @" INSERT INTO Files (FileName) VALUES (@Filename)

Regex.Match(filename, @"(R.*)_.*_\.txt").Groups[1].Value; will parse whatever string is in the variable "filename", if what is being inserted into the database is "random words from the text files" then that is what is in the variable filename. The problem is not the code you have posted below your update, but whtever code sets the value of filename. — Coding Flow
– Coding Flow, Commented May 24, 2010 at 21:23
That makes sense, I need to put the variable that I and using with the Directory.GetFiles method in place of 'filename'. I was trying to use the \\mynetwrok\location in place of it.. :) thanks! — Jake Sankey
– Jake Sankey, Commented May 24, 2010 at 21:38

Coding Flow · Accepted Answer · 2010-05-24 16:36:31Z

1

IF the pattern is always xxxxx_anythingelse.txt then you could parse out the xxx with:

strFileName.Substring(0,strFileName.IndexOf("_"));

answered May 24, 2010 at 16:36

Coding Flow

21.9k6 gold badges68 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DevelopingChris · Accepted Answer · 2010-05-24 16:36:50Z

0

Here's the way given the filename to get the first part before the "_".

string RNumber = Regex.Match(filename, @"(R.*)_.*_\.txt").Groups[1].Value;

Then you can put it wherever you want to put it.

answered May 24, 2010 at 16:36

DevelopingChris

41k30 gold badges90 silver badges119 bronze badges

4 Comments

Jake Sankey Over a year ago

Ok, I tried this... string RNumber = Regex.Match(filename, @"(R.*)_.*_\.txt").Groups[1].Value; insertCommand.Parameters.Add(new SQLiteParameter("@FileName", RNumber)); insertCommand.CommandText = @" INSERT INTO Files (FileName) VALUES (@Filename) But no go.... The database just fills up that column with random words from the files themselves. any help?

Coding Flow Over a year ago

The database does NOT "just fill up that column with random words from the files themselves", YOU are inserting random words into the database by parsing the wrong thing, ie the file content and not the file name.

Jake Sankey Over a year ago

doesn't string RNumber = Regex.Match(filename, @"(R.*)_.*_\.txt").Groups[1].Value; parse the file names? I guess I don't understand what you mean? Thanks for the help!

DevelopingChris Over a year ago

This returns the part with an r in it at the beginning of the file name, if you want the entire filename, then just put in filename. I thought you wanted the r-number by itself and the whole file name to put it in a particular section of the db.

kmatyaszek · Accepted Answer · 2012-11-14 06:00:52Z

0

strFileName.Split('_').First()

use System.LINQ in the Namespace...this will do the trick....

edited Nov 14, 2012 at 6:00

kmatyaszek

19.3k9 gold badges62 silver badges66 bronze badges

answered May 24, 2010 at 16:39

Manoj Talreja

8733 gold badges12 silver badges18 bronze badges

Collectives™ on Stack Overflow

Parse filename, insert to SQL

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related