Category: [Competition Entries]

12 Apr 2015 22:55

Joined:	03/03/2015
Level:	Silver
Points:	104

Points:	5

How to read csv file in vertical format and put into table in horizontal format

Are you looking for a way to read csv file in vertical format and put into table in horizontal format ? then read this thread to know how to do it

Hey friends i am stuck while doing this . i want to import the following csv file and save in database table into horizontal format and in line8 there is "No of medicins" and it contains many values and i want to store all that values in No of medicins column . i think i have to remove "," while reading this line and "=" also remove while importing . but im go through all stuffs but not find working answer. please help me . thnks

here is the file>>>>>

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
Temperature =,222
Blood Pressure =, 555/555
Pulse Rate =, 555
Respiratory Rate =, 999

#757880

13 Apr 2015 05:33

Joined:	13/06/2007
Level:	Gold
Points:	21327

Points:	3

When you say .CSV file it is a comma separated values after your 'no of medicines' columns there are different values separated by comma and all are then converted to different columns,
In my opinion, first you need to remove all comma's and then read each line of file using simple textReader and split your line with '=', it will gives you proper values to insert to table

Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]

#757882

13 Apr 2015 05:38

Joined:	03/03/2015
Level:	Silver
Points:	104

Points:	1

I can't remove quammas this format is fixed can we do to skip splitting after no of medicine and again start at temperature by comparing string .

#757890

13 Apr 2015 22:10

Joined:	03/03/2015
Level:	Silver
Points:	104

Points:	1

i am tried with this code following...

private DataTable ReadFiles(string sourceDir)
{
var IcsvFile = Directory.EnumerateFiles(sourceDir, "*.csv");
DataTable dt = new DataTable();
dt.Columns.Add("Id", System.Type.GetType("System.Int32"));
dt.Columns.Add("Name", System.Type.GetType("System.String"));
dt.Columns.Add("Age", System.Type.GetType("System.Int32"));
dt.Columns.Add("Sex", System.Type.GetType("System.String")); // Gender in proper english
dt.Columns.Add("Doctor", System.Type.GetType("System.String"));
dt.Columns.Add("Mobile", System.Type.GetType("System.String"));
dt.Columns.Add("Alignment", System.Type.GetType("System.String"));
dt.Columns.Add("No of medicins", System.Type.GetType("System.String"));
dt.Columns.Add("Temperature ", Type.GetType("System.Int32"));
dt.Columns.Add("Blood Pressure", System.Type.GetType("System.String"));
dt.Columns.Add("Pulse Rate", System.Type.GetType("System.String"));
dt.Columns.Add("Respiratory Rate", System.Type.GetType("System.Int32"));
foreach (string currentFile in IcsvFile)
{
ImportSingleFile(currentFile, ref dt);
}
return dt;
}

private void ImportSingleFile(string FilePath, ref DataTable dt)
{
string[] Lines = File.ReadAllLines(FilePath);
string ColumnName, ColumnData;
int EqualSignIndex, intColumnData;
DataRow Row = null;
ColumnName = string.Empty;
foreach (string Line in Lines)
{
EqualSignIndex = Line.IndexOf("=");
if (EqualSignIndex > -1)
{
ColumnName = Line.Substring(0, EqualSignIndex);
// after = there is always , that we don't want in the data,
// and the line sometimes ends with a , that we also don't want
ColumnData = Line.Substring(EqualSignIndex + 2).TrimEnd(',');
}
else
{
ColumnData = Line;
}

if (ColumnName == "ID")
{
Row = dt.NewRow();
}

if (Row != null)
{
if (dt.Columns[ColumnName].DataType == Type.GetType("System.Int32"))
{
if (int.TryParse(ColumnData, out intColumnData))
{
Row[ColumnName] = intColumnData;
}
else
{
throw new InvalidDataException(string.Format("For column {0} an integer value is expected", ColumnName));
}
}
else
{
Row[ColumnName] = ColumnData;
}
}
}
}

but it uses user defined table valued type to store data in table through stored procedure. bt im using sql server 2008 and there is no option for creating type so i don't know this code running or not .

plz help me to sort this.

How to read csv file in vertical format and put into table in horizontal format

About dotnetspider.com

Quick Links

General

Services