CONVERT EXCEL TO DATABASE


This article explains why you can save time, money and streamline your operations using a web-based database instead of an Excel or OpenOffice spreadsheet.

Excel Small Scale or Large Scale?

Excel does a great job and has been around for a long time. We use Excel all the time. Excel is ideally suited for individuals that want to perform numerical or some form of structured information management.

There comes a point when using Excel can be more of a hindrance than a help. Let’s consider an example.

Imagine a company wants to process customers’ orders and needs to generate invoices. The customers send in their required items for the order. An Excel user in the company has created a spreadsheet that stores every product in different worksheets. Imagine there are 5,000 products and they are stored in 20 worksheets.

Each product is classified into a category with a text heading. Then every product is denoted by its part number code.

For every new order the Excel file needs to be saved as a new file so it saves the individual customer’s order. The quantities are entered for all the products ordered; then using a formula an “order” worksheet is edited to show the products in the order and then the price for each product times quantity is displayed.

Another sheet “Invoice” then takes the order data and shows the information in a traditional looking invoice with sub totals, tax and shipping added.

Finally the invoice can be printed and the order is finally placed.

The Excel user then has one Excel file stored for every order placed and these need to be saved for future reference.

We know of Excel uses who work like this and are saving files that are over 10Mbytes for every order. The files are very large and not easy to use and time consuming and processing orders is a bottleneck as a result.

When Does Excel Become A Hindrance?

As we can see from the above example, Excel becomes a problem when it’s part of a critical business process in a business and when the amount of data grows to be large.

Excel and very large data-sets are not ideal partners. Just saving and opening files takes time and managing the data can be a problem if there are multiple copies of the Excel file.

If Excel is used as part of a critical business process then it would be reasonable to expect that some of the information in the spreadsheet will be needed by others in the business.

Excel does not allow sharing of its information. There are software products around that can solve this or you could use Google Docs but they are far from ideal. Using Google Docs for the order processing example we cited would be impractical.

A general rule is, if you are using Excel for a critical business task and there is a large set of data or data needs to be shared then it’s very likely that your business will benefit from using a database instead.

What is a Web Based Database Compared to Excel?

Instead of using a programme, using a web based database means accessing all your information using a web browser. The web browser connects to a web server and your data is stored in a database on your web server.

How all your data gets into the database is what we at Logic Replace do for you but let’s assume that part is already done, what will be the differences with using Excel?

Main differences are that any users in your business can login securely and access the information. So you could have say 10 people or more processing orders instead of just one Excel person.

Another difference is how the information is displayed and entered and here Excel appears to have an advantage, perhaps! In Excel all the data is right in front of you and you can edit any cell. It’s easy, edit a cell, edit another cell, paste to 20 cells etc.

In web based user interfaces normally you will see data related to say one product only on the screen. So if you need to change a values in 20 products you will need to select the first and click edit then change the value, then save. The select the second product and so on. This can be very tedious

Logic Replace has generated a solution for this by creating a “Cell View” for data in a web based solution. Within a web page, a user can search for a range or data, say a range of products. Then all the data related to that range are shown in cells on the web page. The user can edit each one and even paste into multiple cells, just like in Excel. Then save the whole range in one go mimicing how Excel works.

Back-end systems like Magento and others do not allow this type of mass editing for products.

Advantages of Using a Web Based Database Compared to Excel

The information needs to be only stored in one place, stored one time making data maintenance efficient.

Can form relationships between data-sets or tables making the process of managing and filtering the data more efficient.

As the data is stored in a secure web based database, it is possible to use the same data in other systems, example a businesses e commerce website could share the exact same product data.

Many users can access the data and permissions can be established so users only see the data they need to see.

Management can access the data and reports can be run on the data.

Real time notifications can be set up that trigger alerts based on some criteria.

The information can be used in a separate business process and can trigger that process, example after an order is placed a work order for production can be generated for example.

Conclusion

Any business using Excel for a critical business process could benefit from moving to a custom web based database solution. The change will save time and provide numerous efficiencies and data intelligence for management in terms of reporting.

If you are fighting with a big Excel file as part of your business, contact us for a chat to see if we can help streamline your operations with a custom web based database.

Schedule a Free Consultation to Discuss Your Project