BACK TO TOP

Using PHP to create a CSV download for import into MYOB

I had the situation a few years back where we were running a custom developed job system (PHP with mySQL) and wanted to import the invoices from this database into the account software we were running which was MYOB.

I thought this would be as simple as exporting the data to a CSV file and importing, however MYOB was a little precious about how it required the file to be formatted and it took a bunch of research to work out the best way to get this done.

This example code works via the Import / Export Assistant in MYOB, which has a bunch of options available, such as importing customers etc.

The below example is based on inserting invoices already created, but you could adapt it to suit your exact needs, such as website orders or any other custom type of database you may have in place.

The PHP code

First off we will setup PHP to output as a CSV file and give it a file name.

<? 
// set php to output as CSV file type
header('Content-type: application/ms-excel');
header("Pragma: no-cache");
header("Expires: 0");
// set your file name, this one has the current date
header("Content-Disposition: attachment; filename=invoices-".date("d-m-Y").".csv");

Next, we are going to define some settings like tax and days due that will be used on all rows. We will also pull in our data source, for the purposes of this demonstration to keep everything in a single, easy to understand file, I have used an array, but you may replace this with your mySQL database connection and SQL query.

// here we set some of the tax and days due settings
$taxcode = 'GST';
$taxamount = 0.1; // 10% for GST
$daysdue = 5;
$myob_account_code = 41100;

// sample data source, replace this with your database connection etc
$sampledata = 
 array( 
 array( 
 "customer_id" => 12345,
 "customer_name" => "Test Company Name", 
 "customer_address" => "123 Henry Street",
 "customer_suburb" => "Sydney",
 "customer_postcode" => "2000",
 "order_description" => "Web Design Services",
 "order_price" => 2000,
 "order_number" => 23232,
 "order_date" => "2017-10-10"
 ),
 array( 
 "customer_id" => 12345,
 "customer_name" => "Test Company Name", 
 "customer_address" => "123 Henry Street",
 "customer_suburb" => "Sydney",
 "customer_postcode" => "2000",
 "order_description" => "Printing Business Cards",
 "order_price" => 375.50,
 "order_number" => 23233,
 "order_date" => "2017-10-16"
 ),
 array( 
 "customer_id" => 98989,
 "customer_name" => "The Other Company", 
 "customer_address" => "44 Ross Street",
 "customer_suburb" => "Melbounre",
 "customer_postcode" => "3000",
 "order_description" => "Graphic Design Services",
 "order_price" => 1000,
 "order_number" => 23234,
 "order_date" => "2017-10-14"
 )
 );

Lastly, we will actually output the header rows and loop through our data source to display in the format MYOB requires.

// write the header row with MYOB order and names, yes the tabs are required
echo "Co./Last Name,Addr 1 - Line 1, - Line 2, - Line 3,Invoice #,Date,Customer PO,Description,Account #,Amount,Journal Memo,Tax Code,Inc-Tax Amount,Terms - Payment is Due, - Discount Days, - Balance Due Days,Card ID\r\n";
 
// loop through the data
foreach ($sampledata as $i => $row) {

 // output each record
 echo ''.$row['customer_name'].','.$row['customer_name'].',"'.$row['customer_address'].'","'.$row['customer_suburb'] . ' ' . $row['customer_postcode'].'",'.$row['order_number'].','.date('d/m/Y', strtotime($row['order_date'])).',,'.$row['order_description'].','.$myob_account_code.','.number_format($row['order_price'], 2, '.', '').',"Sale; '.$row['order_description'].'",'.$taxcode.','.number_format(($row['order_price']*$taxamount), 2, '.', '').',5,1,'.$daysdue.','.$row['customer_id'];

 //create empty line before next record, not sure why MYOB requires this
 echo "\r\n";
 echo "\r\n";
 
}

?>

So that’s it, I hope this might save someone a little time and allow you to build on this example to suit your needs.

Some other things to note:

  • MYOB Account Code variable will be related to how your accounts are setup and may need to
  • In this example MYOB will use the Card ID to match up to your customer, there are a couple of other options available if you look into MYOB options further.
  • Days due did take a little playing around with to get right but can be adjusted based on what sort of terms you have in place.

How to import this file in MYOB

  1. From within MYOB goto the File > Import / Export Assistant
  2. Select the Import Data check box and click the Next button
  3. From the File Import screen, select Sales from the first menu and Service Sales from the second menu. Click browse to select the CSV file. You will need to change to file type selection to All Files in the popup window. Click the Next button once complete.
  4. In the format section, select the following options:
    1. Data is separated by: Commas
    2. The first line contains: Headers or Labels
    3. If the data already exists: Reject the import data
    4. Match Cards using their: Card ID
  5. In the Match Fields screen, click the Auto Match button which will automatically link all fields, double check all fields on the left have a small chain icon present and click the Next button.
  6. There is no need to backup the company file, simply click Import. Depending on the number of invoices to be imported the process may take appox 5 mins for 25 invoices.

 

Categories