Moving CSV Files Based On ColumnNames to Different Folder Destinations
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
August 21st, 2012 12:22am
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/
Free Windows Admin Tool Kit Click here and download it now
August 21st, 2012 2:02am
thank U very much for sending the script. in this script u gave xlsx format is that same for csv format alsoSandeep Kumar
August 21st, 2012 2:10am
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/
Free Windows Admin Tool Kit Click here and download it now
August 21st, 2012 2:55am
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/
August 21st, 2012 3:55am