How Do I Import Csv File Data Which Is In Vertical Form ?

Question

1.00/5 (1 vote)

See more:

i want to import the following file data in database as

id name
10001 Hemant Desai

as regular table data so how can i read this data i got some confusion in this.

here is csv file data....

ID=,10001,
Name=,Hemant Desai,
Age=,60,
Sex=,male,
Doctor=,Pathak,
Mobile=,9021412202,
Alignment=,brain toumer,
No of medicins=,3,
12:02,Stamlo-5,1mg,oral,after meal,*XE0280916*
12:01,Atorfit-CV-10,4mg,oral,after meal,*XE0283337*
12:01,Losar,3mg,oral,after meal,*XE0284350*
12:02,Appointment,X ray of right chest at 11.00 am on Wed 11th Dec

12:01,procedure,Sponge patient with warm water

12:01,Measurement,put
Temperature =,222
 Blood Pressure =, 555/555
Pulse Rate =, 555
Respiratory Rate =, 999

Posted 12-Apr-15 0:12am

Member 11543226

Updated 12-Apr-15 0:18am

Maciej Los

v2

Add a Solution

Comments

Maciej Los 12-Apr-15 6:19am

What have you tried till now? Where are you stuck?

Sascha Lefèvre 12-Apr-15 6:29am

That looks like 1 record. How would it look like if there are two records?

Richard MacCutchan 12-Apr-15 6:48am

That is not CSV, so you will need to write your own parser to process it.

Sinisa Hajnal 12-Apr-15 9:45am

Looks like custom format, you'll need to make your own algorithm...depending on how it would look with more then one ID entry, you could maybe remove all new lines and try it that way...I would go with while loop and readline filling custom object Patient until I found next ID tag.

3 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)

Dave Kreskowiak · Answer 1 · 2015-04-12T10:11:00

Solution 1

That's not a CSV file. A CSV has every field in a record all on one line, with a separator between fields, usually a comma, hence Comma Separated Values.

What you have a some off-the-wall custom format that you're going to have to write a parser for.

Posted 12-Apr-15 10:11am

Dave Kreskowiak

Maciej Los · Answer 2 · 2015-04-12T12:05:00

Linq solution might look like:

C#

string filename = @"FullFileNameWithPath.csv";
//get all lines from csv file
string[] lines = File.ReadAllLines(filename);
//get only id's
var ids = lines
    .Where(a=>a.Trim()!=string.Empty && a.Contains("ID="))
    .Select((a,x)=>new{ID=Convert.ToInt32(a.Split(',')[1]), index =x});
//get names
var names = lines
    .Where(a=>a.Trim()!=string.Empty && a.Contains("Name="))
    .Select((a,x)=>new{Name=a.Split(',')[1], index =x});
//get patiens; join ids and names on index
var patients = from id in ids
		join name in names on id.index equals name.index
		select new 
		{
			ID = id.ID,
			Name = name.Name
		};

Result:

ID    Name
10001 Hemant Desai

Note, that patients returns Enumerable(ID, Name). Now you can loop through that data by using:

C#

foreach(var p in patients)
{
    Console.WriteLine("{0}\t{1}", p.ID, p.Name);
}

and save it to the database.

Maciej Los · Answer 3 · 2015-04-13T20:38:00

If you wnat to get all data, use:

C#

string sFileName = @"fullfilename.csv";
	string[] lines = File.ReadAllLines(sFileName);
	
	int index = 0;
        //get vertical data 
	var verticaldata = lines
			.Where(a=>a.Trim()!=string.Empty && a.Contains("="))
			.Select(a=>Tuple.Create(a.Contains("ID=") ? index+=1 : index, a.Split(',')[0].Replace("=","").Trim(), a.Split(',')[1].Trim()));
	//transform to horizontal ;)
	var horizontaldata = verticaldata
			.GroupBy(r=>r.Item1)
			.Select(
				grp=>new 
				{
					//grp
					//RowNo = grp.Key,
					ID = grp.Where(r=>r.Item2.Contains("ID")).Select(a=>a.Item3).First(),
					Name = grp.Where(r=>r.Item2.Contains("Name")).Select(a=>a.Item3).First(),
					Age = grp.Where(r=>r.Item2.Contains("Age")).Select(a=>a.Item3).First(),
					Sex = grp.Where(r=>r.Item2.Contains("Sex")).Select(a=>a.Item3).First(),
					Doctor = grp.Where(r=>r.Item2.Contains("Doctor")).Select(a=>a.Item3).First(),
					Mobile = grp.Where(r=>r.Item2.Contains("Mobile")).Select(a=>a.Item3).First(),
					Alignment = grp.Where(r=>r.Item2.Contains("Alignment")).Select(a=>a.Item3).First(),
					No_of_Medicin =  grp.Where(r=>r.Item2.Contains("No of medicins")).Select(a=>a.Item3).First() ,
					Temperature = grp.Where(r=>r.Item2.Contains("Temperature")).Select(a=>a.Item3).First()
				});

Result:

ID    Name         Age Sex  Doctor Mobile     Alignment    No_of_Medicin Temperature
10001 Hemant Desai 60  male Pathak 9021412202 brain toumer 3             222

How Do I Import Csv File Data Which Is In Vertical Form ?

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0