Streamlining conversion from one accounting system to another using .csv

[Anne Plummer]

Hi,

I export the financial data out of the old accounting system in csv format, which has one set of fields and headers.

Then comes the boring and time consuming task of cutting and pasting into the new csv template to upload to the new system.

The most revolting part is matching the old Line Item Account names with the new Account Codes, so I have to refer to a separate Chart of Account list.

There's a bit of basic help here:

https://help.xero.com/au/#Accounts_InvoiceImport$BK_TipCOA

I'll probably have to be doing this sort of thing a lot, which is daunting.

There's a small glitch with the tax amounts, which I'm asking Xero help about (the Saasu export amounts are tax inclusive but the Xero amounts import tax exclusive - I then have to go into each invoice once imported and select from a drop-down list.)

Any suggestions gratefully received.

Kind regards,
AP

 

[NARAYANK991]

Hi Anne ,

Sorry , but I am not able to understand either your problem or the kind of help that you are looking for.

Both the files you have uploaded are text files ; does Excel come into this somewhere ?

Either way , what kind of suggestions are you looking for ?

1. Is the old accounting system you are talking about the Xero system ? If not , what is it ?

2. What is the new accounting system ?

3. You export data in csv format from the old accounting system ; which of the uploaded files has this data ?

4. You are manually converting data from the old format to the new format ; is Excel involved in this ?

5. Is the Chart of Accounts available as a text or Excel file ?

6. What about the tax amounts issue ; are you expecting any suggestions for this ?

Narayan

 

[Anne Plummer]

Hi Narayan,

Sorry to be so vague - I will try and remedy.

Here are the .csv files:

https://www.dropbox.com/s/tfq85hvh1zknul1/Saasu purchases-summary-and-details-Chandoo.csv
https://www.dropbox.com/s/5zs2213zkbk8uj7/XeroBillTemplateChandoo.csv

The old accounting system is called Saasu
The new accounting system is called Xero.

The first file, ie. Saasu purchases summary and details... has an example of how the bill is exported out of the old Saasu system.

The XeroBillTemplate... shows how the data has to look so that it will upload successfully into Xero.

I want to automate getting the data from one to the other format, without using cutting and pasting.

The chart of accounts can be exported both from the old and the new systems.

I hope this clarifies - let me know if not.

Kind regards,
AP

 

[NARAYANK991]

Hi Anne ,

Things are clearer now. If you can upload a table which gives the correspondence between the headers in the old system , and the headers in the new system , then it may be easy to create a new file which contains data that has been converted from the old system into a format which is acceptable to the new system.

Are you OK with VBA , or do you want only formulae ?

Secondly , have you opened the .csv files in Excel and seen that the correspondence between the headers and the values is correct ? It is easier to deal with Excel workbooks than .csv files.

Narayan

 

[Anne Plummer]

Hi Narayan,

I've left an example of where the info goes with one sample bill, to show where the information has to go in each template. It's a vodafone bill.

I think VBA might be better, because formulae might mean I still have to get rid of the formulae before saving as csv. You might have to show me how to set up the VBA code, but that's OK.

Yes, I've checked that correspondence between headers and values is correct - I did a test of importing into Xero and it worked.

Hope that helps,
regards,
AP

 

[NARAYANK991]

Hi Anne ,

From what I can see , the following is the correspondence between the old system and the new system :

Sale Date ------------------------------------ InvoiceDate
Due Date ------------------------------------ DueDate
Invoice # ------------------------------------ InvoiceNumber
Billing Company Name ---------------------- ContactName
Line Item Total Exc Tax --------------------- UnitAmount

Can you confirm this ?

Secondly , the following 2 fields in the new system do not have any corresponding fields in the old system :

AccountCode
TaxType

What is to be done ?

Thirdly , whatever data is present in the old system , which is not in the 5 fields listed first , will not be imported into the new system ; is this OK ?

Narayan

 

[Anne Plummer]

Hi Anne ,

From what I can see , the following is the correspondence between the old system and the new system :

Sale Date ------------------------------------ InvoiceDate
Due Date ------------------------------------ DueDate
Invoice # ------------------------------------ InvoiceNumber
Billing Company Name ---------------------- ContactName
Line Item Total Exc Tax --------------------- UnitAmount

Can you confirm this ? Just have a look at the Vodafone example in each, which I've tested and whatever is there matches between old and new.

Secondly , the following 2 fields in the new system do not have any corresponding fields in the old system :

AccountCode
TaxType
That is correct - Saasu (old system) doesn't use account codes. The new system has both account names which can be matched to the account names in the old system. I think I am introducing more complexity here, and perhaps solve this one as a separate problem

What is to be done ?

Thirdly , whatever data is present in the old system , which is not in the 5 fields listed first , will not be imported into the new system ; is this OK ? Yes, that is fine.

Narayan

 

[SirJB7]

Hi, Anne Plummer!
Maybe you want to give a look at this:
http://chandoo.org/forum/threads/arranging-data-as-per-dates-of-each-month.15302/page-2#post-94191
It seems to be an analogue problem.
If so, just advise if any issue.
Regards!