LibXL data reading question

1

Can anyone tell me how robust LibXL is at reading Excel files of erratic formats?

Needed for a web app running on a server that does not have Excel installed, so Office Inside not an option.

Excel file always contains (at least) 3 worksheets, each of which needs to be extracted into a separate SQL table.

I had an SSIS job created to do this, then received a few more Excel files and they had different formats (one column missing, one datetime is a date, etc.) SSIS wants one format, thank you very much.

If it were CSV it would be possible to parse the columns with StringTheory and adjust the import to SQL accordingly.
If wishes were fishes.

If we could get the hospital system to settle on one format, that would be nice.

Lacking that, my question is if I buy the KlariSoft wrapper and LibXL library, will it be possible to parse the Excel file of undetermined format, then select the columns that are available and read the data from those so I can dump it into MS SQL?

Thanks for any thoughts. Or alternative approaches, if you can suggest.

2

Hi Jane, you do not need to know the excel sheet formats ahead of time with LibXL.
You’ll need to get the values from the columns to determine what is where.
You can loop through the sheets in the workbook to determine if they are there by sheet name or number.
LibXL is very powerful. I use in a number of the systems I work on and it’s been very good. Very fast.

3

If only reading, another option is to use the ODBC driver. I haven’t used it, but hear you don’t need Excel to use it.

4

Thanks, Rick. I was about to purchase when I read Jeff’s post.

Thanks, Jeff. I may be able to make this work.
I created a linked server pointing at the Excel file.
Can’t see a way to determine columns directly, but I can do a select * into dummytable for each and then using information_schema to find column names from that.

So I think I’ll fiddle with the no-cost approach first and keep LibXL in mind as an alternate possibility.

Thanks again!

1 Like
5

In my experience I’ve never had a problem reading formats with LibXL. It doesn’t behave stably when writing or it doesn’t warn correctly beyond row 1M. But otherwise it works perfectly and is super fast.

6

Interesting. I don’t think I’ve ever tried to write a million rows.
I did just finish a project where I was creating workbooks with 1500+ sheets in a single workbook.