The purpose of this post is to create a simple converter from an XML file to a CVS file. This program was originally created to convert a DotNetNuke log file to a CVS file to be read by Crystal Reports. It accomplishes this by first scanning the XML file and removing any unwanted XML code, then saving as a CVS file named "mytest.cvs" in the C: drive.
Basically, this program is an automated text editor. But, since I use this code and variations of it all the time for everything from HTML stripping to XML stripping, I thought it might be useful to share.
The first step is to open up Microsoft Visual Studio. I use the Express 2010 version. From the toolbar (should be on the left side of your screen if you are using Microsoft Visual Studio), select a RichTextBox object, an OpenFileDialog and a button to start with. Drag them onto your form.
This first button is going to allow users to select the XML file to open. (Actually, any text file will open--but for simplicity I am going to stick with XML for the moment). I named my button "Open File".
Double click on the "Open File" button and paste in the following code:
//fetch the XML file and show it in the rich text box
private void button1_Click(object sender, EventArgs e)
{
//open the file structure
using (System.Windows.Forms.OpenFileDialog ofd = new OpenFileDialog())
{
//only choose text files
ofd.Filter = "All files (*.*)|*.*";
ofd.ShowDialog();
//write text file to screen
this.richTextBox1.Text = System.IO.File.ReadAllText(ofd.FileName);
}
}
Notice the "All files(*.*)|*.*" statement. If you were to change it to "All files(*.*)|*.txt" you would be restricting the user to files to either select only text files or all files. You can also create your own proprietary file type extension and restrict the user to only opening those.
Also, by double clicking on the richtextbox, you could add some fancy text highlighting on certain terms (just like in the Notebook Editor). To do this, you would simply paste something like:
private void richTextBox1_TextChanged(object sender, EventArgs e)
{
if (richTextBox1.SelectionFont != null)
{
is.CheckKeyword("phrase to find", Color.Red, 0);
is.CheckKeyword("some other phrase", Color.Green, 0);
is.CheckKeyword("yet another phrase", Color.BlueViolet, 0);
}
}
The function "CheckKeyword" would then be coded as follows:
private void CheckKeyword(string word, Color color, int startIndex)
{
if (this.richTextBox1.Text.Contains(word))
{
int index = -1; int selectStart = this.richTextBox1.SelectionStart;
while ((index = this.richTextBox1.Text.IndexOf(word, (index + 1))) != -1)
{
this.richTextBox1.Select((index + startIndex), word.Length);
this.richTextBox1.SelectionColor = color;
this.richTextBox1.Select(selectStart, 0);
this.richTextBox1.SelectionColor = Color.Black;
}
}
}
But I digress--what we want to do is search the richtextbox for XML code and strip that out. We do this by inserting another button into our project from the toolbar. Click on the button and type something like this. Please keep in mind that button4 is the name of my button in my project--the name of your button may be completely different. All you really care about is the ConvertContentsToString function call :
private void button4_Click(object sender, EventArgs e)
{
ConvertContentsToString(richTextBox1);
}
Now we need to be able to define the function ConvertContentsToString. The code is as follows:
void ConvertContentsToString(RichTextBox rtb)
{
string RTBString = rtb.Rtf;
string quotes = "\"";
//if there are any quotes in the text box, change to commas
//remove all XML key words
if (this.richTextBox1.Text.Contains(quotes))
{
RTBString = RTBString.Replace("\"", ",");
RTBString = RTBString.Replace("=", "");
RTBString = RTBString.Replace(">", "");
RTBString = RTBString.Replace("<", "");
RTBString = RTBString.Replace("/", "");
RTBString = RTBString.Replace("LogEntries", "");
RTBString = RTBString.Replace("log", "");
RTBString = RTBString.Replace("LogFileID", "");
RTBString = RTBString.Replace("LogTypeKey", "");
RTBString = RTBString.Replace("LogUserID", "");
RTBString = RTBString.Replace("PropertyName", "");
RTBString = RTBString.Replace("IP", "From Computer: ");
RTBString = RTBString.Replace("LogProperty", "");
RTBString = RTBString.Replace("par", "");
RTBString = RTBString.Replace("LogGUID", "");
RTBString = RTBString.Replace("\\", "");
RTBString = RTBString.Replace("LogServerName", "");
RTBString = RTBString.Replace("LogConfigID", "");
RTBString = RTBString.Replace("True", "");
RTBString = RTBString.Replace("False", "");
RTBString = RTBString.Replace("ByPassBuffering", "");
RTBString = RTBString.Replace("LogCreateNumber", "");
RTBString = RTBString.Replace("LogPortalName", "");
RTBString = RTBString.Replace("LogPortalID", "");
RTBString = RTBString.Replace(", ,", ",");
RTBString = RTBString.Replace("PropertyValue", "");
RTBString = RTBString.Replace("LogUserName", "Name: ");
RTBString = RTBString.Replace("LOGIN_SUCCESS", "");
RTBString = RTBString.Replace("LogProperties", "");
RTBString = RTBString.Replace("SAFWEB01", "");
RTBString = RTBString.Replace("LogCreateDateNum", "");
RTBString = RTBString.Replace("BypassBuffering", "");
RTBString = RTBString.Replace("LogCreateDate", "");
//richtextbox contents have been stripped of dat
//write them to a csv file
File.WriteAllText("C:/mytest.csv", RTBString);
}
}
This function is the bulk of the work. Please modify as you see fit. The complete code for the XML to CVS convert is listed below. Cheers and happy coding!
//Created by: Olivia E Terrell
//Description: A simple log file XML to CVS and data stripper example
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using System.IO;
using System.Drawing.Text;
namespace Translate_my_XML
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
}
//see what is currently living in the rich text box
private void richTextBox1_TextChanged(object sender, EventArgs e)
{
if (richTextBox1.SelectionFont != null)
{
//is.CheckKeyword("LOGIN_SUCCESS", Color.Red, 0);
//is.CheckKeyword("LogUserName=", Color.Green, 0);
//is.CheckKeyword("extern", Color.BlueViolet, 0);
}
}
//check to see if we have any keywords that signal the beginning of data collection
private void CheckKeyword(string word, Color color, int startIndex)
{
if (this.richTextBox1.Text.Contains(word))
{
int index = -1; int selectStart = this.richTextBox1.SelectionStart;
while ((index = this.richTextBox1.Text.IndexOf(word, (index + 1))) != -1)
{
this.richTextBox1.Select((index + startIndex), word.Length);
this.richTextBox1.SelectionColor = color;
this.richTextBox1.Select(selectStart, 0);
this.richTextBox1.SelectionColor = Color.Black;
}
}
}
//convert the richtextbox to a string and search it
void ConvertContentsToString(RichTextBox rtb)
{
string RTBString = rtb.Rtf;
string quotes = "\"";
//if there are any quotes in the text box, change to commas
//remove all XML key words
if (this.richTextBox1.Text.Contains(quotes))
{
RTBString = RTBString.Replace("\"", ",");
RTBString = RTBString.Replace("=", "");
RTBString = RTBString.Replace(">", "");
RTBString = RTBString.Replace("<", "");
RTBString = RTBString.Replace("/", "");
RTBString = RTBString.Replace("LogEntries", "");
RTBString = RTBString.Replace("log", "");
RTBString = RTBString.Replace("LogFileID", "");
RTBString = RTBString.Replace("LogTypeKey", "");
RTBString = RTBString.Replace("LogUserID", "");
RTBString = RTBString.Replace("PropertyName", "");
RTBString = RTBString.Replace("IP", "From Computer: ");
RTBString = RTBString.Replace("LogProperty", "");
RTBString = RTBString.Replace("par", "");
RTBString = RTBString.Replace("LogGUID", "");
RTBString = RTBString.Replace("\\", "");
RTBString = RTBString.Replace("LogServerName", "");
RTBString = RTBString.Replace("LogConfigID", "");
RTBString = RTBString.Replace("True", "");
RTBString = RTBString.Replace("False", "");
RTBString = RTBString.Replace("ByPassBuffering", "");
RTBString = RTBString.Replace("LogCreateNumber", "");
RTBString = RTBString.Replace("LogPortalName", "");
RTBString = RTBString.Replace("LogPortalID", "");
RTBString = RTBString.Replace(", ,", ",");
RTBString = RTBString.Replace("PropertyValue", "");
RTBString = RTBString.Replace("LogUserName", "Name: ");
RTBString = RTBString.Replace("LOGIN_SUCCESS", "");
RTBString = RTBString.Replace("LogProperties", "");
RTBString = RTBString.Replace("SAFWEB01", "");
RTBString = RTBString.Replace("LogCreateDateNum", "");
RTBString = RTBString.Replace("BypassBuffering", "");
RTBString = RTBString.Replace("LogCreateDate", "");
//richtextbox contents have been stripped of dat
//write them to a csv file
File.WriteAllText("C:/mytest.csv", RTBString);
}
}
//fetch the XML file and show it in the rich text box
private void button1_Click(object sender, EventArgs e)
{
//open the file structure
using (System.Windows.Forms.OpenFileDialog ofd = new OpenFileDialog())
{
//only choose text files
ofd.Filter = "All files (*.*)|*.*";
ofd.ShowDialog();
//write text file to screen
this.richTextBox1.Text = System.IO.File.ReadAllText(ofd.FileName);
}
}
//clear the data
private void button2_Click(object sender, EventArgs e)
{
richTextBox1.Clear();
}
//save the stripped data as a new file
private void button3_Click(object sender, EventArgs e)
{
//ask for the new file name
//saveFile1.DefaultExt = "*.txt";
SaveFileDialog saveFile1 = new SaveFileDialog();
saveFile1.DefaultExt = "*.rtf";
saveFile1.Filter = "RTF Files|*.rtf|DSD Files|*.dsd";
// Determine if the user selected a file name
if (saveFile1.ShowDialog() == System.Windows.Forms.DialogResult.OK &&
saveFile1.FileName.Length > 0)
{
// Save the contents of the RichTextBox into the file.
richTextBox1.SaveFile(saveFile1.FileName, RichTextBoxStreamType.PlainText);
}
}
//search the file for this text
private void button4_Click(object sender, EventArgs e)
{
ConvertContentsToString(richTextBox1);
}
}
}
