Click here to Skip to main content
15,887,272 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,

First and foremost thank you for any and all help you can offer.

The problem we receive data in TXT format and need to be able to parse out that data into some form of database/repository.

The idea is everyday between _____ and ____ hours a .txt file is created containing data. For example "Newdata20220629.txt" in text format.

However, this data is extremely hard to read and almost impossible to search in it's raw form. The txt file is raw however the first line of the txt file contains the columns for each row of data such as "Name, Date, File number," etc..

The following rows are raw data in the order of those categories. For instance;
John Smith, 6/29/2022, 1234123

any columns without data in the field have a comma but do not contain data such as;

John Smith,, or ,6/29/2022,


So essentially what I'd like to do is create a tool that runs continuously looking for a file in the format of "Newdate(date).txt", and parsing that text based on what I mentioned above then storing it in a user-friendly and searchable database. Personally, I am thinking a SQL database may be the easiest way for this but don't have a clue as to where I should start.

What I have tried:

Brainstorming, It's more of a vague idea at this point.
Posted
Updated 29-Jun-22 9:03am
Comments
PIEBALDconsult 29-Jun-22 19:03pm    
Sooooo... it's a CSV file, no big deal.

Look into SSIS, but even the BCP utility may be enough for you.

1 solution

Hi,

Create Table in SQL database with same column name as present in your Text file which needs to read.

Then Create a C# Console application and place below code in program.cs file

internal class Program
    {
       //Put your database connection string here
       private static string SqlDBConnectionString="";

        private static void SaveDataToDB(DataTable DT)
        {
            using (var bulkCopy = new SqlBulkCopy(SqlDBConnectionString, SqlBulkCopyOptions.KeepIdentity))
            {
                foreach (DataColumn col in DT.Columns)
                {
                    //Here i assume that your Sql table column names matches with the DataTable column names.
                    bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);//(DT.ColumnName,SQLTableColumnName)
                }

                bulkCopy.BulkCopyTimeout = 600;
                bulkCopy.DestinationTableName = "SqlTableName";
                bulkCopy.WriteToServer(DT);
            }
        }


        static void Main()
        {
            var filename = @"D:\PERSONAL\RawData" + DateTime.Now.ToString("ddMMyyyy") + ".txt";

            if (File.Exists(filename))
            {
                var DT = new DataTable();

                IEnumerable<string> TextLines = File.ReadLines(filename);

                if (TextLines != null)
                {
                    var headers = TextLines.First().Split(',');
                    foreach (var header in headers)
                    {
                        DT.Columns.Add(header);
                    }

                    var records = TextLines.Skip(1); //Skip first line as it column names
                    foreach (var record in records)
                    {
                        DT.Rows.Add(record.Split(','));
                    }

                    if (DT.Rows.Count > 0)
                    {
                        SaveDataToDB(DT);
                    }
                }
                else
                {
                    Console.WriteLine("File is empty.");
                }
            }
        }
    }


And lastly schedule application as per your need using windows scheduler.

Hope this helps you.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900