Putting data in word or excel document in a database

[herman404]

Hi everyone,

I have data coming to me in the form of Word and Excel files (this data is coming to us from an external source, and I need to get the text in these Word files into the DB), and I need to place this data into a SQL Server database. The Word file will be coming in text which is similiar to an Excel sheet, but instead of being comma delimited, it will be delimited by spaces. Regarding the Word document, I can set rules for the field length myself, but my question is regarding the proper programming APIs to use. Can this be done via VB scripts on the documents, or as a seperate .NET
application? I have access to both the C# and VB.NET platforms. (And
J2EE as well, although a Microsoft platform would be more appropriate,
IMHO). Any ideas on the best way to do this?

Thanks,

 

[jmcilhinney]

Getting the data into the database will be the easy part with ADO.NET. Getting it out of Word files is not something that you can do with standard .NET classes though. Unless you know the Word file format and want to read the DOC file in binary form and then convert the relevant parts to text, you're going to need to actually use the Word application to do it for you. You can control an instance of Word in a .NET application using Office Automation. You simply add a reference to the Word type library to your app and you have access to the entire Word object model. You can read about Office Automation and Word Automation specifically on MSDN or probably numerous other Web sites.

http://msdn.microsoft.com/library/d...re/html/wroriautomatingofficeapplications.asp

 

[herman404]

Thanks for the reply, if we could get the Word document as a text file and the Excel file as a csv file, everything else would be a snap. Is the best way to do this as a VB6 script off the document itself? I'll research the Office Automation link you provided as well

 

[jmcilhinney]

There's no reason to be using VB6. Like I said, once you've added a reference to the Word type library you get full access to the Word object model in your VB.NET app. As long as you have the Word application installed on the machine on which you're running this application you'll have no issues.