Categories
Cloud Apps

How To Set Up Infinite WP’s Cron Job

If you’ve never set up a cron job before it can be a little daunting to do so when asked.

As I hadn’t done a cron job for quite some time I had to remember all the processes involved in getting a task set up and being able to check the task was working. On my Ubuntu 13.10 nginx box I went through the following steps and hopefully it helps clear the confusion on how to set it up on your server.

After installing the necessary stuff according to InfiniteWP one of the final tasks will be enabling a cron task on your server. I’m assuming that you have access to your server so once you’ve logged into it, navigate to:

$ cd /etc

Once here, look at the directory by running the ls command and see if you spot the following files and directories:

cron.d
cron.daily
cron.hourly
cron.monthly
crontab

System’s PHP check

Now before we get to adding stuff let’s make sure your server CAN run PHP on the command line. For this run the following:

$ php -v

If you receive an error that apt-get cannot find or will need to install php, then you need to install the php5-cli application, hence run:

$ sudo apt-get update
$ sudo apt-get install php5-cli

Once you’ve checked to make sure you can run php commands from the command prompt you can then safely move to the next step.

InfiniteWP cron setup

To get InfiniteWP to work you will need to navigate within the cron.d folder and to create a file that you will be able to identify for InfiniteWP’s cron tasks for your WordPress installs. I’m going to create a file called iwp, but you can call it whatever you want.

$ nano iwp

Within this file append the necessary information to create a cron task:

# for InfiniteWP's cron
*/20 * * * * root php -q -d safe_mode=Off /directory/to/wordpress/iwp/cron.php >/dev/null 2>&1

Be sure to change the /directory/to/wordpress/iwp/cron.php to whatever the full path location is to your InfiniteWP’s install is.

But what does all this stuff mean?

  • The first segment */20 means that the following task will run at every 20 minute interval.
  • The next segment * means that it will run every hour.
  • The next * * * (three asterisks) mean every day of the month, every month, every day of the week. Obviously if you wanted to change the frequency of how often you want the cron task to run you would modify these variables. I’d highly encourage you to read more about cron jobs by going to the manual here.
  • After all the asterisks we then set who will be the user running this task, in this case I’m setting it as root, followed by the command I’d like to be able to run.
  • In the case of InfiniteWP we run a php command in quiet mode with the following detail of keeping safe_mode off running the php file found at the location of /directory/to/wordpress/iwp/cron.php putting all stdout output into the garbage bin.

Once you’ve saved your iwp file you’ll want to reload cron (just for safe-keeping, even though cron should be able to grab the new commands) by running:

$ sudo service cron reload

If you wanted to make sure cron indeed is running and that it is working you could similarly run:

$ sudo service cron restart

Which should output something like:

cron stop/waiting
cron start/running, process 15910

The final check all is working correctly on our end is to check the /var/log/syslog file to see that it has worked. To do this, possibly wait for 20 minutes (or however long you set your cron job to run for) and run this:

$ grep CRON /var/log/syslog

This should output something like this:

Jun 24 01:20:01 SERVER_NAME CRON[7772]: (root) CMD (  php -q -d safe_mode=Off /directory/to/wordpress/iwp/cron.php >/dev/null 2>&1)

This output shows you the timestamp of when the process ran and who ran it.

Conclusion

In this post you have learnt how to create a cron task for running InfiniteWP and how to check your cron tasks are working.

Categories
Cloud Apps

Convert WordPress Into Markdown Files With YAML Front Matter (PHP Script)

Now we’re ready to begin our journey on extracting our WordPress content into a format needed for our static site generator, the big question becomes:

What format do we want to be able to extract our WordPress data into?

We have a plethora of choices available, but I like having YAML front-matter at the top of my posts and pages (to control HOW the content will be embedded), which is what many of the popular static site generators prefer too. However, I want the flexibility of being able to write my own custom header information which is what a YAML-like header on my posts and pages will provide.

For the content I’d like it to be Markdown based, even though when we get the data it will be a mix of HTML and Markdown (unless you’ve specifically written in <p> tags for every paragraph break in your content). I’d prefer to continue writing in Markdown and then be able to process my content into HTML for uploading to my static site.

Therefore, the way I have constructed my output is a little like this:

---
title: This is the title of the page
date: 2012-10-02 14:30
author: Ryan
tags:
 - hello
 - world
 - blog
excerpt: This is a wonderful post about title tags on pages
template: post
redirects:
- /90/this-is-the-title-of-the-page/index.html
---

What has helped to drive the design of the YAML header in my output has been the help of a node package YAML Front Matter to JSON which helps to transform my entire page into JSON.

The remainder of the page would then contain the content, like:

---
YAML header (as above)
---
This is the content of the blog post. 

When we extract it from the WordPress database we'll be noticing that there will be 
&lt;a href="/"&gt;anchor tags&lt;/a&gt; and image tags and div tags (etc) in our document, 
but there will be no paragraph tags. If you don't like Markdown, you could change my PHP 
script below so that it changes line breaks in &lt;br /&gt; or &lt;p&gt;&lt;/p&gt; tags.

So now we have an idea of what we’d like to output from all the content we’ve written in our WordPress install, let’s run a PHP script on our server’s terminal (yes this would therefore assume you have that type of access, you could still possibly run it if you only have FTP access, just be sure to edit the elements within the code and then run the file).

Anyway, here’s the script with detailed comments on how you can extract WordPress content and convert it to YAML front-matter with Markdown content:

<?php
// MAIN CONFIG AREA
$website = "http://www.webbyworks.com.au"; // enter your website here, see stripContent function for more details
$username = 'root'; // enter your database's username here
$password = 'password'; // enter your database username's password here
$hostname = 'localhost'; // enter the hostname of your database here
$dbname = 'dbname'; // enter the name of your database here
$output_extension = ".md"; // type of file to output
$out = "/tmp/"; // set where you want the output files to go
$author = "Your Name"; // insert your name as the author for each page and post
// END MAIN CONFIG AREA
/**
* This function removes the slashes, and any absolute references in your posts & pages
* It will also change any references to "/wp-content/uploads/..." to "/img/..."
* So be wary of this when you download and the re-upload to your static site
*
* @param string $content
* @param string $website
* @return string
*/
function stripContent( $content, $website ) {
$arr = array( $website, "/wp-content" );
$str = str_replace( "\r\n", "\n", stripslashes( $content ) );
$str = str_replace( $arr, "", $str );
return str_replace( "/uploads", "/img", $str );
}
/**
* This function translates the array of tags in your posts and pages and
* converts it into a string for YAML Front Matter friendly input.
*
* @param string $tags
* @return string
*/
function outputTags( $tags ) {
$arr = explode( ",", $tags );
$result = "";
foreach( $arr as $i ) {
$result .= " - " . trim( $i ) . "\n";
}
return $result;
}
/**
* This function checks whether you have an excerpt for your post and pages
* and if not creates one from the opening paragraph of your content.
*
* @param string $excerpt
* @param string $content
* @return string
*/
function getExcerpt( $excerpt, $content ) {
if ( strlen( trim( $excerpt ) ) > 0 ) return trim( $excerpt );
$result = strip_tags( substr( $content, 0, strpos( $content, "\n" ) ) );
$result = str_replace( ":", ".", $result ); // YAML plugin doesn't like colons anywhere in header text
return trim( $result );
}
// let's connect to the database
$dbhandle = mysql_connect( $hostname, $username, $password ) or die("Unable to connect to MySQL");
// let's select the database we need
$db = mysql_select_db( $dbname, $dbhandle ) or die("could not select" . $dbname );
// set the output to UTF-8, if you need another format enter that here, otherwise leave.
mysql_query("SET NAMES 'utf8'");
// show that everything's all good.
echo("Connected to db\n");
// grab the data from the database
$result = mysql_query( "SELECT p.ID as id, p.post_date as postdate, p.post_title as title, p.post_excerpt as excerpt, p.post_name as URI, p.post_type as posttype, p.post_content as content, group_concat( t.name separator ', ' ) as tags
from wp_posts p
left outer join wp_term_relationships r on (p.ID = r.object_id)
left outer join wp_terms t on (r.term_taxonomy_id = t.term_id)
group by id;" )
or die(mysql_error());
// loop through the array of rows we now have and output to the file accordingly
while( $row = mysql_fetch_array( $result ) ) {
// check that the row has a URI
if ( strlen($row['URI']) > 0 ) {
// check that it's a post or page
if ( $row['posttype'] == 'post' || $row['posttype'] == 'page' ) {
// remove absolute links and amend links to /wp-contents/upload/... to /img/...
$content = stripContent( $row['content'], $website );
// prepare the file name for output
$file = $out . $row['URI'] . $output_extension;
$handle = fopen( $file, "w" );
$file = "\xEF\xBB\xBF".$file; // this is what makes the magic for outputting UTF-8
// START YAML front matter
$output = "---" . "\n";
$output .= "title: " . $row['title'] . "\n";
$output .= "author: " $yourname . "\n";
$output .= "date: " . $row['postdate'] . "\n";
// check if the post or page has tags
if ( $row['tags'] ) $output .= "tags: \n" . outputTags( $row['tags'] );
$output .= "excerpt: " . getExcerpt( $row['excerpt'] , $row['content'] ) . "\n";
$output .= "template: " . $row['posttype'] . "\n";
// As post and pages can have different output we'll need to create different redirects
// for each. Generally 'posts' follow the permalink structure, whereas pages are just the URI
if ( $row['posttype'] == 'post' ) $output .= "redirects: \n" . " - /" . $row['id'] . "/" . $row['URI'] . "/index.html" . "\n";
if ( $row['posttype'] == 'page' ) $output .= "redirects: \n" . " - /" . $row['URI'] . "/index.html" . "\n";
$output .= "---" . "\n";
// END YAML header
// START CONTENT AREA
$output .= $content;
// END CONTENT AREA
// output the result to file and close
fwrite( $handle, $output );
fclose( $handle );
}
}
}
// Once we're done show it!
echo("finished!" . "\n");
Categories
Cloud Apps

5 Things You Need To Know Before You Migrate From WordPress To A Static Site

In my previous post on moving away from WordPress things have progressed somewhat smoothly. However, when you’re jumping into the deep unknown you don’t know what’s a normal pain point, or what is a worry.

I’d like to share a few issues I encountered that you might want to consider if you too are considering when going static.

Here are five such issues:

1. Comments

If your WordPress install has comments enabled, and comments are a big thing on your website, then depending upon how you’ve managed this you may need to do a little more research on how to successfully extract them from the WordPress database.

Thankfully I’ve had my comments enabled with a free online service called Disqus. The only aspect I needed to be wary of with this transition was breaking comments on posts, but Disqus has your back with this insightful post on mapping comments to a new structure.

So the complexity of migrating comments wasn’t really a complexity at all – but may certainly be for your site. Do your research to see if it ends up being a problem.

2. Permalink Structure & Redirects

WordPress has a unique way of managing URL’s within your site. If you have itchy fingers you may have changed the permalink structure of your WordPress website many times over its lifetime. Thankfully WordPress does an awesome job in being able to route old permalink structures to new ones, and it does this by maintaining a simple ID for every post and page. Therefore, regardless of whatever you change for the permalink address on your website you will be referenced to the correct page.

What makes this difficult though is when you bring this complicated permalink structure over to a static site. My personal websites all maintain the same permalink structure of /%post_id%/%postname%/, but there was a time where I used to have this structure: /%year%/%monthnum%/%day%/%postname%/.

I have found creating the permalink for each post and page not too difficult (at least in my case), but you will need to note down the permalink structure you have used as this will be needed to redirect pages on your website to new ones should you want to.

3. Redirects

The other minor issue here is if you have permalinks within your WordPress site that redirect to another web page. When migrating over to a static service you may still want to retain the redirects and therefore you will need to find a way of creating an HTML page which redirects to another.

4. How To Get Your Uploaded Files

WordPress keeps all your uploads in one nice little folder for easy navigation and ease of use. You’ll need to be able to gain access to this folder if you want to be able to move your existing content to a new static site.

If you can’t, I would suggest you read up on how to use the wget command from within a Unix box to help you extract your website into a static site.

wget Command

I’ve found the following command to work with my sites, but you might like to do some more reading here.

wget -m --random-wait -p --html-extension -k -E robots=off -nc -P mysite http://www.mysite.com

Or with the more detailed flags is:

wget --mirror --random-wait --page-requisites --html-extension --convert-links --execute robots=off --no-clobber --directory-prefix=mysite http://www.my-site.com/

With each flag interpreted means:

  • --mirror Turn on options suitable for mirroring. This option turns on recursion and time-stamping, sets infinite recursion depth and keeps FTP directory listings.
  • --random-wait Helps the server so that it doesn’t get clobbered with so many requests. Generally waits between 500 and 1500 milliseconds.
  • --page-requisites This option causes Wget to download all the files that are necessary to properly display a given HTML page. This includes such things as inline images, sounds, and referenced stylesheets.
  • --html-extension Convert the downloaded page to have a .HTML extension.
  • --convert-links Convert links to relative referencing for local viewing.
  • --execute robots=off The command after this parameter will execute. Here we have robots=off which you would only ever do when running on your own WordPress installs because then you will know what has been downloaded.
  • --no-clobber If a file has already been downloaded, don’t download it again.
  • --directory-prefix=mysite Save to the directory within the folder where this command is run and name the folder mysite.
  • http://www.my-site.com/ The URI of your website.

Give your terminal a few minutes to spider through your site and download all your content and when you do you should be able to open up the folder within your mysite folder and navigate to your wp-contents folder and see which files your WordPress media library uses.

You may even prefer to use this than your actual FTP download of your wp-contents folder or perhaps maintain both just to check that the correct files uses the proper references.

After running the wget command you should now be in a position where you already have a static version of your site (thanks to the power of wget), and this may be all you really need, but as I’m looking beyond just converting a WordPress site ONCE to a static site and looking to maintain the site with future updates and posts (etc) I need to keep going.

5. HTML/CSS Design

This is perhaps the biggest complexity for my sites and one which will take the majority of development time. Unfortunately one of the biggest problems I have with some of my WordPress installs is that they have a dozen or so plugins installed all of which love inserting their own meta tags and bloating up my HTML output pages. Now I’m in a position of being able to move to an HTML template of my own design it means that I’ll be leaving a lot of the current structure behind.

This means that there will need to be new development in my new sites with the way they are structured, and the way the CSS then merges with this new structure. Therefore, my current WordPress CSS files become somewhat useless, and the money spent on buying themes and enhancing their structure now becomes obsolete.

Keep this in mind as ponder on whether this cost will be too much for you to throw away. Some WordPress installs have spent a lot of money on plugins and themes and find that by throwing it all away would be a waste of money. If this is your case then maybe look at some aspects that attract you to going static and try to determine if there are ways of tweaking your WordPress installs to fit what you are looking for in your sites.

As I’m fairly confident with my CSS and HTML skills I don’t mind spending a few days coding my sites into a new CSS structure.

Hidden Costs

If you’ve been using WordPress for quite some time, as I did for over a decade, then there would have been purchases made on themes and plugins. If you do make the move away from WordPress it means these costs have become sunk.

You may want to consider how long it will take to recoup these costs by comparing the cost of going static with the current costs you have incurred with your current hosting setup.

Weigh up whether the cost is worth it and be also mindful there’s a time cost incurred when bringing everything across. If you don’t have the time it may not be worth it, but if you have a spare weekend then it might be enough to devote that time to the transition.

Finally, are you confident enough to support the transition? If you’re not confident with DNS in connecting to your sites, and working with the command line to generate your static sites then the time spent on development would be more substantial.

Conclusion

Anyway, there are probably a few more complexities that need to be considered that I haven’t yet thought about, however, these have been the main ones. Therefore, before moving over to static consider the following:

  • How am I going to retain/manage comments?
  • Will I need to employ a complex redirect/permalink structure? Can the static service or software maintain this?
  • Can you get your uploaded files? Can you get them easily enough?
  • Will it be easy to port over your HTML & CSS? Do you rely on themes and plugins and do they insert HTML?
  • Consider the cost of investment you have made with WordPress themes and plugins? Consider also the time cost needed in the transition?
  • Are you confident with handling DNS errors, and the command line in generating your static site?

Hopefully these questions will help you to think more deeply about your transition away. Obviously WordPress has been a popular choice for many people with their websites because the costs and pain points raised from these questions is more burdensome than their alternative.

Categories
Cloud Apps

How To Choose A Good Static Site Host

One of the reasons I outlined in my previous post on why I’ve decided to move from my personal WordPress installs to a static HTML site was the cheapness in hosting static sites on services such as Amazon S3 and Google Cloud Storage.

Before Google Cloud Storage came about Amazon S3 was the standard. People tried hosting their static sites using Dropbox’s Public pages but this ended up being more cumbersome. Initially when I ventured into the static space a couple of years ago I tried Amazon S3 with one of my sites.

The experience was okay, but it didn’t eventuate. I found at the time the service Amazon offered was great, but the managing of the content a little too difficult.

Today though I’ve decided to use Google Cloud Storage as my main hosting provider. I’ve never used them before and as they were slightly cheaper than Amazon I thought to give them a try.

So far so good. I’ve used their command line tool gsutil to help upload, sync, and delete buckets, and it’s all gone without too much of a hitch.

(I’ve found that for gsutil to work on my Mac I have to source .bash_profile – I’ve followed Google’s instructions on getting it all setup but have had to apply that tweak to get the utility working)

Anyway, the reasons for why I’ve decided to use Google Cloud Storage for storing my old-WordPress-new-static websites is:

  • Cost. Although not by much, Google has beaten Amazon in this space by four-tenths of a cent (at least as of today).
  • Command line utility. The command line utility and documentation provided by Google was very clear and understandable. The bash_profile issue above everything else has gone seamlessly.
  • It’s Google. Sorry Amazon, but Google have provided a LOT of traffic to my sites throughout the years therefore I’m quite comfortable and trusting in this area with them. Don’t get me wrong I’ve used Amazon’s web services too, and they’ve been great, but Google has given me free traffic.

But why not store them on your current web server?

This was a question I had contemplated. I even thought about version control with installing git on the server and enabling the server to sync with changes on the local machine. The only advantage I could see was the granular controls and tweaks one could do on their nginx server serving the pages (even routing pages to their new destination now pages have a “.html” extension), the disadvantages were with the higher costs (although paying $5/month with Digital Ocean for an Ubuntu instance certainly wasn’t breaking the bank) and maintaining the server.

I’ll certainly keep this idea as a backup, should Google Cloud and S3 prove too difficult, but let’s see how we go without needing to maintain a server.

Speaking of git, why not GitHub Pages or Atlassian’s Bitbucket?

The main reasons for not choosing either were that I had more than one personal site that I was moving across and both providers only allow one free site per account – and maintaining more than one personal site from several GitHub accounts wasn’t too appealing. I could have chosen to move to a paid plan but this ended up being the most expensive choice out of the lot. I do like git and version control though – so it certainly was very tempting! At this point in time though not today.

Again it’s important to look thoroughly into the static service providers you’ll be using for your sites if you’ve decided to migrate from WordPress to a static setup. My needs for my personal sites aren’t that great and because I’m not relying on maintaining a living through these changes then my requirements are not as extensive as perhaps yours might be. Look at all the alternatives and don’t be afraid to test them out.

Categories
Cloud Apps

7 Reasons Why You Would Want To Move WordPress To A Static Site

The time has come for a change.

I have loved WordPress. It was the first CMS I ever used, and I never regretted building the many sites I have throughout my blogging and coding life.

Why move away from WordPress?

There are several reasons why I have decided to move away from WordPress at this point. I have been using WordPress ever since I can remember building my first websites back in 2004.

However, there have been 7 reasons for the decision to move on, and I’d like to explain a little about each:

1. Visual editor

I hardly use the visual editor when writing. For most this is the reason why they find writing easier by using the WYSIWYG editor in WordPress, but there are others like me who are comfortable writing HTML code and feel even more at home when using Markdown.

The Visual Editor has never really worked for me – the formatting has always seemed a little different to how I wanted things to look so now I just prefer on writing raw HTML code to set my pages and posts right.

2. Security

Unfortunately I don’t login to my WordPress installs as often as I need to, and this has caused issues when there has been a plugin desperately needing an update.

Sadly there was a WordPress site I was working on which the end-user/client did not update on a regular basis and by not regularly attending to their site someone maliciously took control, and nuked their server.

They didn’t blame WordPress or me or even the plugins – thankfully they saw the problem was their lack of properly maintaining the site, however, it did highlight to me (as I now myself see) that people don’t have the time to maintain their site.

While it’s great we can use services such as InfiniteWP or ManageWP to help manage updates it still requires logging into these services and clicking “update all” and even updating their plugins within a WordPress install (at least as far as I know – being a customer of ManageWP).

However, these services do cost money and if you’re just blogging because it’s a personal hobby you may not be too thrilled in paying all these additional costs, but hey what hobby doesn’t cost any money?

3. Speed

Google loves fast websites.

A recent project brought across my path was a site that was old, but it ranked #1 on some popular search terms for their business.

Due to their high rankings they have maintained steady and consistent growth in their business, with even their competitors begging and willing to buy their top search rank position!

When we analysed why their site ranked higher than their competitors there were really only two variables that came through:

  • Age of the site, in terms of longevity without any major changes, and
  • Speed

Their site was an old HTML site which had been running on the same IP address (and perhaps the same server for many years) it still served pages really fast – even though the Apache service serving the pages was nothing special at all!

Yet when we compared other aspects such as back links and keywords they were the worst according to their competitors.

So this has highlighted the need for speed, and it’s therefore critical to try and make your site as fast as possible. Unfortunately for WordPress though speed isn’t a thing. You have to install specific caching plugins, but even then you’ll still be slower compared to the same site if they serve static HTML.

4. Cost

You could certainly get around many of these issues by paying a WordPress hosting provider plenty of cash to ensure you don’t succumb to these issues.

However, if most bloggers starting out they likely don’t have the cash needed to pay for all the bells and whistles needed to maintain a secure, fast WordPress site.

With Amazon S3 and now Google Cloud Storage offering VERY cheap storage and bandwidth charges it almost feels like stealing having your pages served from these services when they only charge a few cents per month per gigabyte!

And how heavy is a minified HTML file?

Maybe a couple of kilobytes at most.

It will be the other things you store on your static server, such as images, which will bear the largest portion of your static costs, but it’s still going to take a lot of images to be able to get to a gigabyte.

5. iOS app

I have only ever used the iOS WordPress app once in my entire blogging career.

It is a great tool and is one big benefit for bloggers on the run or out and about and no doubt this will improve over time, but unfortunately my experience with it has been horrible.

I can foresee this getting better and being popular in the future, but as I blog mostly from my computer it’s a nice feature, but not necessary.

I’m also not the fastest in typing on my iPhone too.

6. Managing content

One thing I will miss when moving away is that when I want to internally link posts in my content to others it is easier in the WordPress visual editor to do so.

Also, it’s easier to find previous content and should you need edit it and apply the changes.

Unfortunately I rarely edit my content once I’ve published a post. I might skim back through a blog post once it is published to help fix some typos or get back on track to the topic at hand, but I very rarely ever go back to a post or a page and edit it.

Once it’s up its up, and I move on.

While I may miss the ease of managing content it’s not a deal breaker.

7. SEO

I don’t do search-engine optimisation. I don’t care about back links or ranking higher on Google.

I just enjoy writing, even though I’m not very good at it. I always have, and I also enjoy sharing information and knowledge.

I might get 2c for someone little money every now and again but my primary income is from my job, not from my WordPress sites. Therefore, it’s not critical or will make a massive impact to my earnings if I do change.

If you certainly have the time to learn SEO and want to push your content then by all means do so, I just don’t think the solution to achieving SEO greatness will be found from someone’s plugin – free or paid. I think it has a lot to do with the content you serve and how helpful this is to the person reading it.

Conclusion

I should also add I’m excited for a change.

I’ve been with WordPress for a long time, and it’s been a great relationship. Perhaps by moving over to static sites I’ll realise just how good I had it with WordPress and come running back.

Then again, I’m at a point where I’m comfortable learning new things and have arrived at a point in my coding life where I’m confident in building something, and I thought what better opportunity than by being able to translate some of my WordPress sites into static HTML sites.

I could be crazy, but considering I don’t blog as often as I used to anymore in the spare time I now have I’ve decided to take the plunge and see what it would be like.

To reiterate these are my big reasons. If you are contemplating moving away from WordPress and going static then maybe spend a little while thinking about your reasons. You may end up deciding that it’s perhaps easier and better to stick with what you’ve got or if you’re a little adventurous and want to see what other possibilities are out there, then see how I go migrating from WordPress to a static service.

Before I go I should reiterate that I’m certainly not advocating everyone should move away from WordPress because it’s insecure – it’s not, and it is getting better now with the way in which it automatically updates itself. This is a purely personal decision for my personal sites as I try to expand my knowledge and coding experience in other areas.

Let’s see how it all goes!

Categories
Cloud Apps

Import Chart of Accounts to NetSuite – ParentRef

One of the challenges when importing the chart of accounts into NetSuite is trying to understand how a parent reference is to be configured.

Unfortunately an example template I’ve come across doesn’t differentiate what is what as per the following:

accountNumberaccountTypeRefnameparentRef
6000ExpenseExpense
6010ExpenseTravel & Entertainment6000 Expenses
6020ExpenseMeals6010 Expense : Travel & Entertainment

What is most confusing with this example is the final row where you don’t really know which reference Expense is referring too? Is it the accountTypeRef? Is it the name of the uppermost parent?

What is to happen when you need to go down another sub-level in your accounting structure – what exactly is the pattern, is it:

Uppermost level's account name : next sub-level's account name : next sub-level's account name...

Unfortunately after testing a few different varieties which weren’t working for our nested account structure I eventually found the successful import, in a pattern it would look like this:

AAAA HLName : SLName : ... : LSLName

Where:

  • AAAA is the nearest sub-level’s account number
  • HLName is the name of the account starting at the highest level
  • SLName is the name of the account at the next sub-level
  • ... is the name of additional sub-level account names
  • LSLName is the name of the account that represents the nearest sub-level’s name (if this account has a number it is that which is written in at AAAA above).

As an example, if I have the following structure:

+ 6000 - Expenses
    + 6100 - Operational Expenses
        + 6110 - Local Expenses
            + 6111 - Printing

Where 6000 is a main header account, 6100 the next sub-level account header, 6110 an additional sub-level account header and 6111 being a posting account. Our import structure for just this data using the above table as our basic template would look as follows:

accountNumberaccountTypeRefnameparentRef
6000ExpenseExpenses
6100ExpenseOperational Expenses6000 Expenses
6110ExpenseLocal Expenses6100 Expenses : Operational Expenses
6111ExpensePrinting6110 Expenses : Operational Expenses : Local Expenses

This portion of the import will work successfully and when done you should receive 100% success status.