Date fields in XLS file

[kimoy]

hi,

i have 2 xls files where 1 contains 6000+ records, and the other
containing the first 10 records of the 6000-record XLS file.

when reading thru the smaller file using CSV reader, all date fields
(with proper format) are fetched correctly -- in integer format. but
when reading thru the file with 6000 records, the date fields are
fetched as string in the format "mm/dd/yyyy" instead of integer. CSV
reader was able to fetch the value 39122 on the smaller file, but
fetched "2/9/2007" on the bigger file.

please advise.

thanks.

[shriop]

Are the files .csv or .xls? Are you using CsvReader or XlsReader? Why
are you wanting date fields read as integers? CsvReader will return
the values exactly as they are in the file. XlsReader will always
return dates in the default format of the operating system.

Bruce Dunwiddie

[kimoy]

Bruce,

The files are in XLS format and I'm using XlsReader. It's not that I
want date fields read as integers but that is what XlsReader is giving
me --- date fields, as long as the format is correct, is fetched as
integer. That's why I have in my code the following line:

DateTime value = new DateTime(1899,12,30);
value.AddDays(dateField);

Where "dateField" is an int variable coverted from a string-formatted
integer.

Thanks.

[shriop]

What format option are you choosing in Excel to tell Excel that it's a
date field? Can you email me a sample spreadsheet that shows XlsReader
reading a date formatted cell as an integer? XlsReader should return
all date fields as a formatted date, but the underlying storage in
Excel is an integer, so the only way it determines that it needs to
format as a date is based on the format mask of the cell based on some
rather problematic logic.

Bruce Dunwiddie

> > > thanks.- Hide quoted text -
>
> - Show quoted text -

[shriop]

Ok, after looking over your file, I can see what's wrong internally,
but I'm trying to decide the best way to handle fixing the issue. I'm
currently guessing that this file was not actually generated by Excel
because the format mask actually looks to be in the wrong casing to
me, and if I resave the file from Excel, it reads correctly. What
program did you use to generate this file?

Bruce Dunwiddie

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

[shriop]

Sorry, now I see that you told me that the file came from a website.
Is it a popular website where I could go play with it myself? Or is it
just a random small website?

Bruce Dunwiddie

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

[kimoy]

bruce,

i got this file from this link

http://www.bankofengland.co.uk/publications/financialsanctions/index.htm

thanks.

[shriop]

Sorry this keeps going on. Anyhow, I download the 7200ish records and
it opens fine. So you used OpenOffice to make the smaller document
that you then had problems with?

Bruce Dunwiddie

[kimoy]

Since I was testing my code using XlsReader I have to use a minimum
number of records to parse. So instead of parsing the 6000+ XLS file,
I opened it using OpenOffice, and just saved the first 10 records to a
separate XLS file. All along I thought that date fields when read
using XlsParser returns integer because this was the case with the
smaller file. The bigger file returns string so I presumed there was a
problem.

[jwcolby]

Bruce,

Does the e object used in the csvData_ReadRecord() allow me to get the total
length of the string read, or the string itself that was read from the file?

I got an error from System.Data.SqlClient.SqlException which claims that I
tried to feed in a column longer than 100K bytes. The error message told me
the exact line number and column number. I used UltraEdit to open the file
and move to that line number. I expected to visually see the error but in
fact I do not see any indication that the error was real.

What I really want to do is build functionality that allows me to log (in
the event of error) the longest value ever encountered in every field, as
well as the longest length of any single string read from the file, stuff
like that. I do not know if that is already available in other properties
of the csvData object but if not I could do that kind of thing in the
ReadRecord event handler though it would be painstakingly slow I suspect.

OTOH, having a file with 9 million lines error out at line 8881595 pretty
much sucks (that happened). So I need to be able to log what file and what
line number I errored out on and then "move back there" to start the import
over once the error is corrected.

Any wisdom on this subject would be much appreciated.

Thanks,

John W. Colby
Colby Consulting
www.ColbyConsulting.com

[shriop]

There's quite a few parts to this answer, but it's all stuff that you
should be aware of. The exception that you're referring to is not of
type SqlException, it's an IOException. It's possible that it's being
wrapped up inside a SqlException, but the inner exception should be
IOException. This is not an exception being thrown by Sql Server, it's
a low level exception that I've built into the low level parser.
Because I have no actual hard limit on the size of a column or record,
I buffer all the contents into memory until the end of the column is
reached. In an instance where the end of the column can't be
recognized, like if the wrong encoding is specified making the
delimiter unrecognizable, this buffering would keep going, until in
your circumstance, the server runs out of memory. Because of this
possibility, I built in a safety catch to have the parser bail if it
encounters a column longer than 100,000 characters. I also added a
setting called SafetySwitch that is set to true by default that would
allow you to disable this safety catch. That's the exception that you
ran into which is why you got such a detailed error message all the
way down to the column index and record index. The only instance I've
seen where this exception is thrown in a non straightforward manner is
when you're dealing with a tab delimited file, but you have not
disabled the use of text qualifiers and the parser ran into a column
that started with a quote, so it thinks that it's still going inside a
cell, even when it runs into line endings. My suggestion for this
specific file is to go through all the settings on the parser and make
sure you understand what they do and that you have them set to what
you actually want.

Now, for your logging and exception handling. Most of what it sounds
like you want to do in the ReadRecord event shouldn't cause a real
performance issue in calculating lengths and such, but obviously would
if you're logging them back out to a file or database. You might even
be ok logging to a file as long as you're not opening the file for
every call, but you could use like a class scoped writer to avoid
reopening the file. Same way with the last successfully loaded record
index, you could save CurrentRecord that exists in the ReadRecord
event off to a class scoped variable so you know what record to jump
back to. Now a problem that you're going to run into is that the data
is being loaded inside a transaction, which is what you want for
speed, but it means that the last record sent through ReadRecord won't
actually be the last successfully commited record. Now you could set
the BatchSize on SqlBulkCopy to directly match NotifyAfter on
SqlBulkCopy, and I think that would tell you what was the last record
that was actually commited, but you'd need to verify that. You do have
access to column values inside ReadRecord, but ReadRecord won't
actually be fired on this problem row because the parser is throwing
the exception before that event is fired. Once this exception is
thrown, based on the severity I consider this exception to be, the
parser closes down, so you won't be able to skip this record and go on
to the next. Generally, if you're wanting to do this kind of exception
handling, I would recommend making a run at the file first with
CsvReader and validating the entire file before attempting to bulk
load the file. Then, you can do the best logging of exactly what is
wrong and do whatever kind of validation you see fit. CsvReader also
has a RawRecord property which returns the entire contents of the
current row. Even at your data volume, the time to parse the file
really shouldn't be all that much in comparison, especially for the
added logging and verification it gives you.

Bruce Dunwiddie

> Bruce Dunwiddie- Hide quoted text -

[jwcolby]

Bruce,

Right you are for the IOException.

>The only instance I've seen where this exception is thrown in a non
straightforward manner is when you're dealing with a tab delimited file, but
you have not disabled the use of text qualifiers and the parser ran into a
column that started with a quote, so it thinks that it's still going inside
a cell, even when it runs into line endings.

Hmm... I am using a | delimiter (not tab) and I am not setting the text
qualifier directly. I assumed that unless I specified one then there would
not be one. Do you have a default of the quote or something? If so, what
should I set the property to? Null?

>Most of what it sounds like you want to do in the ReadRecord event
shouldn't cause a real performance issue

The only thing I have access to is the fields collection. In order to get
the max field size I would have to iterate this collection for every field
for every time the ReadRecord event fired. Many of my files have 100, 150
or more (640 in one case) fields. Doing something like that WILL cause a
performance hit if done for every ReadRecord event.

One issue I am trying to address is "what size fields do I need in my
table?". IOW these are completely unknown files to me. I get them and,
using your CSVData object discover the names of the fields (from the headers
collection) and build a table in SQL Server:

strSQL = "CREATE TABLE [" & lstrDBName & "].[dbo].[" &
mclsIOData.pTblName & "] ("
strFldNames = ""
For Each strName In .Headers
If Len(strFldNames) > 0 Then
strFldNames = strFldNames & ",[" & strName & "]
[varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS "
Else
strFldNames = "[" & strName & "] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS "
End If
Next strName

But as you can see I just "default" to some arbitrary field length. I
initially defaulted to 50 characters but I already ran into an instance
(field) where that was not enough so I had to stop and open up that field to
100 (again arbitrarily selected). It would be nice to be able to discover
from actual data in the file what was the maximum data size actually
encountered so that I could make adjustments to the table to build fields of
that size plus X%. There are LOTS of fields where the field size is just a
character ('Y', 'N', 'A'..'T' etc) so.

It appears that I need to run through a data file in the function where I
build this table and perform this process of iterating the fields collection
for every record to discover in advance the sizes so that I can then make
educated guesses instead of uneducated guesses. Given that the files parsed
are completely different from one client to the next, having any FIXED field
size in my build table code is going to be problematic.

>Now a problem that you're going to run into is that the data is being
loaded inside a transaction, which is what you want for speed, but it means
that the last record sent through ReadRecord won't actually be the last

successfully committed record.

I am sinking the OnSQLRowsCopied event:

Private Sub OnSqlRowsCopied(ByVal sender As Object, ByVal args As
SqlRowsCopiedEventArgs)
Debug.Print("Copied {0} so far...", args.RowsCopied)
mlngRecordsBulkCopied = args.RowsCopied
RaiseEvent evLinesComplete(args.RowsCopied)
Application.DoEvents()
End Sub

So I hope that I have that issue covered.

>CsvReader also has a RawRecord property

I am using the csvData object which does not appear to have this property.
I assume that CsvReader is the parent object of the CsvData object but it
does not appear to expose the RawRecord property. How do I get at that?

>You do have access to column values inside ReadRecord, but ReadRecord won't
actually be fired on this problem row because the parser is throwing the
exception before that event is fired.

Understood.

>Once this exception is thrown, based on the severity I consider this
exception to be, the parser closes down, so you won't be able to skip this
record and go on to the next.

Understood. If you can't discover the end of the column then you are toast.

>Generally, if you're wanting to do this kind of exception handling, I would
recommend making a run at the file first with CsvReader and validating the
entire file before attempting to bulk load the file.

Well the problem there is that then each file has to be processed twice
"just in case" it runs into such an issue. These files can contain millions
of rows with hundreds of columns. In the case I am working on now I
successfully processed 19 (out of 60) files before encountering an error.
These 19 files took just over three hours to process as it is. Processing
each file twice would roughly double the processing time - not good.

It seems more logical to me to go ahead and process the file normally, and
should it (or any other error) occur trap this IOError (I can do that
right?) and log that the error occurred, how many lines were successfully
posted to SQL Server, the line that caused the error etc. That would allow
me to move on to the next file. I do entire directories full of files at
once, moving them to an archive directory if they successfully process so I
could then come back to the problem files after the files with no issues are
finished. Since the problem files did not complete, they are not moved to
the archive directory and I can easily find the problem files. I embed the
data file name in the log file name so I can easily find the log file for
that input file. EVENTUALLY I will be logging this info to a process table
in SQL Server.

Kudos on your product, it has saved me tons of programming time, allowing me
to just "plug in" your functionality instead of rolling my own (which I was
doing when I discovered yours). The one major issue I have is the lack of
programming examples. This is a rather large black box and so examples of
how to set parameters, and in particular how to use the CSV writer and the
fixed width stuff is going to be a requirement. I started with the CSV
reader because that was what I needed the most but the CSV Writer is next on
my agenda. Then a flat file writer, then a flat file reader. I need all of
them and your product appears to have all of them, but with no example code
it is going to be tough to figure out I think.

Anyway, thanks for your assistance,

John W. Colby
Colby Consulting
www.ColbyConsulting.com