Monthly Archives: March 2012

Fixing authors when importing a split WXR file for WordPress

If you have a large WordPress Extended RSS import file, WordPress suggests that you split the file in order to create multiple smaller files.  If you do this, then each file will have the same authors.  If you import the second file, third, fourth, etc… then those authors will be seen as duplicates and dropped (and all imported posts will be credited to ‘admin’) unless you go through each author and select them in the “or assign this post to existing author” combo boxes.   These combo boxes become available after you select a file and press “Import.”

If you are importing multiple files because a single one is too big, there is a chance you could have way too many authors to go through each one and do this.  This post describes a hack method of populating all of these combo boxes using jQuery and the browsers development tools.

I had to import 6 WXR files that i created, each one around 80Megs, and a total of almost 500 authors.

1) Get jQueryify here and add the bookmarklet per the instructions.

2) When you get to the “assign authors” page, right-click on the browser page and select “Inspect Element” (Chrome), or “Inspect with Firebug” in Firefox (install firebug if you haven’t already). In IE press F12 to start the Dev Tools.

3) Press the jQuerify bookmarklet button that you installed from step 1. This will turn on jQuery for the page.

4) Go to the console tab, enable the console if you need to, paste this code in and press RUN:
jQuery.noConflict(); //Disable jQuery $ just incase.

jQuery('#authors li').each(function() {
// Get the author login text
var username = jQuery(this).find('strong').html();
var author_login = jQuery.trim((username.split('('))[0]);

//Figure out which option this author is in the drop down.
var selectOptionval = -1;
jQuery(this).find('select option').each(function(){
if (jQuery(this).html() === author_login) {
selectOptionval = jQuery(this).val();
return false;//quit .each() early.
}
});

// Set the combo box to this author's option key.
jQuery(this).find('select').val(selectOptionval);
// For test...
//console.log(author_login + ": " + selectOptionval);
});

Advertisements

jQuery ready function with holdReady

I used this the other day in GWT, prior to backing it out and placing style classes in better places so i could use straight CSS targeting.  But, I thought it could be useful and it uses the new jQuery.holdReady function so here it is.

My goal was this: In order to get the correct horizontal scrollbar in a GWT app, I had to target the div right ABOVE the root div placed in my Root View (which contains the structural layout of my page). I used jQuery to target that div, but they went back and placed a styleclass in the RootLayoutPanel widget instead so that i could target this more cleanly (within GWT) with CSS’s :nth-of-type / :nth-child selector.  I found the exact DIV that i needed to target by placing overflow-x: auto !important on all the parent divs until it worked ‘correctly.’

The ‘pageScrolling’ style class is defined with only overflow-x: auto !important

/* Startup functions */

/*
* First, place a hold on the document ready function… GWT loads more stuff
* after the DOM loads, and we need to add a little extra delay to account for that.
*
* By using document ready and hold ready together, i am able to make sure that the
* startup function runs after the page is completely ready.
*/
jQuery.holdReady(true);

// Set up the on-DOM-ready function
jQuery(function($) {
// Find the DIV that should handle scrolling for the “body” and mark it
$(‘.myRootPanel’).parent().addClass(‘pageScrolling’);
});

/*
* GWT needs to create its structure, which happens after DOM loads??
* Therefore, hold the document READY function until an expected element
* is detected
*
* (ps.. This holds ALL jQuery ready functions anywhere the system… if there are any)
*/
var waitForPageToLoad = function() {
if (jQuery(‘.myRootPanel’).size() > 0) {
// it exists now.. trigger the ready function
jQuery.holdReady(false);
} else {
// Doesn’t exist yet.. wait some randomly selected time period.
setTimeout( waitForPageToLoad, 200 );
}
};

/*
* Trigger the timeout function to wait for the page to REALLY load,
* and then release the document READY hold.
*/
waitForPageToLoad();


Dynamically remove blank cells from a column in Excel

This is another Xcelsius issue i had — but the solution is usable in any excel spreadsheet.

I wanted to create a fake drop down (custom image w/ little arrow like most web pages have now + push button + list box) that i could set up with dynamic options — dynamic meaning different options based on different states of the dashboard, not adding or removing new options at runtime.  The problem was that i needed to link to a range in the Excel spreadsheet that gave me the currently available options and did not have blank spaces between them.  I hunted on the internet and found an example, but it used functions that are not available in the Xcelsius-enabled list of Excel functions.

So I edited it to be Xcelsius-friendly and here it is (click on this pic to see a larger version):

Table layout for dynamically removing blank cells

Basic table layout for setup

  • Column 1 (H): a row number that is copied across to column 4 when the option text is marked as “available” in column 3.  these are hardcoded numbers.  Xcelsius doesn’t support the ROW function.
  • Column 2 (I): The table of data.  In my case it doesn’t have spaces, but effectively does when i have certain options marked as “unavailable”.
  • Column 3 (J): A formula that is unique for each row and determines if THAT row should be available.  This checks the status of different things in the Xcelsius file (e.g., the dynamic visibility of other components in order to determine what tab on on, etc) and determines when this option should be available in my faked-out combo box implementation.  If you have data that has spaces in it that you are removing, this row can check for that or (even better) just combined with column 4.
  • Column 4 (K): A filter.  If this row is “available”, then this cell is set equal to the row number in column 1.
  • Column 5 (L): blank… i left it there for spacing, or maybe for adding something later… i don’t know.  I can’t remember.  but it is blank.
  • Column 6 (M): The magic happens here… Keep reading

The function in Column 6 is this, the column letters are defined in the column definitions above (i.e., column 1 is H) and the first row of that table was at row 30:

=IF(ISERROR(SMALL($K$30:$K$49,H30)),"",INDEX($I$30:$I$49,SMALL($K$30:$K$49,H30)))

To break this down:

  1. ISERROR(SMALL($K$30:$K$49,H30)),””…: The first part here checks if there is a next smallest value in column 4.  if there is, it continues to the INDEX function, otherwise it leaves the cell in column 6 empty.  Column K is the sparsely populated filter column (aka column 4), and the “n” in the “nth next smallest value” comes from column H (the rownum).
  2. INDEX($I$30:$I$49,SMALL($K$30:$K$49,H30)):  Since we know an “nth next smallest value” exists in the filter column, this pulls the “option text” in column 2 (i.e., the table data) corresponding to that row number.  the row number starts at 1 for the first row, so it is equal to the “index” of that row when using the INDEX function.
Here is a screen shot to help you line up the formula to the columns.  If you setup the first cell in column 6 with the same “$” that i use for static cells, then a copy-down will fill in that whole column correctly:
Options table with formula 1st row

Showing the super awesome formula in the last column

It is actually pretty simple and useful.  I simplified it as i wrote it here because i found a function that I was using that was redundant, but in case it solved some other issue that i don’t have and can’t think of, here is it:

=IF(ISERROR(SMALL($K$30:$K$49,H30)),"",INDEX($I$30:$I$49,MATCH(SMALL($K$30:$K$49,H30),$K$30:$K$49,0)))

.   The MATCH at the end there is redundant.  In my example, the relative index is also equal to the “nth next smallest value” in the filter column — so i’m doing a MATCH using the SMALL function to get the same number the SMALL function already returns.  It is useful to write these posts and fix my own code!

I’m not maintaining this blog here so commenting is closed… Please see this same post on my new site to ask questions:http://mpickell.com/blog/2012/03/05/dynamically-remove-blank-cells-from-a-column-in-excel/