?
Solved

Enumerate text from txt file into SQL database

Posted on 2010-01-11
7
Medium Priority
?
321 Views
Last Modified: 2012-05-08
Hi Experts,

I'm looking to extract some information from a txt file.  The information is not comma seperated but each field (for want of a better phrase) is seperated by spaces.  I need to look through each line in the text file looking for a match to a field in an sql database.  Once I see a match, then get the date/time from the line in the text file and put it into another field in the database.  I then need to do it all over again for a different match (effectively the next name in the sql database) and put that info into the sql database under that name etc etc.

The database is MySQL and has a number of tables in it.  The relevant tables are dvrinfo which has a field in called FTPName  The FTPName field is what I want to search the attached file for.  When I see the match I want to get the date and time for the 230 Logged on line and put the date time in the MySQL dvrinfo table in the field lastlogon.  

I need this to happen every 4 hours and then do it again the following day with the next log file.  Fun eh?

Is there anyone out there how can point me in the right direction for code, or utils/apps or anything.

Much appreciated.
Carl.
fzs-2010-01-11.log
0
Comment
Question by:CarlCCTVSI
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26288881
try:

You may need to modify 1 or 2 things for your environment.

Code is C# - Visual Studio 2008 (Console application)

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;

namespace sqlUpdater
{
    class Program
    {
        static void Main(string[] args)
        {
            UploadFileToSql(@"c:\logfile.log", "TableName", "sql.mydomain.com", "Pubs", null, null, true);
        }

        public static bool UploadFileToSql (string fileName, string insertTable, string serverAddress, string databaseName, string Uid, string Pwd, bool trustedConnection)
        {
            if (!File.Exists(fileName)) return false;


            #region Read the file
            var sqlCommands = new StringBuilder();
            using (var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                using (var sr = new StreamReader(fs))
                {
                    var buffer = sr.ReadLine().Split(' ');

                    var values = string.Empty;

                    foreach (var field in buffer)
                    {
                        values = "'" + field + "', "; //Here I am making everythin a string ... you may need to change this
                    }
                        

                    if (values.EndsWith("', ")) 
                        values = values.Substring(0, values.Length - 2);

                    //here I assum all fields in txtfile mach table struct ... you may need to change
                    sqlCommands.Append("INSERT INTO [" + insertTable + "] Values (" + values + ")");
                }
            }
            #endregion

            string connectionString = trustedConnection ?
                "Data Source=" + serverAddress + ";Initial Catalog=" + databaseName + ";Trusted_Connection=Yes;" :
                "Data Source=" + serverAddress + ";Initial Catalog=" + databaseName + ";UID=" + Uid + ";PWD=" + Pwd + "";

            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();

            if (connection.State !=ConnectionState.Open)
            {
                throw new Exception("Unable to connect to SQL Server");
            }

            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = connection;
                cmd.CommandText = sqlCommands.ToString();
                cmd.CommandType = CommandType.Text;
                cmd.EndExecuteNonQuery();
            }

            return true;

        }
    }
}

Open in new window

0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26288888
hmmm ... did not see the attached file ... let me make a quick mod.
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26289091
try again :)

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;

namespace sqlUpdater
{
    class Program
    {
        static void Main(string[] args)
        {
            UploadFileToSql(@"c:\fzs-2010-01-11.log", "TableName", "sql.mydomain.com", "Pubs", null, null, true);
        }

        public static bool UploadFileToSql (string fileName, string insertTable, string serverAddress, string databaseName, string Uid, string Pwd, bool trustedConnection)
        {
            if (!File.Exists(fileName)) return false;

            #region Read the file
            var sqlCommands = new StringBuilder();
            using (var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                using (var sr = new StreamReader(fs))
                {
                    var buffer = sr.ReadLine();

                    while (buffer != null)
                    {
                        if (buffer == string.Empty)
                        {
                            buffer = sr.ReadLine();
                            continue;
                        }
                        var sqlVlaues = new StringBuilder();

                        int iStart = 0;
                        int iIndex = 0;

                        //OK making this very specific to this file. - new files mod this section
                                               
                        //First Field In text box
                        iIndex = buffer.IndexOf(" ");
                        var fieldID = (buffer.Substring(iStart, iIndex+1)).Replace("(", "").Replace(")", "");
                        sqlVlaues.Append(fieldID + ", " );
                        iStart = iIndex + 1;/* + length of previous string ie: length of )*/

                        // ... and so on
                        iIndex = buffer.IndexOf(" - ", iStart+1 );
                        var fieldDateTime = Convert.ToDateTime(buffer.Substring(iStart, iIndex-iStart));
                        sqlVlaues.Append("Cast ('" + fieldDateTime.ToString("dd MMM yyyy hh:mm:ss") + "' as DateTime), ");
                        iStart = iIndex + 3;

                        var nextChar = ((buffer.Substring(iStart, 1)).Equals("(")) ? ")" : " "; // if the username is there it not surrounded with brackets

                        iIndex = buffer.IndexOf(" ", iStart + 1);
                        var fieldUser = (buffer.Substring(iStart, iIndex - iStart + 1)).Replace("(", "").Replace(")", "");
                        sqlVlaues.Append("N'" + fieldUser + "', ");
                        iStart = iIndex+1;

                        iIndex = buffer.IndexOf(")", iStart + 1);
                        var fieldIp = (buffer.Substring(iStart, iIndex - iStart + 1)).Replace("(", "").Replace(")", "");
                        sqlVlaues.Append("N'" + fieldIp + "', ");
                        iStart = iIndex+1;

                        iIndex = buffer.IndexOf(">", iStart);
                        var fieldMessage = buffer.Substring(iIndex+1, buffer.Length - iIndex-1);
                        sqlVlaues.Append("N'" + fieldMessage + "'");                                      

                        //here I assum all fields in txtfile mach table struct ... you may need to change
                        sqlCommands.Append("INSERT INTO [" + insertTable + "] Values (" + sqlVlaues.ToString() + ")");
                        sqlCommands.Append("GO");
                        buffer = sr.ReadLine();
                    }
                }
            }
            #endregion

            string connectionString = trustedConnection ?
                "Data Source=" + serverAddress + ";Initial Catalog=" + databaseName + ";Trusted_Connection=Yes;" :
                "Data Source=" + serverAddress + ";Initial Catalog=" + databaseName + ";UID=" + Uid + ";PWD=" + Pwd + "";

            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();

            if (connection.State !=ConnectionState.Open)
            {
                throw new Exception("Unable to connect to SQL Server");
            }

            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = connection;
                cmd.CommandText = sqlCommands.ToString();
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
            }

            return true;

        }
    }
}

Open in new window

0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:CarlCCTVSI
ID: 26290845
Hi Zadeveloper

Wow, I wasn't expecting the code to do the job, I'll give that a go today and let you know the results.  Many thanks, I'll come back to you.
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26412049
any news on this one.
0
 

Author Comment

by:CarlCCTVSI
ID: 26428900
zadeveloper,

you are a star, I shall try it this evening and let you know.

0
 
LVL 13

Accepted Solution

by:
zadeveloper earned 2000 total points
ID: 26432089
cool, I hope it works out :)
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question