How to parse a text file and write the result to an xls file?

Question

Hi all i am a junior level SQL developer. I have a situation where I have a text file with 1100 lines of a search result with each line containing a file path and a stored procedure associated with that file. Each line has a structure like the one below:

abc\def\ghi\***.cs(40): jkl=******.*****.******, "proc_pqrst", parms);

Where abc\def\ghi\***.cs is file path of the file ***.cs. The stored procedure names begin with proc_. I have to extract the ***.cs and the corresponding stored procedure name begining with proc_ and write them to a .xls file. Can some body help me in writing the parsing program to do this? Also can I get a detailed outline on where should I write the code for c# and where should I compile it? This could be a great help as I don't have any knowledge of C#.

Thank you, BK.

Answer 1

This might sounds silly, but a better option might be to import the raw CSV into Excel and then write a macro to do the extraction. Might be a gentler learning curve than C#, Visual Studio, etc.

Answer 2

As for parsing, you could use regular expressions, such as this one:

^(.*?\.cs).*"(proc_[A-Za-z0-9]*?)".*$

(by the way, I didn't test that, so it might not work)

On the exporting to XLS file side of things, that's a bit harder. If you're going to be running it on a Windows box that has Excel installed, then I believe you can use COM to make Excel do it for you, but otherwise, you'll have to either: 1. Implement the XLS file format (hard), 2. buy an implementation of the XLS file format (expensive), or 3. do something else, such as automating OpenOffice or something.

Answer 3

// returns abc\def\ghi\something.cs
string path = myString.Substring(0, myString.IndexOf(".cs"),3);

string temp = myString.Substring(myString.IndexOf("proc_"));

// returns proc_pqrst
string proc = temp.Substring(0, temp.IndexOf('\"')));

To create a file readable in Excel, just write out a comma-delimited file (CSV).

Answer 4

If you absolutely need an xls file (instead of just csv), the FileHelpers library makes it easy to write an xls file.

FileHelpers takes a strongly typed class and writes it as a csv or xls, or can connect directly to a SQL server and save records there.

Basically, you'd have something like:

public class Record {
    public string filename;
    public string storedProc;
}

You could use Robert Harvey's code or a regex to extract the path and procedure names, create a new Record, and drop them in the new object.

Record r = new Record();
r.filename = myString.Substring(0, myString.IndexOf(".cs"),3);       
r.storedProc= myString.Substring(myString.IndexOf("proc_")).Substring(0, temp.IndexOf('\"'));       

You can then use the ExcelStorage class to save the file.

Answer 5

Thank you all for the quick response! I will try the suggested solutions and will let you know.

Thank you all once again!