Topic: Importing via CSV - Exposed !

Assalam-o-Alaikum ! Hello Everybody

When I first tried "FA", I was stunned because of its enhancement, flexibility and customization. But the only annoying thing

was importing of data via csv file. Though i'm not a programmer, but i've manually done it.

Now Everyone can import : -

    1. Items and inventory.
   
    2. Sale, Purchase and Standard Prices.
   
    3. Stock Category, Quantity and Placement.
   
    4. GL Transactions.
   
    5. Customers invoices, balance, transactions, orders etc
   
    6. Suppliers invoices, balance, transactions, orders etc

I've imported more than 4,000 items with their sale, purchase and standard prices, quantity, placement, exact GL

transactions. I've also uploaded more than 100 suppliers and 150 customers with their respective balance (credit and debit),

transactions, orders and GL entries. Trust Me its working fine for me, without any problem.

The Procedure is mentioned as under : -

a). Install the latest installation package of FA.

b). Make a Fresh Installation of FA and setup your new company i.e. company name, phone no and address etc.

Sample data should not be installed, if installed then please empty all the data using MySQLAmin or any other tool).

You can also import Fresh Installation en_US-new.SQL file from sql folder of FA using Setup tab.

(Note I've installed the FA with No Tax Group and No Dimensions).

How to Import ?

Requirement :
You should have some knowledge any of MySQL tool so that you can import .xls or .csv file in the respective tables.

Important Point :
when you enter any of item, location, sale or purchase price, customer or supplier data, sale or purchase order etc, there are some entries that are inserted in various tables. For example if you insert only a single item with its sale and purchase price, its respective entry will affect the following tables.

0_gl_transactions
(each single item is added twice (debit and credit) for balance sheet, trial balance)

0_loc_stock
(if you have one location, the entry will be single, but if you have two locations/stores the item has two entris, one will be blank for adjustment purpose and the other contains the item's current location)

0_prices
(contains sales prices for item)

0_stock_master
(contains purchase price, locations, category and gl transactions references)
0_stock_moves (contains standard price, quantity and location)

so it is very important to do each step carefully.

For your ease i've sent a file to Mr. Joe, the the Administrator, containing all the required data which will easily describe itself the way of importing.

when Mr. Joe uploads it to the web you can use it.

What's Inside this File ?

It is a Microsoft Excel File.
It contains all the tables of MySQL files needed for FA.
Every single sheet contains the respective name of table being used in FA.
Sample data is inserted for further ease of use.
You have to first backup your company data, or make a fresh FA installation in another folder for testing purpose.
Now first make separate spreadsheets of all the sheets.
Each sheet should be named respectively as of table name for easy usage.
Now make necessary changes to sample data of all the sheets and save all the spreadsheets as comma separated .CSV file using MS Excel.
After changing and saving your new csv files please import them using PHPMyAdmin or any other MySQL Tool.
During importing first select you desired table.
Empty it if it contains previous data.
Now import the respective .CSV file.
When all files are imported successfully, go to FrontAccounting and check the followings.
Items and Inventory Tab
Sales Tab
Purchase Tab

Now you'll see that all the imported items are being displayed according to their category.
Each item has its sales, purchase and standard price.
Every item has its location and quantity.
Suppliers and Customers are added respectively.

Enjoy.

If you are feeling any kind of difficulty, then please do write me in this forum. I'll try to answer you.
Moreover, please first carefully read the MS Excel file.
Because this is the main file and contains all the relations among other tables' data.

See the sample data in all the sheets and find the relations with other tables so that you may insert other data in FA using this method.

Note :  more than 4,000 items with their sale, purchase and standard prices, quantity, placement, exact GL transactions. I've also uploaded more than 100 suppliers and 150 customers with their respective balance (credit and debit), transactions, orders and GL entries. Trust Me its working fine for me, without any problem.

Wish you Best of Luck.

Allah Hafiz, Good Bye and Take Care

qaiserikram@hotmail.com

Re: Importing via CSV - Exposed !

This tool is now available for download at the Download section - Miscellaneous page. I have converted the file from .xlsx to .xsl. This way it is more compatible with older versions of Excel as well as it can be used by Open Office Calc.
The instructions given by Qaiser above is also placed in a CSVImporting.txt file that is included in the package.

/Joe

3 (edited by tom 03/28/2009 02:12:54 am)

Re: Importing via CSV - Exposed !

Is this the file you are talking about?

https://frontaccounting.com/wbt/modules … hp?file=92

It has the CVSImporting.txt file but the xls file only has sheets for manufacturer and workcenters.

No Items

Is there an easy way to import items?

I only have maybe 100 items so I can do it manually...

tom
(edit) I found another thread dealing with this, and links to the docs

Re: Importing via CSV - Exposed !

The link to this file no longer works?

Re: Importing via CSV - Exposed !

I tried the link today, and it worked.

/Joe

Re: Importing via CSV - Exposed !

Thanks found issue with browser not allowing pop ups from your site. fixed by adding allow rule.

Re: Importing via CSV - Exposed !

I am new to FA, and have no knowledge on how to use SQL, CSV, But I have been playing with the program and reading for the past 2 months,  and manage to import CSV files through Heidi SQL. I have import my chart of accounts, using chart master, chart type, and chart class. I have set up the company files, vendors, exchange rates, bank account info, fiscal year, group, tax type.
Also imported stock master, stock category,  and prices. Manually input a journal entry for my beginning balance.  So far it looks good.

But went I see inventory evaluation report all my items are in zero. PLEASE tell me what chart I miss so that my inventory quantity is updated.

Thanks to all for your reply, and comments that have help me set up FA.