Parsing Excel Documents

I have a number of Excel documents that I would like to extract data from.
The data however is no in a CSV layout.  There are some rules on how the
file is laid out (for example the data rows i want are always after the
first empty row after the first lot of data in the file).

I wanted to know if there is an exensible consumer of Excel Docuemnts that
can be used in an MFC app.

I'll be using .NET studio 2002 and the code will be UNmanaged.

Thanks in advance.

MattC


0
MattC
7/25/2003 3:53:07 PM
vc.mfc 33608 articles. 0 followers. Follow

7 Replies
886 Views

Similar Articles

[PageSpeed] 20

>I wanted to know if there is an exensible consumer of Excel Docuemnts that
>can be used in an MFC app.

Matt,

You can "drive" Excel via its object model from your application. Have
a look at Knowledge Base article 196776 "Office Automation Using
Visual C++" for some background information.

Dave
--
MVP VC++ FAQ: http://www.mvps.org/vcfaq
0
davidl (422)
7/25/2003 4:27:09 PM
No I wont to be able to read IN a .xls file into my C++ app to the store the
data in some internal maps to combine with data from other sources (NON
excel) this data would then be manipulated and then stored in a DB.

MattC

"David Lowndes" <davidl@mvps.org> wrote in message
news:ogm2iv0dmsmn3vbs7nn6rfrcq4715q5um9@4ax.com...
> >I wanted to know if there is an exensible consumer of Excel Docuemnts
that
> >can be used in an MFC app.
>
> Matt,
>
> You can "drive" Excel via its object model from your application. Have
> a look at Knowledge Base article 196776 "Office Automation Using
> Visual C++" for some background information.
>
> Dave
> --
> MVP VC++ FAQ: http://www.mvps.org/vcfaq


0
MattC
7/25/2003 4:47:20 PM
>No I wont to be able to read IN a .xls file into my C++ app to the store the
>data in some internal maps to combine with data from other sources (NON
>excel) this data would then be manipulated and then stored in a DB.

If you want to process the file directly, have a look to see if you
can find information on how to handle BIFF (Excel's file format).
There are some references on MSDN.

You may be able to do what you want by using Excel's object model as I
originally mentioned - I don't know for sure.

Dave
-- 
MVP VC++ FAQ: http://www.mvps.org/vcfaq
0
davidl (422)
7/25/2003 10:11:16 PM
For low volumes, using the method David suggests works well.
You can read entire spreadsheets and in fact do about everything with excel
using that method.

An alternative is to use ADO to open the spreadsheet.
Take a loook at KB article 306023 in the section "Transfer Data to a
Worksheet by Using ADO.NET"  which is near the bottom of the article. It
relates to C#, but the principles apply equally. The Excel connection string
is what you need...

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_strSampleFolder +
	"Book7.xls;Extended Properties=Excel 8.0;");


- Tim


"MattC" <m@m.com> wrote in message
news:u#6LqTsUDHA.2252@TK2MSFTNGP10.phx.gbl...
> I have a number of Excel documents that I would like to extract data from.
> The data however is no in a CSV layout.  There are some rules on how the
> file is laid out (for example the data rows i want are always after the
> first empty row after the first lot of data in the file).
>
> I wanted to know if there is an exensible consumer of Excel Docuemnts that
> can be used in an MFC app.
>
> I'll be using .NET studio 2002 and the code will be UNmanaged.
>
> Thanks in advance.
>
> MattC
>
>


0
Tim
7/26/2003 1:29:48 AM
Will connecting to the spreadsheet as a database work if the spreadsheet
isn't set out like one table, its more like two or three tables in each
sheet each with different layout.

thanks

MattC
"Tim" <Tim@NoSpam> wrote in message
news:%23DJjWVxUDHA.3312@tk2msftngp13.phx.gbl...
> For low volumes, using the method David suggests works well.
> You can read entire spreadsheets and in fact do about everything with
excel
> using that method.
>
> An alternative is to use ADO to open the spreadsheet.
> Take a loook at KB article 306023 in the section "Transfer Data to a
> Worksheet by Using ADO.NET"  which is near the bottom of the article. It
> relates to C#, but the principles apply equally. The Excel connection
string
> is what you need...
>
> "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_strSampleFolder +
> "Book7.xls;Extended Properties=Excel 8.0;");
>
>
> - Tim
>
>
> "MattC" <m@m.com> wrote in message
> news:u#6LqTsUDHA.2252@TK2MSFTNGP10.phx.gbl...
> > I have a number of Excel documents that I would like to extract data
from.
> > The data however is no in a CSV layout.  There are some rules on how the
> > file is laid out (for example the data rows i want are always after the
> > first empty row after the first lot of data in the file).
> >
> > I wanted to know if there is an exensible consumer of Excel Docuemnts
that
> > can be used in an MFC app.
> >
> > I'll be using .NET studio 2002 and the code will be UNmanaged.
> >
> > Thanks in advance.
> >
> > MattC
> >
> >
>
>


0
MattC
7/28/2003 9:42:37 AM
Even so, you can still use Excel (OLE) Automation.  If you cannot use that,
then perhaps Excel's ODBC driver is an option.

"MattC" <m@m.com> wrote in message
news:#Nk58xsUDHA.1748@TK2MSFTNGP12.phx.gbl...
> No I wont to be able to read IN a .xls file into my C++ app to the store
the
> data in some internal maps to combine with data from other sources (NON
> excel) this data would then be manipulated and then stored in a DB.
>
> MattC
>
> "David Lowndes" <davidl@mvps.org> wrote in message
> news:ogm2iv0dmsmn3vbs7nn6rfrcq4715q5um9@4ax.com...
> > >I wanted to know if there is an exensible consumer of Excel Docuemnts
> that
> > >can be used in an MFC app.
> >
> > Matt,
> >
> > You can "drive" Excel via its object model from your application. Have
> > a look at Knowledge Base article 196776 "Office Automation Using
> > Visual C++" for some background information.
> >
> > Dave
> > --
> > MVP VC++ FAQ: http://www.mvps.org/vcfaq
>
>


0
7/28/2003 1:03:56 PM
Here's the file I will be retrieving data from:
http://www.djindexes.com/downloads/xlspages/change_file_DJGT50.xls

I wish to extract F21..F70 and T21..T70

How would that work using ODBC?

MattC

"David Crow [MCSD]" <david.crow.remoovethis@pbsnow.com> wrote in message
news:0b9Va.459$kv3.317913177@newssvr30.news.prodigy.com...
> Even so, you can still use Excel (OLE) Automation.  If you cannot use
that,
> then perhaps Excel's ODBC driver is an option.
>
> "MattC" <m@m.com> wrote in message
> news:#Nk58xsUDHA.1748@TK2MSFTNGP12.phx.gbl...
> > No I wont to be able to read IN a .xls file into my C++ app to the store
> the
> > data in some internal maps to combine with data from other sources (NON
> > excel) this data would then be manipulated and then stored in a DB.
> >
> > MattC
> >
> > "David Lowndes" <davidl@mvps.org> wrote in message
> > news:ogm2iv0dmsmn3vbs7nn6rfrcq4715q5um9@4ax.com...
> > > >I wanted to know if there is an exensible consumer of Excel Docuemnts
> > that
> > > >can be used in an MFC app.
> > >
> > > Matt,
> > >
> > > You can "drive" Excel via its object model from your application. Have
> > > a look at Knowledge Base article 196776 "Office Automation Using
> > > Visual C++" for some background information.
> > >
> > > Dave
> > > --
> > > MVP VC++ FAQ: http://www.mvps.org/vcfaq
> >
> >
>
>


0
MattC
7/30/2003 8:34:49 AM
Reply:

Similar Artilces:

Inserting files into excel worksheets
Why is there no option for inserting an files into an existing Excel worksheet similar to the option forinserting files into Word and PowerPoint files. I work for a CPA firm and this would be a valuable feature feature. "ccwies" <ccwies@discussions.microsoft.com> wrote in message news:FFC33867-4C40-45F8-89E0-540ABD94AC3C@microsoft.com... > Why is there no option for inserting an files into an existing Excel > worksheet similar to the option forinserting files into Word and > PowerPoint > files. I work for a CPA firm and this would be a valuable feature feature...

Excel 2003 Printing
How do I print multiple active worksheets in a workbook, twosided (duplex)? ...

Parsing Question
I've done parsing where you have one delimiter such as a comma or space. What if you have more than one? Example data: sStr(0) = "3/9" sStr(1) = "3/11 3/15-" sStr(2) = "3/8+ 3/12" sStr(3) = "3/9 3/15" sStr(4) = "3/8- 3/10+ 3/15" The strings above are Month/Day with some having an additional - or + character next to it. These dates are extracted from Excel cells as strings. I need to break those strings down into individual dates. So for sStr(4), I need to parse out "3/8", "3/10" and "3/15&...

How do I align ( ) in excel?
The ending bracket will align nicely. Is it that you want to align these negatives numbers in a column which also has positive numbers? -- Regards, Fred "jules" <jules@discussions.microsoft.com> wrote in message news:A4A2D8A0-5260-4C3F-94D3-C81E5A3579E6@microsoft.com... > This is done through number formats Use 0.00_);(0.00);0 The underscore character means leave a space the size of the following character The first part of format is positive numbers followed by negative followed by zero followed by text "Fred Smith" wrote: > The ending bracket ...

Excel 2007 #4
i have the first name in the cell of an e-mail list and i need to add the (@etc.com) can someone tell me how to do this mfm i need it to look like mfm@etc.com please help =A1&"@etc.com -- Regards, Peo Sjoblom "David TW" <David TW@discussions.microsoft.com> wrote in message news:6E7E4CEF-FA29-45EE-972A-C824767971DE@microsoft.com... >i have the first name in the cell of an e-mail list and i need to add the > (@etc.com) can someone tell me how to do this > > mfm i need it to look like mfm@etc.com > please hel...

Date conversions....Excel thinking too hard?
We have many programs (Access, SAP, etc.), that when they try to export to Excel, Excel always tries to convert our part numbers into dates. For instance, we have a part number, 1944-1-5, that needs to be exported to Excel as a text value, 1944-1-5, not converted to a date, like 1/5/1944. And no, putting quotes around it is not practical, since we have thousands of part numbers. I have successfully exported from Access using the TransferSpreadsheet method in a macro or VBA (instead of the standard "File | Export..." menu selection) without the date conversion occuring. Ho...

Excel report
I am new to Excel. I am doing a short course on it and have to write a short one page report on how it can manipulate data. The problem is all the books talk about excel in terms of examples. What I need is some basic information on Excel in general, just someone saying how good it is for modelling situations. Can anybody send me a few line, please! Steven:D --- Message posted from http://www.ExcelForum.com/ "Steven >" <<Steven.100zdo@excelforum-nospam.com> wrote in message news:Steven.100zdo@excelforum-nospam.com... > I am new to Excel. I am doing a short cour...

Help with Excel Inventory Database
Is there a good example of a parts inventory database and reports that is publicly available? I have an idea of what I need to do with the columns for receiving and releasing parts, but I am not certain how to display the current inventory for each part. Any help will be greatly appreciated. Hi, Take a look at my Excel database tutorial at http://edferrero.m6.net/DataTutor1.html Ed Ferrero "ridgerunner" <iluttrell@msn.com> wrote in message news:%23t0uUdoIFHA.576@TK2MSFTNGP15.phx.gbl... > Is there a good example of a parts inventory database and reports that is ...

how to parse <?xml version="1.0" encoding="UTF-8"?> with xpath? is it possible?
how to parse <?xml version="1.0" encoding="UTF-8"?> with xpath? is it possible? "Daniel" <softwareengineer98037@yahoo.com> wrote in message news:%23g11DbDOGHA.2176@TK2MSFTNGP10.phx.gbl... > how to parse <?xml version="1.0" encoding="UTF-8"?> with xpath? is it > possible? No -- the xml declaration is not represented in the XML Infoset. Cheers, Dimitre Novatchev ...

Parsing cells
Hi Folks, I have data in cells which is OCR`d from my phone bill, and some of the info is corrupt. Where I should be getting eg. 17 Nov 14:49 for date/time I am getting O17 Nov 14:49 or C17Nov 14:49. The reason is that there are unreadable icon characters to signify day or night. Questions: 1)How do I remove the faulty characters......they are always the first digit in the date if they are there. 2)How can I seperate the date and time into two adjasent colums of cells rather than their current format in one cell. Any and all help greatly appreciated. John Hi John You ca...

How do i link Excel to Powerpoint slide show & see graph on wk she
I establish a link that shows on the screen but I want to be able to print the one slide from powerpoint on Excel spreadsheet. ...

Excel copy and paste
I have tried to copy a worksheet with several columns and rows of data and paste to another worksheet but I lose the formatting of the rows and columns and then have to manually reformat (I've tried to use paste special but this doesn't help) Also with the same worksheet I filtered the data down to a group of rows and would like to copy this to another worksheet and hide the other data or delete it to send it to someone else. Is this possible? Many thanks Anne "Anne Girling" <anne.girling@vodafone.co.uk> wrote in message news:a4f901c346d0$9bc1b110$a401280a@ph...

mkdir command in excel 2003 on XP SP2
The mkdir does not work after the install of SP2 Why? Maybe you're trying to create a subfolder to a folder that doesn't exist??? "zvi carmi via OfficeKB.com" wrote: > > The mkdir does not work after the install of SP2 Why? -- Dave Peterson It works for me just fine. What problem are you encountering? Any error message? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "zvi carmi via OfficeKB.com" <forum@nospam.OfficeKB.com> wrote in message news:3e03c3efad8341d8a77eb841212c7e20@OfficeKB.co...

Parse Nested Elements to Single DataGrid
I have been losing my mind trying to parse an XML document (with nested child elements, not all of which appear in each parent node) into a DataGrid object. What I want to do is "flatten" the XML document into a text document with a single row for each parent node (that has all of the values from all of the child nodes for that row) The DataView within VS 2005 IDE displays my 15 or so child tables - and knows that some parent rows don't have child rows in every relation - but trying to get all the child tables to go with the correct parent row programmatically is killin...

I lost a document in excel, the whole document disappeared
I have had a document in Excel for over a year. I simply went in the get a serial number, then exited out. Later I went back and the document is gone. The file name is still there but nothing comes up. Please Help Hi, Try opening the file and then: (1) Windows | Unhide If this does not work, try: Windows | Arrange | Ok --- Regards, Norman "Excel Document Gone SSW" <Excel Document Gone SSW@discussions.microsoft.com> wrote in message news:F1B68051-7D8F-4950-A8D3-3CBB097CC665@microsoft.com... >I have had a document in Excel for over a year. I simply went in...

How can I import multiple tasks from excel?
I hope you can help me, because it is annoying to import each task, one by one. Thanks -- Oscar File, Import doesn't work? See http://www.slipstick.com/Tutorials/import/import.htm if you need help importing from excel. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM You can access this newsgroup ...

Parsing CSV
Hi, im using A file with records in my mfc application, i need to read*/write records to the file anybody ahs a clue where should i start? Take a look at CStdiofile and CString::Format() (for output) and CString::Tokenize() (for parsing input). You can use ReadString() and WriteString() to access the file line by line. Tom "fima" <fkpkot@gmail.com> wrote in message news:eXA9vp95FHA.1184@TK2MSFTNGP12.phx.gbl... > Hi, > im using A file with records in my mfc application, i need to read*/write > records to the file anybody ahs a clue where should i start? >...

New Named Range Created Each Time Data Imported into Excel via Macro
I have noticed that each time I import data into an Excel spreadsheet via a macro, a new named range (for the same range) is created. This does not pose a problem, but after a while, I'll have a huge number of named ranges that will never be used. Why does Excel name the range and how can I stop this? I noticed in the recorded macro, there was a line .Name = "drd_5". I commented this out to see what would happen, but it just renamed the range "ExternalData_5". The next one was "ExternalData_6", etc. Thanks, Carroll Rinehart You could refresh with differen...

Excel number / date conversion
I've just pulled some data out of an ODBC connection into Access an then exported it to Excel. The dates are currently in the format 20031109. But the built in dat convertor (from Format Cells) doesn't do anything, even if I convert i to a 'proper' number first. How can I convert this to 09/11/2003 so that I can do some date - base arithmetic and work out difference in days -- markperr ----------------------------------------------------------------------- markperry's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2759 View this thread: http://w...

Excel charts should allow "switching" series on or off
A plot (chart) of time series data may be used for many purposes. From my use two reasons I use such plots are: Studying individual time series trends; and Overall impression of all results. But when studying multiple time series data a chart can become cluttered. It would be useful if individual series could be seleceted as visible or invisible so that they could be evaluated without losing the total plot. At the moment the only way to "unclutter" the plot is to remove the temporaily-unwanted series but it can be time consuming to reconstruct the plot. ---------------- Thi...

Email from Excel #3
Is it possible to have Excel send an email if the value of a certain cell is changed? Hi see your post in public.excel -- Regards Frank Kabel Frankfurt, Germany "berj" <berj@adelphia.net> schrieb im Newsbeitrag news:CcSdndsVDJy4jcjcRVn-pQ@adelphia.com... > Is it possible to have Excel send an email if the value of a certain cell is > changed? > > ...

version excel
bonjour a tous, & a tous les pro bien sur es qu'il est possible ,que des l'instant on modifie le code VBA EXCEL on incremente un compteur pour les mise a jour cela me semble plus simple merci pour vos reponse patrick.villa@laposte.net +++++ http://www.mcgimpsey.com/excel/udfs/sequentialnums.html -- --- HTH Bob (change the xxxx to gmail if mailing direct) "pounet" <patrick.villa@laposte.net> wrote in message news:1165418782.552172.169740@j44g2000cwa.googlegroups.com... > bonjour a tous, & a tous les pro bien sur > > es qu'il est possible ,qu...

Error Message When opening Excel
I am using Windows VISTA 64 bit O/S with Office Professional 2003 All of a sudden I started to get the following error message: 'One of your object libraries (Stdole32.tlb) is missing or damaged. Please run setup to install it' I am at a loss as how to correct this. I checked online and found some instructions that were for Windows 2000 Those did not work for VISTA at all. Thank you in advance for your support and suggestions, HankL It is telling you to do a "setup to install" on the Office product, did you try that? Did you try doing a repair on ...

MFC: Excel Automation Can't Quit from Excel. Please help...
Hi I use MFC Visual C++ 4.2. I am automating Excel from my application. My problem is I cannot quit the Excel after the automation process finished! I checked it still exists on the Process lists and the most DANGEROUS is everytime the automation function run, it create other Excel and so on. So it must be out of memory soon. I have used the app.Quit() method like all the people used but it DOES NOT work. What should I do? Thank you. I happened to answer this on the office.developer.vba newsgroup. You most likely have neglected to Release() the automation object. joe On Wed, 3 Sep...

ASP .NET 2.5 - VB - Excel 2003
Hello, This is my setup: Application's front-end tools: Visual Studio 2005, ASP .Net 2.5, VB, Excel 2003 Web server running Win Server 2003 I have a pre-formatted spreadsheet (that's why this cannot be done with a ..csv file) in Excel 2003. It needs to be populated with data retrieved from SQL server with a stored procedure. After populated the resulting spreadsheet file needs to be accessible to the web application's user in their local PC, so (s)he can open it and add other data by directly editing the spreadsheet outside the application. How it works now...