Sage Instant Accounts CSV

[♥FWR Media]

This is an initial post as I add the contrib, I will update this on success.

[♥FWR Media]

Ahh well I couldn't update it so will have to post here.

 

Sage Instant Accounts CSV

 

What is it?

 

I got the Sage Instant Accounts Package when I signed up with Barclays Bank for a business account.

 

After a month I still hadn't touched it as I couldn't be bothered to input the endless data.

 

I wanted this inputting automated and that is what this contribution is about.

 

What it does:

 

Sage Instant Accounts allows 5 types of CSV (Comma Seperated Value) file uploads ..

 

1) Customer Records - Included

2) Supplier Records - Not included, Osc does not support this

3) Nominal Ledger Records - Not included, Osc does not support this

4) Audit Trail Transactions - Included

5) Stock Records - Included

 

What it doesn't do:

 

Although it will record all financial transactions and tax, it does not allow for the complete information of the sale. This means that sadly we cannot drill down into the sale to see what was bought.

 

The Benefits:

 

Massive timesaving alleviating the need for manual input of your daily sales/customers/new products, allowing (semi) automated financial accounts.

 

Installation:

 

Upload 2 files.

Upload sql creating 1 table and 1 field.

Alter 1 file admin/includes/boxes/reports.php adding 1 line.

Create 1 diectory in admin sage_csv (chmod 777)

 

That's it!

 

Operation:

 

When you click on sage_csv in admin you are given 2 options ..

 

Full Download (Can only be done once)

Adds all info from the very start to yesterday.

 

Update

Updates all info from the last Update/download to yesterday.

 

Applying to Sage Instant Accounts

 

Go to File/Import this will give you 5 upload options

Upload Stock Records

Upload Customer records

Upload Audit Trail Transactions

 

Each file will go through a test in Sage to test its validity, if you get errors they are likely to be ..

 

Customer Records - If a customer uses a character that Sage does not like e.g. ? it will need to be removed you can do this manually but I'd recommend adding to the arrays in sage_accounts_csv which replaces such characters.

 

##### VERY IMPORTANT #####

The CSV files will update the Sage database and delete previous records, DO NOT use this on a live Sage DB, test first and make a back-up of your live DB.

######################

 

##### NOTE #############

You must have Optimize tep_get_tax_rate() contrib installed

http://www.oscommerce.com/community/contributions,2417/

Edited August 27, 2006 by thunderace

[♥FWR Media]

Contribution located HERE

[ritun]

Contribution located HERE

Hi thunderace,

 

Nice useful contribution. But I'm having errors in 'sage_accounts_csv.php' . I'm getting the following error:

 

Sage Accounts CSV

 

Choose a Full Download or an Update Below: -

 

1146 - Table 'database.sage_csv' doesn't exist

 

SELECT last_update FROM sage_csv

 

[TEP STOP]

 

 

 

* Database name removed. I have installed the MOD carefully and have created the necessary Table 'sage_csv' in the database. I dunno why I'm getting this error. Please advice. Many Thanks in advance.

[♥FWR Media]

Hi thunderace,

 

Nice useful contribution. But I'm having errors in 'sage_accounts_csv.php' . I'm getting the following error:

 

Sage Accounts CSV

 

Choose a Full Download or an Update Below: -

 

1146 - Table 'database.sage_csv' doesn't exist

 

SELECT last_update FROM sage_csv

 

[TEP STOP]

* Database name removed. I have installed the MOD carefully and have created the necessary Table 'sage_csv' in the database. I dunno why I'm getting this error. Please advice. Many Thanks in advance.

 

Hi

 

The only way I can replicate this error is if I delete the table sage_csv from my testsite database.

 

The code to apply the table as given in the instructions is ..

 

DROP TABLE IF EXISTS `sage_csv`;
CREATE TABLE `sage_csv` (
 `last_update` date default '0000-00-00',
 KEY `last_update` (`last_update`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

[ritun]

Hi

 

The only way I can replicate this error is if I delete the table sage_csv from my testsite database.

 

The code to apply the table as given in the instructions is ..

 

DROP TABLE IF EXISTS `sage_csv`;
CREATE TABLE `sage_csv` (
 `last_update` date default '0000-00-00',
 KEY `last_update` (`last_update`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

Actually I'm getting the following error because of the above SQL command:

 

Error

 

SQL query:

 

CREATE TABLE `sage_csv` (

`last_update` date default '0000-00-00',

KEY `last_update` ( `last_update` )

) ENGINE = MYISAM DEFAULT CHARSET = latin1;

 

MySQL said: Documentation

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 1

 

---------------------------------------------------------------------------------------------

 

Hence I modified the SQL command to this:

 

Error

 

SQL query:

 

CREATE TABLE `sage_csv` (

`last_update` date default '0000-00-00',

KEY `last_update` ( `last_update` )

);

 

-------------------------------------------------------

 

This creats the sage_csv table but then I get the error as mentioned in my first post..

[♥FWR Media]

Actually I'm getting the following error because of the above SQL command:

 

Error

 

SQL query:

 

CREATE TABLE `sage_csv` (

`last_update` date default '0000-00-00',

KEY `last_update` ( `last_update` )

) ENGINE = MYISAM DEFAULT CHARSET = latin1;

 

MySQL said: Documentation

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 1

 

---------------------------------------------------------------------------------------------

 

Hence I modified the SQL command to this:

 

Error

 

SQL query:

 

CREATE TABLE `sage_csv` (

`last_update` date default '0000-00-00',

KEY `last_update` ( `last_update` )

);

 

-------------------------------------------------------

 

This creats the sage_csv table but then I get the error as mentioned in my first post..

 

 

I can't replicate this at all, what version of SQL are you running? Do you have phpMyAdmin?

[♥FWR Media]

I can't replicate this at all, what version of SQL are you running? Do you have phpMyAdmin?

 

Try saving the below code as a php file in the \admin directory, call it something like add_sage.php, then run it in your browser.

 

<?php
 require('includes/application_top.php');
$drop_sage = 'DROP TABLE IF EXISTS `sage_csv`';
tep_db_query($drop_sage);
echo 'sage_csv table dropped<br />';
$add_sage = 'CREATE TABLE sage_csv( '.
	 'last_update date, '.
	 'PRIMARY KEY(last_update))';
if (tep_db_query($add_sage)) echo 'sage_csv table applied<br />';
else echo 'sage_csv table failed!<br />';


 require(DIR_WS_INCLUDES . 'application_bottom.php');
?>

 

Ensure to delete the php file after use.

Edited September 2, 2006 by thunderace

[ritun]

Try saving the below code as a php file in the \admin directory, call it something like add_sage.php, then run it in your browser.

 

<?php
 require('includes/application_top.php');
$drop_sage = 'DROP TABLE IF EXISTS `sage_csv`';
tep_db_query($drop_sage);
echo 'sage_csv table dropped<br />';
$add_sage = 'CREATE TABLE sage_csv( '.
	 'last_update date, '.
	 'PRIMARY KEY(last_update))';
if (tep_db_query($add_sage)) echo 'sage_csv table applied<br />';
else echo 'sage_csv table failed!<br />';


 require(DIR_WS_INCLUDES . 'application_bottom.php');
?>

 

Ensure to delete the php file after use.

 

BINGO!! That's Brilliant mate..It worked..Many Thanks.. :thumbsup:

[max_osb]

Hi,

 

Thanks for this contrib.

 

I installed it as a possible answer to my prayers as I need to transfer my data from my shop (lots of it) into Sage line 50 in order to operate a till system and keep my accountant happy.

 

The install went fine. But when I did the Full update action, it says I only had 8 stock records. Does stock mean products as I have around 800 products that I want to transfer into Sage?

 

Secondly, does anyone know if this is compatable with Sage Line 50 v11 rather than any lesser Sage package??

 

Thanks,

[♥FWR Media]

Hi,

 

Thanks for this contrib.

 

I installed it as a possible answer to my prayers as I need to transfer my data from my shop (lots of it) into Sage line 50 in order to operate a till system and keep my accountant happy.

 

The install went fine. But when I did the Full update action, it says I only had 8 stock records. Does stock mean products as I have around 800 products that I want to transfer into Sage?

 

Secondly, does anyone know if this is compatable with Sage Line 50 v11 rather than any lesser Sage package??

 

Thanks,

 

Only Instant accounts as it's all I have.

[dananos]

Only Instant accounts as it's all I have.

 

You could also try the Sage Library at SageDataObjects.com

 

It's not specifically for OsC but it should work.

[Jaya]

A huge thank you for this - works brilliantly and saves me plenty of time.

[Jaya]

Hi Robert, I do have one slight problem - everything works fine except that the products description doesn't appear in the stock records csv file (all other columns are populated correctly). Would you be able to give advice on that?

[mikebd]

I'm installing your great Sage plugin, but when I go to run the SQL it's querying the date 0000-00-00? Should this be todays date or the date of the first web site order?

kind regards

Mike

[Rabaeys]

I was wondering if it is possible to get headers above the customer loop? If it is possible then it is possible to download and import the file direct into sage.

 

I was wondering where i would have to place the headings:

 

 

Do I have to tweak it a little bit around: ???

 

//print_r($customers); //Test print array

tep_db_free_result($new_customers_result); //Housekeeping

//End do the queries

$cust_count = count($customers);

if (!empty($customers)) { //If no new customers have signed up since the last update then skip writing a CSV

for ($i=0; $i < $cust_count; $i++) { //Loop through all customers that have purchased

$handle = fopen(DIR_FS_ADMIN . 'sage_csv/customers_' . $filename . $date_updated . '.CSV', 'a');

fwrite($handle,

$customers[$i]['customers_id'] . ","

$customers[$i]['customers_city'] . "," .

$customers[$i]['customers_state'] . "," .

[♥FWR Media]

I was wondering if it is possible to get headers above the customer loop? If it is possible then it is possible to download and import the file direct into sage.

 

I was wondering where i would have to place the headings:

 

 

Do I have to tweak it a little bit around: ???

 

//print_r($customers); //Test print array

tep_db_free_result($new_customers_result); //Housekeeping

//End do the queries

$cust_count = count($customers);

if (!empty($customers)) { //If no new customers have signed up since the last update then skip writing a CSV

for ($i=0; $i < $cust_count; $i++) { //Loop through all customers that have purchased

$handle = fopen(DIR_FS_ADMIN . 'sage_csv/customers_' . $filename . $date_updated . '.CSV', 'a');

fwrite($handle,

$customers[$i]['customers_id'] . ","

$customers[$i]['customers_city'] . "," .

$customers[$i]['customers_state'] . "," .

 

This contribution is really old and was really only ever half done .. there is so little you can do with the base Sage that it is hardly worth bothering.

 

I no longer use Sage so have no means with which to test this or develop it further.