Excel Spreadsheet Software
Rarely is your database self-contained. You may have to create a spreadsheet for the accounting department so it can merge data in the database with its existing spreadsheets, or you may need to import a text file generated from a reporting or tracking tool into one of your database tables. Therefore, you need to be fluent in the use of Oracle Database XE's import and export capabilities. In this chapter, we show you a couple of ways to export data from your database tables to an external destination using the SQL*Plus SPOOL command, and of course the similar options available in the GUI. On the flip side, we show you how to use the Oracle Database XE GUI tools to import data from a text file or a spreadsheet.
As mentioned in Section 7.1, rows in a database table don't have any inherent order. In a spreadsheet, you can refer particular records such as the first row or the last row or rows 15 to 22. A database table is different. If you want to be able to specifically identify individual records, you need to give them each a unique identifier.
Frequently, the descriptions of products for a shop are already available in electronic format. phpShop can import the CSV (Comma Separated Value) format. This format normally stores values in a comma-delimited manner and can be exported from most spreadsheet applications like Excel, among others. Following is a short example with a product from the phpShop sample data
Information structures like database tables and spreadsheets share a similar conceptual organization. Each is broken down into rows and columns, each of which is further broken down into cells. Therefore, you can convert between formats as long as you institute a set of rules for determining how the columns, rows, and cells are recognized. An application utility will read in this data and, based on these rules, make the conversions necessary for adapting the data to its own formatting standards. Typically, a character or a character sequence is used as a delimiter, separating each cell within a row, and each row from the following row. For example, the sales table might be delimited in a format that separates each field by a comma and each row by a newline character
You use application wizards to create a new application or even convert an old application to a more robust platform. For example, you can easily import a single-user Microsoft Excel spreadsheet into APEX to not only make it a Web application available to any user with a Web browser, but also to import the data into Oracle Database XE. This makes your data more available and reliable instead of residing on a user's local hard disk, the data resides in Oracle Database XE where it is backed up and available to a wider user audience.
This chapter will show you how to better take advantage of the other services and applications that your server may have. Starting off is a demonstration of how to compress files using PHP. The second example takes off into a whole new world creating graphical programs using PHP-GTK. The third and fourth topics are related to each other how to automatically run your PHP scripts, first using cron on Unix (and Mac OS X), then using Scheduled Tasks on Windows. In the final example, COM is used on Windows to create an Excel spreadsheet.
Your life would be a lot easier if everyone used an Oracle database. Exchanging data would be considerably easier using Oracle Database XE's native export and import commands (using these commands is beyond the scope of this book). The reality is that you will need to import data into your database from a variety of sources, such as text files, spreadsheets, and other database and application formats, such as XML. In the following example, a legacy application collects anonymous comments about other employees on a Web page and saves them in a spreadsheet. The spreadsheet contains only the employee number, the date of the comment, and the comment itself. To help management more accurately interpret the comments, you must import this spreadsheet data into the database and join the new table to the existing EMPLOYEES table to pull the employee name and e-mail address. The spreadsheet that we will import is EmployeeComments. csv and you can see it in Figure 39-9. Figure 39-9. Employee...
To work with arrays, you need to learn two new terms elements and indexes. Elements are the values that are stored in the array. Each element in the array is referenced by an index that differentiates the element from any other unique element in the array. The index value can be a number or a string, but it must be unique. You can think of an array like a spreadsheet or a database that has only two columns. The first column uniquely identifies the row in the spreadsheet, while the second column contains a stored value.
Before you can put any data into or retrieve any data from a database table, you must create the table. This is usually a onetime operation. You tell the database program to create a new table once. Your PHP program that uses the table may read from or write to that table every time it runs. But it doesn't have to re-create the table each time. If a database table is like a spreadsheet, then creating a table is like making a new spreadsheet file. After you create the file, you open it many times to read or change it.
The SELECT INTO OUTFILE SQL statement is actually a variant of the SELECT query. It's used when you want to direct query output to a text file. This file can then be opened by a spreadsheet application, or imported into another database like Microsoft Access, Oracle, or any other software that supports delimitation. Its general syntax format follows
In the next and final chapter, you'll learn how to use MySQL's default utilities to both import and export large amounts of data. Additionally, you'll see how to use a PHP script to format forms-based information for viewing via a spreadsheet application, such as Microsoft Excel.
While the INSERT statement is fine when executing one or a few row insertions, how might you go about inserting a large amount of data that has come from a spreadsheet or a tab-delimited text file While you could certainly write a script to read in each row of the file and pass the values to an INSERT statement, that isn't necessary this is such a common process that a mechanism has been made readily available to you by way of two functions pg_copy_to() and pg_copy_from(). Both functions are introduced in this section.
A relational database table is a data structure used to store and organize information. You can picture a table as a grid consisting of both rows and columns, much like a spreadsheet. For example, you might design a table intended to store employee contact information, and that table might consist of five columns employee ID, first
It's unlikely that you have a homogeneous environment where all of your data is stored in an Oracle database (and no other brand of database) and no one uses spreadsheets or text files. As a result, you need the capability to import and export data in a variety of formats. On the Data Load Unload page, you can load data into the database from text files, spreadsheets, or XML documents. To transfer the data in your database to another site that, for example, can only accept text or XML files for import, you can export one or more of your database tables into a number of text formats and XML. We cover importing and exporting Oracle data in more depth in Chapter 39.
Where Can I Get Vertex42 The Excel Nexus
Vertex42 The Excel Nexus will be instantly available for you to download right after your purchase. No shipping fees, no delays, no waiting to get started.