What options do I have for reading data from an excel spreadsheet, and what are the limitations of each?
-
I'm aware of at least two possibilities for reading directly from xls files: - Microsoft.Office.Interop.Excel - Microsoft.ACE.OLEDB.12.0 OleDB provider From what little information I've been able to find on the subject, it seems that the
Microsoft.Office.Interop.Excelmethod requires that Excel be installed on the system where this will be run. Is this true? Basically, I want a reliable (read: can be run on any system Windows 7 or higher with or without office installed) to read data from an XLS or XLSX spreadsheet. I don't need to do any writing to the format, I just need to read from it and dump the data into a SQLite or SQL Server Compact database table. Are there any methods other than those two I mentioned, and which would be the highest recommended method of doing what I need? Thanks. -
I'm aware of at least two possibilities for reading directly from xls files: - Microsoft.Office.Interop.Excel - Microsoft.ACE.OLEDB.12.0 OleDB provider From what little information I've been able to find on the subject, it seems that the
Microsoft.Office.Interop.Excelmethod requires that Excel be installed on the system where this will be run. Is this true? Basically, I want a reliable (read: can be run on any system Windows 7 or higher with or without office installed) to read data from an XLS or XLSX spreadsheet. I don't need to do any writing to the format, I just need to read from it and dump the data into a SQLite or SQL Server Compact database table. Are there any methods other than those two I mentioned, and which would be the highest recommended method of doing what I need? Thanks.agent154 wrote:
Is this true?
Yes. So I avoid it.
agent154 wrote:
Microsoft.ACE.OLEDB.12.0 OleDB provider
That's my preference.
agent154 wrote:
read from it and dump the data into a SQLite or SQL Server Compact database table.
That's the sort of thing that SSIS does.
You'll never get very far if all you do is follow instructions.
-
I'm aware of at least two possibilities for reading directly from xls files: - Microsoft.Office.Interop.Excel - Microsoft.ACE.OLEDB.12.0 OleDB provider From what little information I've been able to find on the subject, it seems that the
Microsoft.Office.Interop.Excelmethod requires that Excel be installed on the system where this will be run. Is this true? Basically, I want a reliable (read: can be run on any system Windows 7 or higher with or without office installed) to read data from an XLS or XLSX spreadsheet. I don't need to do any writing to the format, I just need to read from it and dump the data into a SQLite or SQL Server Compact database table. Are there any methods other than those two I mentioned, and which would be the highest recommended method of doing what I need? Thanks.Just an FYI since the other guy didn't mention it at all... * Your first choice, of course requires that Excel be installed, but is a lot less work and is more platform independent. * Your second choice does not... HOWEVER, and this is a BIG HOWEVER because if you don't know about this, you'll spend **weeks** trying to figure out what's going on... Microsoft ACE is the 64-bit driver. That's for a 64-bit OS and a 64-bit process **ONLY**. Won't run on a 32-bit OS or if your process is 32-bit. No big deal you're thinking, right? Not quite... if the machine happens to have 32-bit Office installed, you can't even install the 64-bit Ace driver, regardless of your OS because you aren't allowed to mix bitage. You have to install a different 32-bit driver and that limits your process to running as 32-bit because you can't use the 32-bit driver from a 64-bit process or vice versa. This causes a lot of issues... you'd have to have a 32-bit build that uses the 32-bit driver if the machine has 32-bit Office installed and a 64-bit build that uses the 64-bit driver if the machine has 64-bit Office installed. Most 64-bit machines I've seen have 32-bit Office installed... just sayin'... so you're limiting yourself to a 32-bit process.
-
Just an FYI since the other guy didn't mention it at all... * Your first choice, of course requires that Excel be installed, but is a lot less work and is more platform independent. * Your second choice does not... HOWEVER, and this is a BIG HOWEVER because if you don't know about this, you'll spend **weeks** trying to figure out what's going on... Microsoft ACE is the 64-bit driver. That's for a 64-bit OS and a 64-bit process **ONLY**. Won't run on a 32-bit OS or if your process is 32-bit. No big deal you're thinking, right? Not quite... if the machine happens to have 32-bit Office installed, you can't even install the 64-bit Ace driver, regardless of your OS because you aren't allowed to mix bitage. You have to install a different 32-bit driver and that limits your process to running as 32-bit because you can't use the 32-bit driver from a 64-bit process or vice versa. This causes a lot of issues... you'd have to have a 32-bit build that uses the 32-bit driver if the machine has 32-bit Office installed and a 64-bit build that uses the 64-bit driver if the machine has 64-bit Office installed. Most 64-bit machines I've seen have 32-bit Office installed... just sayin'... so you're limiting yourself to a 32-bit process.
AAAHHhhhh and they wonder why I dump on Office from a great height every opportunity I get. I hate having to deal with Office version incompatibilities, now you tell me the processor type may be an issue. Ok so now I can see a benefit to the 32 bit OS policy I have been muttering about for some time.
Never underestimate the power of human stupidity RAH
-
I'm aware of at least two possibilities for reading directly from xls files: - Microsoft.Office.Interop.Excel - Microsoft.ACE.OLEDB.12.0 OleDB provider From what little information I've been able to find on the subject, it seems that the
Microsoft.Office.Interop.Excelmethod requires that Excel be installed on the system where this will be run. Is this true? Basically, I want a reliable (read: can be run on any system Windows 7 or higher with or without office installed) to read data from an XLS or XLSX spreadsheet. I don't need to do any writing to the format, I just need to read from it and dump the data into a SQLite or SQL Server Compact database table. Are there any methods other than those two I mentioned, and which would be the highest recommended method of doing what I need? Thanks.There are also some libraries for reading (and sometimes also writing) Microsoft Office files which do not need MS Office or OleDB installed. See e.g. here on CP Use Cross-Platform/OSS ExcelDataReader to Read Excel Files with No Dependencies on Office or ACE[^]. Google may find some more libraries.
-
I'm aware of at least two possibilities for reading directly from xls files: - Microsoft.Office.Interop.Excel - Microsoft.ACE.OLEDB.12.0 OleDB provider From what little information I've been able to find on the subject, it seems that the
Microsoft.Office.Interop.Excelmethod requires that Excel be installed on the system where this will be run. Is this true? Basically, I want a reliable (read: can be run on any system Windows 7 or higher with or without office installed) to read data from an XLS or XLSX spreadsheet. I don't need to do any writing to the format, I just need to read from it and dump the data into a SQLite or SQL Server Compact database table. Are there any methods other than those two I mentioned, and which would be the highest recommended method of doing what I need? Thanks. -
I'm aware of at least two possibilities for reading directly from xls files: - Microsoft.Office.Interop.Excel - Microsoft.ACE.OLEDB.12.0 OleDB provider From what little information I've been able to find on the subject, it seems that the
Microsoft.Office.Interop.Excelmethod requires that Excel be installed on the system where this will be run. Is this true? Basically, I want a reliable (read: can be run on any system Windows 7 or higher with or without office installed) to read data from an XLS or XLSX spreadsheet. I don't need to do any writing to the format, I just need to read from it and dump the data into a SQLite or SQL Server Compact database table. Are there any methods other than those two I mentioned, and which would be the highest recommended method of doing what I need? Thanks.agent154 wrote:
requires that Excel be installed on the system where this will be run.
What is the Business case where that matters? If this is a client machine and the spread sheet is just a read only data store that comes with the application why not use a different format? If a client machine where a user can modify the file then they would need excel anyways. If a server machine then the situation is similar to the above but there should be less concern about cost.
