471,353 Members | 1,786 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,353 software developers and data experts.

from excel to delimited.txt (hidden values in excel?)

I'm working on a program which reads through a directory of excel files
and parses them into a delimited text file. However, in each date field
12:00:00 AM is added at the end of the date (format: mm/dd/yyyy). I've
tried different date/custom formats for those fields in excel, always
with the same result Reformatting all of the excel files isn't really
an option anyway, due to the amount I am working with. I'm not exactly
sure why it is adding it, but I do know that I don't want it to. Any
ideas?

Feb 3 '06 #1
5 1658

KBuser wrote:
I'm working on a program which reads through a directory of excel files
and parses them into a delimited text file. However, in each date field
12:00:00 AM is added at the end of the date (format: mm/dd/yyyy). I've
tried different date/custom formats for those fields in excel, always
with the same result
So it's your program that is creating the text files, right? in which
case you need to look at where you are outputting the dates, rather
than the input. Remember that in Excel (as in .NET) all date values are
actually date-and-time values; and 12am (midnight) is zero as a time
value; so in fact when you have what you think is a 'pure date', such
as 3rd February 2006, it's actually stored as 3rd February 2006 00:00.
Reformatting all of the excel files isn't really
an option anyway, due to the amount I am working with. I'm not exactly
sure why it is adding it, but I do know that I don't want it to. Any
ideas?


It's not adding it, it's already there. Look to your output, not your
input.

--
Larry Lard
Replies to group please

Feb 3 '06 #2
Exactly. Heres a sample output.txt line:
13|ALTERNATIVE MEDICINE||2C|1 page|7054||Frequency|0.1||1|3|7/1/2005
12:00:00 AM|6/30/2006 12:00:00 AM|

Is there a way to not output the time with the date?
Perhaps some regex implementation?

Feb 3 '06 #3

KBuser wrote:
Exactly. Heres a sample output.txt line:
13|ALTERNATIVE MEDICINE||2C|1 page|7054||Frequency|0.1||1|3|7/1/2005
12:00:00 AM|6/30/2006 12:00:00 AM|

Is there a way to not output the time with the date?
Perhaps some regex implementation?


Show us the code that you have at the moment.

--
Larry Lard
Replies to group please

Feb 3 '06 #4
DataExcel.cs
private static DataSet GetSheet(string path)
{
string cText = "Select * from [sheet1$]";
string strConn =@"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source="+path+@";Extended Properties=""Excel 8.0;HDR=NO""";
DataSet ds = new DataSet();
using(OleDbConnection conn = new OleDbConnection(strConn))
{
using(OleDbDataAdapter da = new OleDbDataAdapter(cText,conn))
{
da.Fill(ds,"sheet1");
}
conn.Close();
}
return ds;
}

public static RateData[] GetRateData(string path)
{
DataSet ds =DataExcel.GetSheet(path);
DataTable dt = ds.Tables["sheet1"];
RateData[] rt = new RateData[dt.Rows.Count];
DataRowCollection rows = dt.Rows;
for (int i = 0; i < dt.Rows.Count; i++)
{
rt[i].magazineID = rows[i][0].ToString();
rt[i].magazineName = rows[i][1].ToString();
rt[i].magazineSection = rows[i][2].ToString();
rt[i].color = rows[i][3].ToString();
rt[i].sizeName = rows[i][4].ToString();
rt[i].rate = rows[i][5].ToString();
rt[i].decimals = rows[i][6].ToString();
rt[i].discountType = rows[i][7].ToString();
rt[i].bleedRate = rows[i][8].ToString();
rt[i].discountRate = rows[i][9].ToString();
rt[i].fromVal = rows[i][10].ToString();
rt[i].toVal = rows[i][11].ToString();
rt[i].dFrom = rows[i][12].ToString();
rt[i].dTo = rows[i][13].ToString();
}
return rt;
}
public struct RateData
{
public string magazineID; // a
public string magazineName; // b
public string magazineSection; // c
public string color; // d
public string sizeName; // e
public string rate; // f
public string decimals; // g
public string discountType; // h
public string bleedRate; // i
public string discountRate; // j
public string fromVal; // k
public string toVal; //l
public string dFrom; // m
public string dTo; // n
}
}

Form1.cs

private void GetFileList(string xlpath)
{
string[] excellist = Directory.GetFiles(xlpath, "*.xls");
TextWriter tw = new StreamWriter(xlpath + "out.txt");
foreach(string fileloc in excellist)
{
//TextWriter tw = new StreamWriter(fileloc +
"out.txt");
DataExcel.RateData[] rtd =
DataExcel.GetRateData(fileloc);
foreach(DataExcel.RateData r in rtd)
{
tw.Write(r.magazineID + "|"); // a
tw.Write(r.magazineName + "|"); //b
tw.Write(r.magazineSection + "|"); //c
tw.Write(r.color + "|"); //d
tw.Write(r.sizeName + "|"); //e
tw.Write(r.rate + "|"); //f
tw.Write(r.decimals + "|"); // g
tw.Write(r.discountType + "|"); //h
tw.Write(r.bleedRate + "|"); //i
tw.Write(r.discountRate + "|"); //j
tw.Write(r.fromVal + "|"); //k
tw.Write(r.toVal + "|"); //l
tw.Write(r.dFrom + "|"); //m
tw.WriteLine(r.dTo + "|"); //n
}

}
tw.Close();
MessageBox.Show("Done");

}

Those are the guts of it... I tried to format it as best I could... I
am also in the middle of editting it, but I'm pretty sure this would
run properly as it is.

Feb 3 '06 #5


Change the following lines:
rt[i].dFrom = rows[i][12].ToString();
rt[i].dTo = rows[i][13].ToString();

To this:
rt[i].dFrom = rows[i][12].ToString("d");
rt[i].dTo = rows[i][13].ToString("d");
--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--
Feb 4 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by dave | last post: by
6 posts views Thread by frankplank | last post: by
1 post views Thread by Sanjeev Mahajan via .NET 247 | last post: by
1 post views Thread by Brian Conklin | last post: by
3 posts views Thread by herman404 | last post: by
15 posts views Thread by pakerly | last post: by
reply views Thread by XIAOLAOHU | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.