Moving CSV Files Based On ColumnNames to Different Folder Destinations

Question

I have One Folder Named ABC in that i have Five Sub Folders (A1 to A5). in that sub folders 10 csv Files with two different Format

Format 1 (Did,Eid,Dname) are the columns 

Format 2 (Dname,Did,Eid,Others) are the columns 

what my requirement is

step 1: loop the files from sub folders  (i completed this)

step 2: check the column names

step 3: move the files into destinations based on formats( csv file have did,eid,dname move the file into Format 1 folder in abcidentity(main folder) else move the file (having dname,did,eid,others) into format 2 folder) this step is also i know how to create by using file system task

what my problem is how to check scan the columns and give the destinations in script task(step 2)

in script task i already wrote if the file is there or not. if file is there content is there or not

i dont know how to scan columns in script task if anybody can send the code. more helpfull for me

Answer 1

This code uses the .xlsx extension, if you have .xls utilise the Microsoft.Jet.OLEDB.4.0 instead of Microsoft.Jet.OLEDB.12.0 and Extended Properties=""Excel 8.0, SHEET1$ can be changed to use the sheet name of sheet name in your xcel

I am displaying the first row below assume that is your column name you can then use them as you like, basically top 1 returns the 1st row dr.read traverses rows and fieldcount give the columns to be traversed,

 string fileName = @"C:\Users\abhinav\Desktop\test.xlsx";
            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; 
                                      Data Source=" + fileName + @";Extended Properties=""Excel 12.0;HDR=No;""";
            string CreateCommand = "SELECT top 1 * FROM [Sheet1$]";
            OleDbConnection conn = new OleDbConnection(connectionString);

            conn.Open();
            OleDbCommand cmd = new OleDbCommand(CreateCommand, conn);
            //   cmd.ExecuteNonQuery(); 
            DbDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                for (int intColCount = 0; intColCount < dr.FieldCount; intColCount++)
                {
                    string ab = dr.GetValue(intColCount).ToString();// This is the First Row First Column 
                    Console.WriteLine(ab);
                }
                
            } 
            Console.ReadLine();

---

Abhinav
http://bishtabhinav.wordpress.com/

Answer 2

thank U very much for sending the script. in this script u gave xlsx format is that same for csv format also

---

Sandeep Kumar

Answer 3

The connection string can be modified to read use the same code above, using ADO .Net, but i would say accessing csv s better via simple IO functions and avoiding dependencies on connection strings

Have modified the code here to suit your need you can replace the name coulmn with the column you want to check i am checking for name column to be present in this code.

http://stackoverflow.com/questions/5282999/c-net-reading-csv-file

 
string fileName = @"C:\Users\abhinav\Desktop\test.csv";var reader = new StreamReader(File.OpenRead(fileName));
            var line = reader.ReadLine();
// AS you need to read only the first line
            if(line.Contains("Name"))
               Console.WriteLine("Name Found");
            else
                    Console.WriteLine("Name not Found");

---

Abhinav
http://bishtabhinav.wordpress.com/

Answer 4

Follow this where the author computes a MD5 hash per file to determine whether the file is of a new format and accordingly takes different action on each file format.

---

http://btsbee.wordpress.com/