Datetime valies with xlsReader

[Buda56]

Hi,
I am importing data from an excel spreadsheet using the xlsreader
and am having an issue with some datetime columns.

The columns have both date and time in the cell but when I import the
data from the cell it is only importing the date value.

I have stepped through my code and looked at the values from both the
spreadsheet and this is what i see:

Spreadsheet - 07/01/2009 10:56 AM
Code shows - 07/01/2009.

Here is my code:
CsvReader csvData = new CsvReader(strPath + "\\" + txtFile);
csvData.ReadHeaders();
try
{
//Determine number of Columns..
cHeader = csvData.Headers;
for (counter = 0; counter < cHeader.Length; counter++)
{
if (cHeader[counter] != "")
HeadCount += 1;
}

if (HeadCount == 14)
{
ErFlag = 0;
//Potential Correct File - AP1135 report
for (counter = 0; counter < cHeader.Length; counter
++)
{
if (cHeader[counter].ToString().ToUpper() ==
"CUSTOMER NAME")
Flag += 1;
if (cHeader[counter].ToUpper() == "CALLER
NAME")
Flag += 1;
if (cHeader[counter].ToUpper() == "SOS")
Flag += 1;
if (cHeader[counter].ToUpper() == "SITE")
Flag += 1;
if (cHeader[counter].ToUpper() == "ASSIGNED
SPG")
Flag += 1;
if (cHeader[counter].ToUpper() == "STATUS")
Flag += 1;
if (cHeader[counter].ToUpper() == "DATE
CREATED")
Flag += 1;
if (cHeader[counter].ToUpper() == "DATE
RESOLVED")
Flag += 1;
if (cHeader[counter].ToUpper() == "DATE
CLOSED")
Flag += 1;
if (cHeader[counter].ToUpper() == "TIME TO
RESOLVE")
Flag += 1;
if (cHeader[counter].ToUpper() == "RESULT")
Flag += 1;
if (cHeader[counter].ToUpper() == "EXCUSABLE")
Flag += 1;
if (cHeader[counter].ToUpper() == "SEVERITY")
Flag += 1;
}
if (Flag != 13)
ErFlag = 1;
else
ErFlag = 0;
} if (Flag == 13 && ErFlag == 0)
{
//Correct AP1135 report
Lnum = 0;
recUpd=0;
recAdd=0;
while (csvData.ReadRecord())
{
Lnum++;
cData = csvData.Values;
rtFlag = 0;
rtFlag = ImportData(cData);
if (rtFlag == 1)
recAdd++;
else
recUpd++;
}

}

Import Data subroutine:
private int ImportData(string[] cData)
{
int myAdd = 0;
DateTime SDate;
DateTime EDate;
TimeSpan DaysOpen;
IncidentsCollection IColl = new IncidentsCollection();
Incidents Ient = new Incidents();
IColl.Query.Where(IColl.Query.CaseID.Like(cData[0]));
IColl.Query.Load();
if (IColl.Count == 0)
{
//Add new record
//Ient.AddNew();
//Ient.CaseID = cData[0];
//Ient.Customer = cData[1];



myAdd = 1;
}
else
{
//Update existing record
Ient.Query.Where(Ient.Query.CaseID.Like(cData[0]));
Ient.Query.Load();
Ient.SDate = Convert.ToDateTime(cData[7]);
Ient.RDate = Convert.ToDateTime(cData[8]);
if (cData[9] != "")
Ient.EDate = Convert.ToDateTime(cData[9]);
else
EDate = DateTime.Today;
Ient.TTResolve = Convert.ToInt32(cData[10]);
if (cData[11] != "")
Ient.Result = cData[11];
if (cData[12] != "")
Ient.Excusable = cData[12];
SDate = Convert.ToDateTime(cData[7]);
EDate = Convert.ToDateTime(cData[9]);
DaysOpen = EDate.Subtract(SDate);
Ient.DaysOpen = DaysOpen.Days.ToString();




myAdd = 2;
}

//Ient.Save();
return myAdd;
}

End Code.

Am I missing a setting or something to allow for all of the date
field??

Regards..
Peter.

[shriop]

I'm a little confused because you said that this has to do with an
Excel spreadsheet and the XlsReader but the code you provided is for
csv files using CsvReader. No, there's no setting if you are talking
about XlsReader. Generally what controls the output from XlsReader is
the format code and/or format string that is set on the cell in Excel.
There are built in system codes that can be specified or you can
choose custom and specify the format string. The format strings are
not an exact science as they use the same character for both minutes
and months but XlsReader should be using the same magic code that
Excel uses to determine the format. If you could send me a simple
example file that replicates the problem I would be able to track down
the issue.

Bruce Dunwiddie

[Buda56]

Bruce,
Sorry I posted the code that works using the CsvReader rather
than the XlsReader, I will put together an example that shows the
issue.

Regards..
Peter.

> > Peter.- Hide quoted text -
>
> - Show quoted text -

[Buda56]

Bruce,
Further investigation has show that, if I reformat one of
the date columns in the excel spreadsheet to custome foramt of "dd/mm/
yyyy hh:mm" and then run the import. During debug of the line I can
see the reformatted column has the correct data i.e. "07/01/2009
10:56" but the other non formatted date columns only have the date.

I have emailed you the specified file so you may have a look.

Regards..
Peter

> > - Show quoted text -- Hide quoted text -

[shriop]

It looks like an odd custom formatting with unneeded escape
characters. The current format is d\/mm\/yyyy" "h":"mm":"ss AM/PM and
the formatting I would expect is d/mm/yyyy h:mm:ss AM/PM, without all
the escape characters, and this format works. I will have to look up
all the valid escape sequences and handle them all and add this in the
next version. I unfortunately don't have an easy code fix. Your only
immediate option is to go in and manually change the formatting of
these columns to just a system date format or a more standard custom
format.

Bruce Dunwiddie

On Jan 18, 7:54 pm, Buda56 <peter.annand...@eds.com> wrote:
> Bruce,
>           Further investigation has show that, if I reformat one of
> the date columns in the excel spreadsheet to custome foramt of "dd/mm/
> yyyy hh:mm" and then run the import. During debug of the line I can
> see the reformatted column has the correct data i.e. "07/01/2009
> 10:56" but the other non formatted date columns only have the date.
>
> I have emailed you the specified file so you may have a look.
>
> Regards..
> Peter
>

[Buda56]

Bruce,
Thanks for the quick response, I will maintain my work around for
the moment. I have written an excel macro that reformats the columns
to a custom forma of "dd/mm/yyyy hh:mm" and then it saves it to my
inport directory where I pick it up with the XslReader and import into
my database.

Regards..
Peter.