Excel and OleDbDataAdapter. How can i ignore blank columns

Hi,

I have an application, that needs to import excel spreadsheets into a
SQL Server
database.


I am using an OleDBDataAdapter to load (fill) the data into a
Dataset.

This works fine, when the spreadsheet is formatted correctly.


Occasionally, person sending us the file, will put the data starting
in column B, not column A. This fails our checks on column count.

They also have started in the third row, which messes up the column
headings. I Get F1 -F6 for the
column headers and only three of the six actual column heading are
read in (as data). The other three
column headings are read in as blanks.

Also occasionally, an extra blank column after the data gets read
in. Well it looks blank in the spreasheet, but I'm assuming they have
done something to it.

Is there an easy way for me to get the DataAdapter to ignore blank
columns and rows?

At the moment, I'm (in my opinion) quite rightly rejecting the file,
but I would like my application to
be more fault tolerant and intellegent.

Rollasoc

Hi,

Well maybe OleDB is not the right way to make your application fault
tolerant. Maybe you should try some third party component for reading
Excel files. If you don't need more than 5 sheets and 150 rows per
sheet, then you could use our free component that you can freely use
in commercial apps. Later if you need more then 150 rows or more then
5 sheets, you can easily update to GemBox.Spreadsheet professional.

Mario
GemBox Software
--
GemBox.Spreadsheet for .NET - Easily read and write Excel (XLS, XLSX
or CSV) files
or export to HTML files from your .NET apps. See
http://www.gemboxsoftware.com/GBSpreadsheet.htm
--

On Apr 25, 9:21 am, Rollasoc <rolla...@hotmail.com> wrote:
> Hi,
>
> I have an application, that needs to import excel spreadsheets into a
> SQL Server
> database.
>
> I am using an OleDBDataAdapter to load (fill) the data into a
> Dataset.
>
> This works fine, when the spreadsheet is formatted correctly.
>
> Occasionally, person sending us the file, will put the data starting
> in column B, not column A. This fails our checks on column count.
>
> They also have started in the third row, which messes up the column
> headings. I Get F1 -F6 for the
> column headers and only three of the six actual column heading are
> read in (as data). The other three
> column headings are read in as blanks.
>
> Also occasionally, an extra blank column after the data gets read
> in. Well it looks blank in the spreasheet, but I'm assuming they have
> done something to it.
>
> Is there an easy way for me to get the DataAdapter to ignore blank
> columns and rows?
>
> At the moment, I'm (in my opinion) quite rightly rejecting the file,
> but I would like my application to
> be more fault tolerant and intellegent.
>
> Rollasoc

On Fri, 25 Apr 2008 00:21:34 -0700 (PDT), Rollasoc <rollasoc@hotmail.com> wrote:

¤ Hi,
¤
¤ I have an application, that needs to import excel spreadsheets into a
¤ SQL Server
¤ database.
¤
¤
¤ I am using an OleDBDataAdapter to load (fill) the data into a
¤ Dataset.
¤
¤ This works fine, when the spreadsheet is formatted correctly.
¤
¤
¤ Occasionally, person sending us the file, will put the data starting
¤ in column B, not column A. This fails our checks on column count.
¤
¤ They also have started in the third row, which messes up the column
¤ headings. I Get F1 -F6 for the
¤ column headers and only three of the six actual column heading are
¤ read in (as data). The other three
¤ column headings are read in as blanks.
¤
¤ Also occasionally, an extra blank column after the data gets read
¤ in. Well it looks blank in the spreasheet, but I'm assuming they have
¤ done something to it.
¤
¤ Is there an easy way for me to get the DataAdapter to ignore blank
¤ columns and rows?
¤
¤ At the moment, I'm (in my opinion) quite rightly rejecting the file,
¤ but I would like my application to
¤ be more fault tolerant and intellegent.

You can select by an Excel Range but you still have to determine where the data starts and ends,
which can probably be done with an initial SELECT on the Worksheet.

Selecting data by Range:

SELECT * FROM [Sheet1$A3:C100]


Paul
~~~~
Microsoft MVP (Visual Basic)