none
How to extract data RRS feed

  • Question

  • This is my sample data

    Net income	$109,138	$86,974		
    Depreciation	16,651		16,336
    Amortization	12,942		11,942

    first column is Li name and rest is period value. periodic value may have currency sign or may not but must be numeric.

    each line is separated by /t/r that i can extract but how could i extract Li & period value from each line. if i split by space then Li name may have space between so how could i do it ?

    how RegEx can help me. please help me with sample code. thanks

    Thursday, June 27, 2019 3:43 PM

Answers

  • Hi Sudip_inn, 

    Thank you for posting here.

    Based on your question, you want to extract the data you provided. I use a text file to do the test.

    Here’s the code and you can refer it.

            private void button1_Click(object sender, EventArgs e)
            {
                List<string> lstr = fileToString(@"D:\test.txt");
                Regex regex = new Regex(@"[0-9]|[,]");
                Regex regex2 = new Regex(@"[a-zA-Z]");
                string strReg = null;
                bool control1 = true;
                bool control2 = true;
                DataTable dt = new DataTable();
                dt.Columns.Add("data");
                dt.Columns.Add("money1");
                dt.Columns.Add("money2");
                List<string> lt = new List<string>(); 
                foreach (string str in lstr)
                {
                    char[] ca = ReverseC(str);
                    foreach (char ch in ca)
                    {
                        if (control1)
                        {
                            if (regex.IsMatch(ch.ToString()))
                            {
                                strReg += ch.ToString();
                            }
                        }
                        if (control2)
                        {
                            if (ch.Equals('\t') || ch.Equals('$'))
                            {
                                if (strReg == null)
                                {
                                    continue;
                                }
                                else
                                {
                                    lt.Add(ReverseS(strReg));
                                    strReg = null;
                                }
                            }
                        }
                        if (ch.Equals(' '))
                        {
                            strReg += " ";
                        }
                        if (regex2.IsMatch(ch.ToString()))
                        {
                            control1 = false;
                            control2 = false;
                            strReg += ch.ToString();
                        }
                    }
                    lt.Add(ReverseS(strReg));
                    dt.Rows.Add(lt[2],lt[1],lt[0]);
                    lt.Clear();
                    strReg = null;
                    control1 = true;
                    control2 = true;
                }
                dataGridView1.DataSource = dt;
            }
            public static string ReverseS(string text)
            {
                char[] charArray = text.ToCharArray();
                Array.Reverse(charArray);
                return new string(charArray);
            }
            public static char[] ReverseC(string text)
            {
                char[] charArray = text.ToCharArray();
                Array.Reverse(charArray);
                return charArray;
            }
            public static List<string> fileToString(string filePath)
            {
                List<string> lstr = new List<string>();
                try
                {
                    string line;
                    using (System.IO.StreamReader sr = new System.IO.StreamReader(filePath))
                    {
                        while ((line = sr.ReadLine()) != null)
                        {
                            lstr.Add(line);
                        }
                    }
                }
                catch (Exception e)
                {
                    MessageBox.Show("The file could not be read:");
                    MessageBox.Show(e.Message);
                }
                return lstr;
            }

    Hope it can help you.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Friday, July 5, 2019 7:22 PM
    Tuesday, July 2, 2019 8:21 AM
    Moderator

All replies

  • Hello,

    • Does the rest of the data look like the sample data and is there just one, or more.
    • Where does this data exists e.g. text file and if so what are the delimiters if any or positional. If that is know perhaps that is an alternative to working with spaces.
    • What is "Li name"?
    • What have you tried code-wise so far?

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, June 27, 2019 4:50 PM
    Moderator
  • suppose my above data stored in string variable or in flat file. so how to pick first column value if there is space and as well as how to extract numeric data with $ sign separated by space
    Friday, June 28, 2019 9:07 AM
  • Hi Sudip_inn, 

    Thank you for posting here.

    Based on your question, you want to extract the data you provided. I use a text file to do the test.

    Here’s the code and you can refer it.

            private void button1_Click(object sender, EventArgs e)
            {
                List<string> lstr = fileToString(@"D:\test.txt");
                Regex regex = new Regex(@"[0-9]|[,]");
                Regex regex2 = new Regex(@"[a-zA-Z]");
                string strReg = null;
                bool control1 = true;
                bool control2 = true;
                DataTable dt = new DataTable();
                dt.Columns.Add("data");
                dt.Columns.Add("money1");
                dt.Columns.Add("money2");
                List<string> lt = new List<string>(); 
                foreach (string str in lstr)
                {
                    char[] ca = ReverseC(str);
                    foreach (char ch in ca)
                    {
                        if (control1)
                        {
                            if (regex.IsMatch(ch.ToString()))
                            {
                                strReg += ch.ToString();
                            }
                        }
                        if (control2)
                        {
                            if (ch.Equals('\t') || ch.Equals('$'))
                            {
                                if (strReg == null)
                                {
                                    continue;
                                }
                                else
                                {
                                    lt.Add(ReverseS(strReg));
                                    strReg = null;
                                }
                            }
                        }
                        if (ch.Equals(' '))
                        {
                            strReg += " ";
                        }
                        if (regex2.IsMatch(ch.ToString()))
                        {
                            control1 = false;
                            control2 = false;
                            strReg += ch.ToString();
                        }
                    }
                    lt.Add(ReverseS(strReg));
                    dt.Rows.Add(lt[2],lt[1],lt[0]);
                    lt.Clear();
                    strReg = null;
                    control1 = true;
                    control2 = true;
                }
                dataGridView1.DataSource = dt;
            }
            public static string ReverseS(string text)
            {
                char[] charArray = text.ToCharArray();
                Array.Reverse(charArray);
                return new string(charArray);
            }
            public static char[] ReverseC(string text)
            {
                char[] charArray = text.ToCharArray();
                Array.Reverse(charArray);
                return charArray;
            }
            public static List<string> fileToString(string filePath)
            {
                List<string> lstr = new List<string>();
                try
                {
                    string line;
                    using (System.IO.StreamReader sr = new System.IO.StreamReader(filePath))
                    {
                        while ((line = sr.ReadLine()) != null)
                        {
                            lstr.Add(line);
                        }
                    }
                }
                catch (Exception e)
                {
                    MessageBox.Show("The file could not be read:");
                    MessageBox.Show(e.Message);
                }
                return lstr;
            }

    Hope it can help you.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Friday, July 5, 2019 7:22 PM
    Tuesday, July 2, 2019 8:21 AM
    Moderator
  • A regular expression is not necessary here at all.  When lines are separated by newlines and fields are separated by tabs, parsing is really easy.

    using System;
    using System.Text;
    using System.IO;
    using System.Globalization;
    
    class Program
    {
        static void Main(string[] args)
        {
            string[] lines = File.ReadAllLines("x.dat");
            foreach( string line in lines )
            {
                string[] parts = line.Split('\t');
                if( parts[0] == "Net income" )
                {
                    decimal d1 = decimal.Parse(parts[1], NumberStyles.Currency );
                    decimal d2 = decimal.Parse(parts[2], NumberStyles.Currency );
                    Console.WriteLine( "Income: {0} {1}", d1, d2 );
                }
                else if( parts[0] == "Depreciation" )
                {
                    decimal d1 = decimal.Parse(parts[1], NumberStyles.Number );
                    decimal d2 = decimal.Parse(parts[3], NumberStyles.Number );
                    Console.WriteLine( "Dep: {0} {1}", d1, d2 );
                }
                else if( parts[0] == "Amortization" )
                {
                    decimal d1 = decimal.Parse(parts[1], NumberStyles.Number );
                    decimal d2 = decimal.Parse(parts[3], NumberStyles.Number );
                    Console.WriteLine( "Amort: {0} {1}", d1, d2 );
                }
            }
        }
    }
    


    Tim Roberts | Driver MVP Emeritus | Providenza &amp; Boekelheide, Inc.

    Tuesday, July 2, 2019 7:21 PM
  • we can't hard code Amortization & Depreciation because it is not known.

    so what would be best solution. thanks

    Thursday, July 4, 2019 11:57 AM
  • put the parse data in data table and add screen shot so i can understand how data look like.

    tell me the usage of below two RegEx what these 2 regex is doing

    Regex regex = new Regex(@"[0-9]|[,]");
    Regex regex2 = new Regex(@"[a-zA-Z]");

    thanks

    Thursday, July 4, 2019 11:59 AM