How to pass specific portion of the csv file

Question

0.00/5 (No votes)

See more:

Hi Friends,

I have a csv file and need to parse it for the specific portion. Since my csv file is not properly structured difficult to read into memory and identify rows and columns.

My requirement is, Here Two accounts are given and need to parsed based upon the class structure

C#

ASSTROLLUPSTRT
TOTALACCTS ,  Total Accounts : 2
ACCTHDR , Account Details
ACCTHDR , Key Account ID, Account Type, Value, Portfolio Type, Portfolio ID, Owner ID
ACCTDTLS ,  2002-01-20-12.04.20.445297, RothIRA, 6280496.23, TaxExemptIRA, 1, 2423
ACCTALLOHDR, Account Allocations
ACCTALLOHDR, Key Account ID, Type , AssetClass, Value
ACCTALLODTLS ,  2002-01-20-12.04.20.445297, Allocation, E, 0.11
ACCTALLODTLS ,  2002-01-20-12.04.20.445297, Cost Basis, E, 0
ACCTALLODTLS ,  2002-01-20-12.04.20.445297, Allocation, EA, 0.11
ACCTALLODTLS ,  2002-01-20-12.04.20.445297, Cost Basis, EA, 0
ACCTALLODTLS ,  2002-01-20-12.04.20.445297, Allocation, A, 2.92
ACCTALLODTLS ,  2002-01-20-12.04.20.445297, Cost Basis, A, 123328.9
ACCTALLODTLS ,  2002-01-20-12.04.20.445297, Allocation, AH, 2.92
ACCTALLODTLS ,  2002-01-20-12.04.20.445297, Cost Basis, AH, 123328.9
ACCTALLODTLS ,  2002-01-20-12.04.20.445297, Allocation, O, 56.72
ACCTALLODTLS ,  2002-01-20-12.04.20.445297, Cost Basis, O, 0
ACCTALLODTLS ,  2002-01-20-12.04.20.445297, Allocation, OO, 56.72
ACCTALLODTLS ,  2002-01-20-12.04.20.445297, Cost Basis, OO, 0
ACCTALLODTLS ,  2002-01-20-12.04.20.445297, Allocation, L, 40.25
ACCTALLODTLS ,  2002-01-20-12.04.20.445297, Cost Basis, L, 2528022.3
ACCTALLODTLS ,  2002-01-20-12.04.20.445297, Allocation, LC, 40.25
ACCTALLODTLS ,  2002-01-20-12.04.20.445297, Cost Basis, LC, 2528022.3
ACCTDTLS ,  2016-09-28 13:01:20:9473, Taxable, 0, Taxable, 2, 2423
ACCTALLOHDR, Account Allocations
ACCTALLOHDR, Key Account ID, Type , AssetClass, Value
ACCTALLODTLS ,  2016-09-28 13:01:20:9473, Allocation, LC, 100
ACCTALLODTLS ,  2016-09-28 13:01:20:9473, Cost Basis, LC, 0
ACCTALLODTLS ,  2016-09-28 13:01:20:9473, Allocation, L, 100
ACCTALLODTLS ,  2016-09-28 13:01:20:9473, Cost Basis, L, 0

What I have tried:

Below is the class i defined by looking at the csv file. for easy parsing if changes are required we can modify the class structure.
Please help me to achieve this..Please shed some lights how to proceed further

C#

    public class CSVparsing
{
    public List AccountDetails<Item> { get; set; } // //less than and greater symbol is not working while posting the question
}

public class AccountDetails
{
    public string  KeyAccountID { get; set; }
    public string  AccountType { get; set; }
    public double Value { get; set; }
    public string  PortfolioType { get; set; }
    public double EarningsRate { get; set; }
    public int  PortfolioID { get; set; }
    public int  OwnerID { get; set; }
    public Allocation AllocDetails { get; set; }
}
public class Allocation
{
    public string AssetClassCode { get; set; }
    public double Amount { get; set; } //Allocation Value
    public double CostBasis { get; set; } // Cost Basis Value
}

Posted 29-Sep-16 6:40am

jinesh sam

Updated 29-Sep-16 10:13am

#realJSOP

v7

Add a Solution

Comments

[no name] 29-Sep-16 12:53pm

How you can achieve what? Writing some code would be a good start.

jinesh sam 29-Sep-16 12:55pm

please suggest some tips to starts. Which method will be efficient ?

#realJSOP 29-Sep-16 14:12pm

To include < and > in a message here, you have to use standard html notation of < and >

I went ahead and fixed your "what I tried" stuff.

jinesh sam 29-Sep-16 16:17pm

@John, One Doubt, If < is Inside pre="C#" tag why we need to go for lt; Previously i didn't face any such issues. while positing questions

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Answer 1 · 2016-09-29T07:35:00

Well, the first thing you need to do is identify the pattern(s), and code for that. After a cursory glance, the pattern looks like this:

<br />
ASSTROLLUPSTRT<br />
  ACCTHDR [section name]<br />
    ACCTHDR [column names]<br />
      ACCTDTLS, Detail data...<br />
    ACCTALLOHDR [subheader name]<br />
      ACCTALLOHDR[column names]<br />
        ACCTALLODTLS, Detail data...<br />
        ACCTALLODTLS, Detail data...<br />
        ACCTALLODTLS, Detail data...<br />

Once you've established the patterns, write code to support the patterns. Unfortunately, this is NOT a CSV file according to the strict definition of a CSV file (all rows have the same number of columns). This looks more like an XML file without the XML decoration. The section naming is inconvenient, but not a substantial mental hurdle. Go forth and code.

jinesh sam · Answer 2 · 2016-09-29T10:13:00

Myself found solution. Please anyone suggest any simple or easy way

C#

 class Program
    {
        static void Main(string[] args)
        {
            var lines = File.ReadAllLines(@"Mycsvfile.csv").Select(x => x.Split(',')).ToList();
            List<accountdetails> lstAccountDetails = new List<accountdetails>();
            for (int i = 0; i < lines.Count; i++)
            {
                if (lines[i][0].Trim() == "ACCTDTLS")
                {
                    AccountDetails ad = new AccountDetails();
                    if (lines[i].Count() > 6)
                    {
                        ad.KeyAccountID = lines[i][1];
                        ad.AccountType = lines[i][2];
                        ad.Value = Convert.ToDouble(lines[i][3]);
                        ad.PortfolioType = lines[i][4];
                        ad.PortfolioID = Convert.ToInt32(lines[i][5]);
                        ad.OwnerID = Convert.ToInt32(lines[i][6]);
                    }
                    i = i + 3;
                    List<allocation> lstallocation = new List<allocation>();
                    for (int j = i; j < i + 2; j++)
                    {
                        Allocation aloc = new Allocation();
                        if (j < lines.Count)
                        {
                            aloc.AssetClassCode = lines[j][3];
                            if (lines[j][0].Trim() == "ACCTALLODTLS")
                            {
                                aloc.Amount = Convert.ToDouble(lines[j][4].Trim());
                                aloc.CostBasis = Convert.ToDouble(lines[j + 1][4].Trim());
                                j = j + 1;
                                lstallocation.Add(aloc);
                                i = j + 1;
                            }
                            else
                            {
                                i = i - 1;
                                break;
                            }                           
                        }
                        else
                        {
                            break;
                        }                      
                    }
                    ad.AllocDetails = lstallocation;
                    lstAccountDetails.Add(ad);
                }
            }
            Console.ReadLine();
        }
    }
</allocation></allocation></accountdetails></accountdetails>

How to pass specific portion of the csv file

2 solutions

Solution 1

Solution 3

Add your solution here

Preview 0