[request] Excel Article Import

mrdale · 2008-07-07 01:34:11

…would certainly save a lot of people a lot of time. Anyone feel like giving it a shot?

CSVs or some such, I guess.

Last edited by mrdale (2008-07-07 14:36:25)

jm · 2008-07-07 02:09:03

You know you have a problem when you write articles in Excel :P. How would your data be organized? Something like this in a CSV?

Title, Body, Excerpt, Category1, Category2, Section

mrdale · 2008-07-07 03:37:22

har, har.

Just wanna be able to take a shit-load (hundreds) of articles scraped from an existing site and plop ‘em into TXP without doing cut/paste/save dance.

Your format would be fine, except it’d be nice to support the full compliment of custom fields.

jakob · 2008-07-07 06:16:05

Oh yes, I second this. I have a project possibly coming up with a couple of hundred short articles with project data provided by the client in excel and was thinking the same.

Alternatively, can the existing blog import plugins be bent to use or can existing excel data be reformated to be importable with one of the existing importers (custom fields would be required)?

(BTW: Dale, as far as I remember, phpmyadmin will accept excel as an import format but I’ve never tried it)

jm · 2008-07-07 07:01:58

Yep, you can import via phpMyAdmin (and from shell) – you just need to have your CSV columns match the textpattern table columns (describe textpattern;).

The somewhat barebones-version would be 21 columns. Annotate, Image (article image), Keywords, etc. could be removed too. Dates, IDs, and other info would be set during the import.

AuthorID, Title, Body_html, Excerpt_html, Image, Category1, Category2, Annotate, Status, Section, Keywords, custom_1, custom_2, custom_3, custom_4, custom_5, custom_6, custom_7, custom_8, custom_9, custom_10

(This is why I hate CSVs.)

Last edited by jm (2008-07-07 08:16:14)

mrdale · 2008-07-07 14:15:52

OK, that went pretty well but I have a couple of problems…

timestamps are all zero’d which produces an error until I save them all…
textile. They’re all set to use textile. X(

2. is easily solved by
UPDATE `textpattern` SET textile_body=replace(textile_body,'1','0')
and
UPDATE `textpattern` SET textile_excerpt=replace(textile_excerpt,'1','0')

not sure how to fix 1.

Last edited by mrdale (2008-07-07 14:24:42)

redbot · 2008-07-07 14:26:43

mrdale wrote:

timestamps are all zero’d which produces an error until I save them all…

If I remember well you can set them all to an arbitrary timestamp via phpmyadmin, so at least you won’t have to resave them all…

UPDATE textpattern
SET Posted="2008-01-01 00:12:00"

I have not tested it but it should work (backup first)

Last edited by redbot (2008-07-07 14:31:10)

net-carver · 2008-07-07 14:37:49

mrdale

Try something along the lines of…

UPDATE `textpattern` SET `Posted`=now() `LastMod`=now() `feed_time`=now();

net-carver · 2008-07-07 14:42:35

Ooh.

Don’t forget…

the uid field in textpattern. That may need to be unique.
the last mod time in txp_prefs. That may need updating as above if you want the imported articles to correctly register in feeds etc.

Without resorting to php, you might be able to do the uid in MySQL like this…

UPDATE `textpattern` SET `uid`=MD5( UUID() );

But I haven’t tried it.

Last edited by net-carver (2008-07-07 14:54:49)

ruud · 2008-07-07 14:51:21

A very simplistic approach to setting the uid would be to set it equal to the ID column (which is unique as well)

net-carver · 2008-07-07 14:57:44

ruud wrote:

A very simplistic approach to setting the uid would be to set it equal to the ID column (which is unique as well)

Yes. That would do it.

Txp itself sets it to an MD5 hash though. Wonder if the feed spec require it in a 32 char format? Anyway, putting the ID through MD5 would do it too.

mrdale · 2008-07-07 15:02:38

Thanks all… help appreciated.

But it does bring me back to the point of this post, which is that… textpattern needs a viable and simple mechanism for generic multiple-article import. It should have this out of the box, but I’d settle for a plugin.

Let’s not talk about the limited blog import options that are in core. They’re kludgy, ultra-specific and rely on textile for the most part. What we need is a way to with a few clicks be able to get a mass of articles into TXP.

In case this sounds like mrdale whining again about a pet peeve/project consider this scenario… web developer falls in love with textpattern, wants to convert a huge investment of existing content. The process right now is maddeningly-manual. click-fingers require intensive physical therapy afterward. A good generic import tool would invite and encourage switchers.

Thoughts?

[Update] did all that and got all my data in… Thanks. Not sure that in this case it actually saved me any time. But i have a book import coming up… we’ll see…

My Steps

UPDATE `textpattern` SET textile_body=replace(textile_body,'1','0')

UPDATE `textpattern` SET textile_excerpt=replace(textile_excerpt,'1','0')

UPDATE `textpattern` SET `Posted`=now(), `LastMod`=now(), `feed_time`=now() where category1='my_category';

UPDATE `textpattern` SET `uid`=MD5( UUID() ) where category1='my_category';

Last edited by mrdale (2008-07-07 15:35:47)

Textpattern CMS

Textpattern CMS support forum

#1 2008-07-07 01:34:11

[request] Excel Article Import

#2 2008-07-07 02:09:03

Re: [request] Excel Article Import

#3 2008-07-07 03:37:22

Re: [request] Excel Article Import

#4 2008-07-07 06:16:05

Re: [request] Excel Article Import

#5 2008-07-07 07:01:58

Re: [request] Excel Article Import

#6 2008-07-07 14:15:52

Re: [request] Excel Article Import

#7 2008-07-07 14:26:43

Re: [request] Excel Article Import

#8 2008-07-07 14:37:49

Re: [request] Excel Article Import

#9 2008-07-07 14:42:35

Re: [request] Excel Article Import

#10 2008-07-07 14:51:21

Re: [request] Excel Article Import

#11 2008-07-07 14:57:44

Re: [request] Excel Article Import

#12 2008-07-07 15:02:38

Re: [request] Excel Article Import

Board footer