• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

Read matching files in a folder, read one of them and insert the Data in a Table

Helo Everyone!!!

I really have a big Problem and no solution or idea how to solve it. I really need help from Experts like you... I am really not very goog with files and directories and I have no one to help me. So, I need a really good Program for this problem.

In a folder for example in c:\files there will be transfering 4 files with the following names
1.1234_a.txt
2.1234_b.txt
3.1234_c.pdf
4.1234_d.txt

The 4 files will also have the same prefix name before the underscore and the only difference is the letters a,b,c,d after the underscore and the _c file will be a pdf file.
So, when the 4 files are in the folder the program will check if the files belong together(with the name before underscore) and when  all 4 are there (a,b,c.pdf,d) then it should run. It shall also ignore all other files that not belong together. Then the Programm should take the fullpath for each one of the files. I mean the fullpath where the files will be moving.. I will move the files in the folder  c:\files\old_files\current_date. The moving of the files should be the last step (I think).
So, we will take the archiving fullpath of each file and then I want to read the _d file to take some info. This file is a csv file with one line -->

invNo ; GID  ; bla; blabla ;blablabla

From the _d file in need the first 2 rows invNo and GID.
So with the archiving fullpath of the 4 files and the invno and GID that we have read from the _d file, then it should be running a store procedure 4 Times with 3 parameters -->

@GID  = The First row that we have read from the d file
@invNo =The second row that we have read from the d file
@Path  = The archiving fullpath of all 4 files

The stored procedure than -->

INSERT INTO [Demo].[dbo].[ES00Documents]
           ([TableName]
           ,[Caption]
           ,[fGID]
           ,[UNCPath])
     VALUES
           ('IMP_eDocArchive'
           ,@invNo
           ,@GID                                    
          ,@Path)                    
This store procedure will run 4 times for all 4 files. At the end the files should be moved in the archiving  folder.

Please, I know its really a difficult program but I need help.

Thank you for any help!!!

Rena24
0
rena24
Asked:
rena24
  • 5
  • 3
1 Solution
 
magicdlfCommented:
I helped with the first part (archive the files into a folder). Let me know if it works.
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        void handleFiles(string folderPath, string prefix, List<string> suffixes)
        {
            //create a folder with current date
            string newFolderName = folderPath + "\\old_files\\" + DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss");
            System.IO.Directory.CreateDirectory(newFolderName);
            foreach (string suffix in suffixes)
            {
                string srcFilename = string.Format("{0}\\{1}_{2}", folderPath, prefix, suffix);
                string dstFilename = string.Format("{0}\\{1}_{2}", newFolderName, prefix, suffix);
                System.IO.File.Move(srcFilename, dstFilename);
            }
            //then handle the d file
            //TODO
        }

        void checkFile(string folderPath)
        {
            string[] files = System.IO.Directory.GetFiles(folderPath);
            Dictionary<string, List<string>> dict = new Dictionary<string, List<string>>();
            foreach (string file in files)
            {
                string filename = System.IO.Path.GetFileName(file);
                string[] parts = filename.Split(new char[] { '_' });
                //parse the prefix
                if (parts.Length == 2)
                {
                    if (!dict.ContainsKey(parts[0]))
                    {
                        dict[parts[0]] = new List<string>();
                        dict[parts[0]].Add(parts[1]);
                    }
                    else
                    {
                        dict[parts[0]].Add(parts[1]);
                    }
                }
            }
            foreach (string key in dict.Keys)
            {
                //check if a.txt,b.txt,c.pdf,d.txt are all ready.
                List<string> suffixes = dict[key];
                if (suffixes.Contains("a.txt") &&
                    suffixes.Contains("b.txt") &&
                    suffixes.Contains("c.pdf") &&
                    suffixes.Contains("d.txt"))
                {
                    handleFiles(folderPath, key, suffixes);
                }
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //Put this into a timer, and you can have this program monitoring the src folder every several seconds.
            checkFile("c:\\files");
        }
    }
0
 
rena24Author Commented:
Helo magiclf!

Your code is really magic!!! I will try it as soon as possible an I think it will work!! Great!!!!! With so much good code I might will be able to solve the problem. I just need now the procedure that adds the data into database!!!! I will try it now and any help besed on this code to master the second part is welcome!!! Thanks again! Its more than important to me if I solve this problem!

Rena  
0
 
rena24Author Commented:
Helo again magiclf!!!

Your program is perfect and it works!!! Thank you and I will try now to solve my second problem!!!! Thanks!!! Thanks and Thanks!!!!

Rena
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
magicdlfCommented:
Glad to know : )
0
 
rena24Author Commented:
Helo magicdlf,

I just wanted to thank you again and to tell you that I have solved the second problem too... It was the easy part and i am used to call stored procedures in vb. The difficult part was the one that you have solved. You can see my code bellow. Thanks again!!!!

Rena
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;

namespace WindowsApplication4
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

         void handleFiles(string folderPath, string prefix, List<string> suffixes)
        {
            //create a folder with current date
            string newFolderName = folderPath + "\\old_files\\" + DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss");
            System.IO.Directory.CreateDirectory(newFolderName);
            System.Collections.ArrayList tokens = new System.Collections.ArrayList();

            foreach (string suffix in suffixes)
            {if (suffix == "d.txt"){

                string inputFileName = string.Format("{0}\\{1}_{2}", folderPath, prefix, suffix);
                
                
                StreamReader reader = new StreamReader(inputFileName);
                string line;
                                while ((line = reader.ReadLine()) != null)
                {
                    foreach (string word in line.Split(';'))
                    {
                        tokens.Add(word.Trim());
                    }
                }
                reader.Close();
            }
            }
            string gid = Convert.ToString(tokens[0]);
            string invNo = Convert.ToString(tokens[1]);
            foreach (string suffix in suffixes)
            {
                string srcFilename = string.Format("{0}\\{1}_{2}", folderPath, prefix, suffix);
                string dstFilename = string.Format("{0}\\{1}_{2}", newFolderName, prefix, suffix);
                writeDataToDatabase(invNo, gid , dstFilename );
                
                System.IO.File.Move(srcFilename, dstFilename);
            }
            //then handle the d file
            //TODO
            MessageBox.Show("The Data is in the Database");
        }

        void checkFile(string folderPath)
        {
            string[] files = System.IO.Directory.GetFiles(folderPath);
            Dictionary<string, List<string>> dict = new Dictionary<string, List<string>>();
            foreach (string file in files)
            {
                string filename = System.IO.Path.GetFileName(file);
                string[] parts = filename.Split(new char[] { '_' });
                //parse the prefix
                if (parts.Length == 2)
                {
                    if (!dict.ContainsKey(parts[0]))
                    {
                        dict[parts[0]] = new List<string>();
                        dict[parts[0]].Add(parts[1]);
                    }
                    else
                    {
                        dict[parts[0]].Add(parts[1]);
                    }
                }
            }
            foreach (string key in dict.Keys)
            {
                //check if a.txt,b.txt,c.pdf,d.txt are all ready.
                List<string> suffixes = dict[key];
                if (suffixes.Contains("a.txt") &&
                    suffixes.Contains("b.txt") &&
                    suffixes.Contains("c.pdf") &&
                    suffixes.Contains("d.txt"))
                {
                    handleFiles(folderPath, key, suffixes);
                }
            }
        }

        void writeDataToDatabase(string invNo, string gid , string dstFilename)
        { 
         SqlConnection conn = new SqlConnection();
         SqlCommand comm = new SqlCommand();
       
        
         try{
         conn.ConnectionString = System.Configuration.ConfigurationManager.AppSettings.Get("SQLCONN");
         conn.Open();
         comm.Connection = conn;
         comm.CommandType = CommandType.StoredProcedure;
         comm.CommandText = "Imp_Data";
         comm.Parameters.AddWithValue("@invNo", invNo);
         comm.Parameters.AddWithValue("@GID", gid);
         comm.Parameters.AddWithValue("@path", dstFilename);
         //SqlDataReader dr = 
             comm.ExecuteReader();
     
            }

finally{
conn.Close();
} 

        }
        private void button1_Click_1(object sender, EventArgs e)
        {
            checkFile(System.Configuration.ConfigurationManager.AppSettings.Get("FILEPATH"));
            

        }

        private void button2_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }
    }

    }

Open in new window

0
 
rena24Author Commented:
Helo magicdlf,

I have a little problem. The pdf file  will not have tis format --> 1234_d.pdf but this 1234.pdf. So what changes must we make? I will try it but if you have a Tip for me or the code ;-) that would be great.

Thanks Again

Rena
0
 
magicdlfCommented:
if a file name is end with ".pdf", rename it to "_d.pdf". But remember to change that back when you are actually handling the real files. This is a change that affects least code that I can think of.
0
 
rena24Author Commented:
Helo magicdlf,

great Idea!!! I will try this!!!

Thanks again!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now