Fog Creek Software
Discussion Board




Knowledge Base
Documentation
Terry's Tips
Darren's Tips

Future Enhancement Request: Import from Excel

Would be very helpful to me to be able to  have a spreadsheet with columns corresponding to my article records that I can edit en'mass and then import into CD. This would obviate the need for me to open an instance of seperate article windows for every new article I wanted to enter.

Of course, if you do provide an Excel->CD import feature, it would also be helpful to provide a feature export the other way from CD->Excel .

Since you are using MS Access as the underlying DB, and the integration between Access and Excel is pretty tight, I suspect it would be relatively easy to do this using builit-in MS Office Automation features.

What do the CD folks think? Any fellow users agree that Excel import/export of article fields would be useful?

-Ben

Ben Meiry
Wednesday, January 23, 2002

So, do you mean that you would have a sheet where every line is the information for an article? If so, I think this should be fairly easy to do right now, using Excel's db links.

Maury Markowitz
Wednesday, January 23, 2002

Maury: yes, that is what I meant. Your point about the DB link to Excel is an execellent one that I wish I had thought of. I will give it a try and post my results. thanks.

Ben Meiry
Wednesday, January 23, 2002

Maury:

I tried linking the DB to Excel in order to allow for spreadsheet entry of article records. This is what I found:

1. First off, I am nervous about in messing with the Access DB in any way that were not intended by the CD Folks. I can always make backkups, but then it gets complicated. Besides, I don't know and shouldn't need to know the DB structures in detail. Case in point, is the article table...

2. The article table does not have a simple "memo" field for the article text as one might suspect. Instead it is of type OLE Binary Data. It was not obvious to me what was going on here and how to mesh that with a spreadsheet without some very advanced Excel skills which are beyond me at the moment.

3. Furthermore, there are a bunch of keys in the article records which are generated from other tables, and I would have to write queries, I think, to start reading and writing to multiple tables.

All this is by way of example to say that an import export feature should be easy to do, if you happen to be the CD folks and know what is really going on behind the scenes.

Maybe it is much less complicated and safer to use Excel DB links than I understand. I am very interested in learning more about what is possible.

Ben Meiry
Wednesday, January 23, 2002

Memo fields are restricted to 64k of data.  OLE blobs are not, so we use OLE binary blobs to store your data (this also allows us to store binary blobs like images in the database).

We'll consider adding some sort of CTY file map to explain how things are laid out in a later version (at least after SP1).

Michael H. Pryor
Wednesday, January 23, 2002

Michael: Thanks for the info on the OLE blob objects. I certainly hope I don't have to write single articles anywhere near 64K-worth of text! ;-) Although I guess your point is that the "article" can contain any type of data that one drags or pastes into it, so it might get that large.

Does this mean I could just link from this database field to any OLE object like a word doc?

If so, in concept, could a db/msoffice-savy person feed article records directly into the article table using a datasheet or spreadhsheet view?

I hope I am not jumping the gun on this, it's just that it would make my life easier to have this now, am I am wondering if it's worth me trying....

Ben Meiry
Wednesday, January 23, 2002

You could, conceivably, do something like that, but it would certainly take some macros and glue code to massage the formats.

Joel Spolsky
Thursday, January 24, 2002

How about Import from CSV.

On a related topic - Import from Website doesn't work right with frames. It SHOWS you the site with frames in the preview window, but only imports the frameset page.

Mark W
Wednesday, January 30, 2002

Errr....not to say that you shouldn't be using OLE Object fields, but an Access memo can hold 1 GB of data. The 64K limit is only for data entered directly through the user interface. You can put lots more than that into a memo programmatically.

Mike Gunderloy
Saturday, February 09, 2002

*  Recent Topics

*  Fog Creek Home