Overview of Importing Files in an ASCII Format
ASCII format files are plain text files where fields are separated by a delimiting character. There are many different formats which exist, the most common being CSV (comma separated value) and TAB. With *.CSV files, fields that are text-based are normally enclosed in quotation marks, whereas numeric fields are without quotation marks. *.CSV files are easy to read and import when the importing program knows exactly how the comma separator is being used in the file.
However, generic programs must try and decipher the *.CSV format when importing and errors often occur. For example, when commas exist within descriptions and they are not meant as delimiting comma values.
The Pastel Data Import includes a unique interactive facility that converts any ASCII format file into a TAB delimited ASCII format. This TAB delimited ASCII format is then used exclusively by the Pastel Data Import for all further processing. Once the Pastel Data Import has formatted the ASCII file into a TAB format, the import of that file becomes possible.
The file that you are converting from needs to be in a specific layout to ensure successful integration. You must first prepare the conversion data by exporting it out of the other package in an ASCII format that is readable to the Pastel.
Information could be reproduced in a table that looks like the following:
1st column – Account Number (1000000; 2000000; etc)
Character 7 characters maximum
2nd column – Description (Sales; Cost of Sales; etc)
Character 40 characters maximum
3rd column – Financial Category (I 90; I 95; etc)
Character I=Income Statement / B=Balance Sheet
4th column – External Reference
Character 12 characters maximum
Hi, my GL codes are not importing correctly. For example the code I import is (8000/00) after I import the GL, the code reflects as (8000//00). When I import without the /, then it reflects as (0800/00). Also when I try to go into the account is says “This record does not exist or has been deleted”
Please help . . .
Hi there Prinisha.
Firstly I have to apologise to you for this late reply. I don’t know if you are still working on your imports?
When I look at your example, I notice that your GL codes are incorrect.
Pastel works on 4 numbers for the main account number, and 3 numbers on the sub account number.
This means that your example of wanting to import account 8000/00 should be 8000/000. You are short a digit. so its adding the extra digit as a /.
Pastel wont recognise your format of 8000//00 and will give an error.
Hope this helps.
Bev
Bev,
I’m trying to export Inventory Items from one company to another company, so that I do not have to re-create all the stock codes. But, when I export as a CSV file, then input, it gives me error messages “Group Code Does Not Exist” and “Blank Spaces in Code”. I’ve deleted all the blank spaces, but the error remains and I do not know how to address the “Group Code” error. If I export as a TXT file, I get an error “The Inventory Category does not exist” for every line.
Please help.
Hi Jonathan,
Yes, you still need a column that has a ’00 or a ’01 (this MUST be text in excel) in it. Even if you are not using inventory categories in your new data, there is still the default one that Pastel uses.
Just check that the other company that you exported this file from does not in fact have a inventory category list in it.
Also, check your version of Excel. The older versions of excel saved the csv with a comma, but I’ve seen that the newer versions like 2007 and 2010 like to change the comma , to a ; instead, and this throws the whole import out.
I open my csv file in notepad before I import, and do a find and replace to remove all the ; from the file. And save it this way.
Hope this helps.
Bev
Im trying to import payroll journals from a 3rd party payroll software which has the pastel GL codes used already set up, I need a layout/format for the .csv file to do this please assist.
Thanks
Donald
Hi Donald, I have hunted down the Employee Master File layout for you.
View this link Sage Pastel Employee Masterfile Layout
At the bottom of the page is the attachment.
Hope this helps.
Bev
Hi, I WONDER IF SOMEBODY CAN HELP? WHEN I IMPORT FROM A CSV FILE PASTEL WANTS EXCL PRICES AND THEN ALL MY IMPORTED RETAIL INCL PRICE GOT +- 1 CENTS IN THEM. HOW TO ROUND TO NEAREST 50 CENTS. THANK YOU
Derick you need to round the cents in Excel before you bring them into Pastel,
and then make sure that the column that holds this amount only rounds to two digits.
Do a search on the formulas in the Excel help menu.
Good day, I have a Pastel Payroll client who when she imports her employee’s hrs from a time and attendance program the data from a previous import is not replaced by the new data, the top line on the payslip contains the previous data and under it the new data will reside.
She exports into .txt format from the T&A program, the data is 100% correct and no duplications of any data or codes are present, the correct data imports into Pastel but as mentioned the older data is not replaced.
Pastel consultants are not able to assist on this matter, and we are in a position where everybody is pointing fingers, can the default import file format be changed to .txt instead of .csv? We have tried .CSV export but that creates issues so we a banded that option..
Any help would be greatly appreciated.
Thank you
Hi Quinton.
I need to ask some questions, hope you don’t mind.
I will assume that the journal batch is always updated in Pastel before a new import is done. (So its a empty journal that the import comes into.)
If you do an export as a simple test dump, and save it as a csv file, and open it in Excel or notepad do you see all the new lines including all the older lines from the T&A program?
Or do you see only the new info?
Is she giving her export a new name each time?
Are you importing this into the payroll batch screen?
Can you come back to me on these questions.
Good day, thank you for your reply, my apologies for the late reply, here are the answers to your questions:
1.I will assume that the journal batch is always updated in Pastel before a new import is done. (So its a empty journal that the import comes into.)
Yes it is.
2. If you do an export as a simple test dump, and save it as a csv file, and open it in Excel or notepad do you see all the new lines including all the older lines from the T&A program?
Or do you see only the new info?
When saved as a CSV file i see only the new information, we have checked to ensure that the exported data is indeed correct and tha there are no duplications.
3. Is she giving her export a new name each time?
Not every time, but this has been explored and made no difference.
4. Are you importing this into the payroll batch screen?
Yes we are and that is when we see the previous data on the top line of the payslip with the current newly imported data underneath it in the next two lines
Hi
I am sending this to a Payroll Consultant I know, who is a really good payroll fundi.