Parse multiple Flat files and export to Excel Spreadsheet

I have written a utility which will be use full for parsing a flat file and exporting to an excel spreadsheet. I used Visual studio 2005 with .net 2.0 framework c#.
One of my friend had a requirement in his work location he has to read multiple flat files of similar data and export it to one single spread sheet.

Bacically i have written in a Console App. You can use this code and change accordingly based on the your requirement.

I took a two simple flat files like information regarding firstname,lastname,middlename,address.

Create a text file Name:FlatFile_1.txt
Paste the content in the txt file and save in C:\FlatFiles\

Samual sam Jackson 900 Bethalham rd edison NJ
Gorge Micheal Micheal 300 vartan ct Harrisburgh PA
Micheal jackson Micheal 100 Wlatin rd Los Angeles CA
Demi Moore M 211 Century Park Los Angeles CA

Create a text file Name:FlatFile_2.txt
Paste the content in the txt file and save in C:\FlatFiles\

AlPachino sam son 900 Bethalham rd edison NJ
Rihana heal Micheal 300 vartan ct Harrisburgh PA
Britny Speers al 100 Wlatin rd Los Angeles CA
joel Osteen M 211 Century Park Los Angeles CA

NameSpace
Namespaces required for this app is System.IO is used for get,read and write the files from the directory.System.Data is used to create a temp table of DataTable and DataSet objects.
System.Collections is used for creating the array list.
Microsoft.Office.Interop.Excel is used to creat a Excel object and export to Spreadsheet.

Add reference of Microsoft.Office.Interop.Excel by clicking Add Reference in you project and go to .NET tab and search for Microsoft.Office.Interop.Excel dll. select it and click ok to add it to your project reference.


using System.IO;
using System.Data;
using System.Collections;
using Microsoft.Office.Interop.Excel

I created a class called MyFlatFIleReaderClass and a Main Method where you try to execute the code of your class. THe class consist of a Function ParseFile which takes the input parameter of string inputFile. which returns a dataset.

This function has a tempeory table DataTable dt and a DataColumn dc and DataRow dr.
StreamReader and TextReader class is used to Read the flat file data and puts in the string call SLine. It read's each line and add's it to the array list object.
Once the arraylist object is filled we have to loop through it and adds the rows to the Columns by using subString on the column length and gets the actual column value.

In the Main method DirectoryInfo class is used to get all the files which is of type *.txt by using the GetFiles method and stores it in FIleInfo Array object.
once you hacve all the files from the directory we can loop through the files and make a call to the ParseFile function and pass the path Attached the filename. The Function returns a dataset with all the columns and rows created for numbers of tables.

Next step is to create a ApplicationClass object which is used to add columns and rows in the excel object and opens the Excel spreadsheet.

THe Data Displayed in the Excel Sheet will be sorted by the First Column.
by implementing the
Range rng = worksheet.get_Range("A1", Type.Missing);
rng.Sort Method

// Source Code
// Source Code
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Collections;
using Microsoft.Office.Interop.Excel;
namespace Training
{
class MyFlatFIleReaderClass
{
public DataSet ds = new DataSet();
public DataSet ParseFile(string inputFile)
{
DataRow dr;
DataColumn dc;
System.Data.DataTable dt = new System.Data.DataTable();
dc = new DataColumn("FirstName");
dt.Columns.Add(dc);
dc = new DataColumn("LastName");
dt.Columns.Add(dc);
dc = new DataColumn("MiddleName");
dt.Columns.Add(dc);
dc = new DataColumn("Address");
dt.Columns.Add(dc);
//dr = dt.NewRow();
string sLine = "";
ArrayList arrText = new ArrayList();
using (TextReader reader = new StreamReader(inputFile))
{

while (sLine != null)
{
sLine = reader.ReadLine();
if (sLine != null && sLine != "")
arrText.Add(sLine);
}
reader.Close();

foreach (string sOutput in arrText)
{
dr = dt.NewRow();
if (sOutput != string.Empty)
{
dr["FirstName"] = sOutput.Substring(0, 9);
dr["MiddleName"] = sOutput.Substring(10, 9);
dr["LastName"] = sOutput.Substring(20, 9);
dr["Address"] = sOutput.Substring(30);
dt.Rows.Add(dr);
}
}
ds.Tables.Add(dt);
return ds;
}
}

static void Main(string[] args)
{
MyFlatFIleReaderClass tr = new MyFlatFIleReaderClass();
DirectoryInfo di = new DirectoryInfo("C:\\FlatFiles\\");
FileInfo[] rgFiles = di.GetFiles("*.txt");
DataSet ds1 = new DataSet();
foreach (FileInfo fi in rgFiles)
{
ds1 = tr.ParseFile(@"C:\FlatFiles\" + fi.Name);
}

ApplicationClass excel = new ApplicationClass();
excel.Application.Workbooks.Add(true);

//System.Data.DataSet dstable = ds1.Tables[;
int ColumnIndex = 0;
//for (int j = 0; j <= ds1.Tables.Count-1; j++) //{ foreach (DataColumn col in ds1.Tables[0].Columns) { ColumnIndex++; excel.Cells[1, ColumnIndex] = col.ColumnName; } //} int rowIndex = 0; for (int i = 0; i <= ds1.Tables.Count - 1; i++) { foreach (DataRow row in ds1.Tables[i].Rows) { rowIndex++; ColumnIndex = 0; foreach (DataColumn col in ds1.Tables[0].Columns) { ColumnIndex++; excel.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName]; } } } excel.Visible = true; Worksheet worksheet = (Worksheet)excel.ActiveSheet; worksheet.Name = "Family_Details"; Range rng = worksheet.get_Range("A1", Type.Missing); rng.Sort(rng, XlSortOrder.xlAscending, Type.Missing, Type.Missing, XlSortOrder.xlAscending, Type.Missing, XlSortOrder.xlAscending, XlYesNoGuess.xlNo, Type.Missing, Type.Missing, XlSortOrientation.xlSortColumns, XlSortMethod.xlPinYin, XlSortDataOption.xlSortNormal, XlSortDataOption.xlSortNormal, XlSortDataOption.xlSortNormal); worksheet.Activate(); } } }