Remove unwanted Text before and after a character in .csv

Hi Friends i have a .csv file in which there are some unwanted text which i want to remove for extracting the files name only. If anybody is having any idea to solve this please help.

>(Get-Content "D:\Convert\txtdoc\rtbTextBox.csv") |

>Foreach-object {$_ -replace ".*>" -replace "<\.*"} |

>Set-Content "D:\Convert\txtdoc\rtbTextBox.csv"

File is having Text as below:



    TEST-2014-08026
    30-05-2014
    Developer
    svn://10.285.120.144/devmd/Trunk


        load\hcl\safetoload\doc_typ\TEST-2014-08026_doc_typ.csv
        hcl\safetoload\doc_typ\doc_typ.ctl





        load\hcl\safetoload\doc_typ_arg\TEST-2014-08026_doc_typ_arg.csv
        hcl\safetoload\doc_typ_arg\doc_typ_arg.ctl

        load\hcl\safetoload\doc_lblfmt\TEST-2014-08026_doc_lblfmt.csv
        hcl\safetoload\doc_lblfmt\doc_lblfmt.ctl

        load\hcl\safetoload\dscmst\TEST-2014-08026_dscmst.csv
        hcl\safetoload\dscmst\dscmst.ctl

        load\hcl\safetoload\lblfmt\TEST-2014-08026_lblfmt.csv
        hcl\safetoload\lblfmt\lblfmt.ctl

            labels


            LABEL\1662_ceva_carlbl.POF
            LABEL\1662_ceva_carlbl.lbl
            1662_ceva_carlbl.msql






I want to save result as For EX :**

TEST-2014-08026_doc_typ.csv
doc_typ.ctl
TEST-2014-08026_doc_typ_arg.csv
doc_typ_arg.ctl
TEST-2014-08026_doc_lblfmt.csv

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes12y ago
OK, that's no problem:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;
using System.IO;

class Program
{
static void Main()
{
List list = new List();

XElement deployment = XElement.Load("naveen.xml");

foreach(XElement xe in deployment.Element("csvfiles").Elements("csv"))
{
string file = Path.GetFileName(xe.Element("file").Value);
list.Add(file);
string loadCtl = Path.GetFileName(xe.Element("loadCtl").Value);
list.Add(loadCtl);
}

foreach(XElement xe in deployment.Element("files").Element("labels").Element("label").Element("labelFiles").Elements("labFile"))
{
list.Add(xe.Value);
}

File.WriteAllLines("naveen.csv", list.ToArray());
Console.WriteLine("Data extracted and saved to naveen.csv");
Console.ReadKey();
}
}

The csv file now looks like this:

TEST-2014-08026_doc_typ.csv
doc_typ.ctl
TEST-2014-08026_doc_typ_arg.csv
doc_typ_arg.ctl
TEST-2014-08026_doc_lblfmt.csv
doc_lblfmt.ctl
TEST-2014-08026_dscmst.csv
dscmst.ctl
TEST-2014-08026_lblfmt.csv
lblfmt.ctl
LABEL\1662_ceva_carlbl.POF
LABEL\1662_ceva_carlbl.lbl
1662_ceva_carlbl.msql
0
Naveen Singh12y ago
No I mean there is few more files between ..... please check the whole file structure in the Posted Question labels LABEL\1662_ceva_carlbl.POF LABEL\1662_ceva_carlbl.lbl 1662_ceva_carlbl.msql which is not showing using above code
0
Vulpes12y ago
Do you mean that you want the data to be comma separated rather than on individual lines?
0
Naveen Singh12y ago
Hi Vulpes, from the above code we can get data b/w ,,,,,,,,, But not b/w ,,,,,,,,,,,,,,,,, plz help
0
Vulpes12y ago
OK, I've modified the program to save the data to a .csv file rather than output it to the console:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;
using System.IO;

class Program
{
static void Main()
{
List list = new List();

XElement deployment = XElement.Load("naveen.xml");

foreach(XElement xe in deployment.Element("csvfiles").Elements("csv"))
{
string file = Path.GetFileName(xe.Element("file").Value);
list.Add(file);
string loadCtl = Path.GetFileName(xe.Element("loadCtl").Value);
list.Add(loadCtl);
}

File.WriteAllLines("naveen.csv", list.ToArray());
Console.WriteLine("Data extracted and saved to naveen.csv");
Console.ReadKey();
}
}

0
Naveen Singh12y ago
Hi yes i want to save the output data in a csv file please help
0
Vulpes12y ago
Well, you can extract the details you want from the XML file as follows:

using System;
using System.Linq;
using System.Xml.Linq;
using System.IO;

class Program
{
static void Main()
{
XElement deployment = XElement.Load("naveen.xml");

foreach(XElement xe in deployment.Element("csvfiles").Elements("csv"))
{
string file = Path.GetFileName(xe.Element("file").Value);
Console.WriteLine(file);
string loadCtl = Path.GetFileName(xe.Element("loadCtl").Value);
Console.WriteLine(loadCtl);
}

Console.ReadKey();
}
}

The output is:

TEST-2014-08026_doc_typ.csv
doc_typ.ctl
TEST-2014-08026_doc_typ_arg.csv
doc_typ_arg.ctl
TEST-2014-08026_doc_lblfmt.csv
doc_lblfmt.ctl
TEST-2014-08026_dscmst.csv
dscmst.ctl
TEST-2014-08026_lblfmt.csv
lblfmt.ctl

I'm not clear from your post whether you just want to extract these details or to save the 'cleaned' data back to the file?

0