Converting a MS Excel Spreadsheet to FLATTEXT for MySQL Table

Excel is a really easy format to work with.

1) Take a look at your spreadsheet. Make sure that each column contains only one class of information and that no record spans more than one row.

2) Save your Excel spreadsheet as Text and Tabs.  Use the File > Save As option to do this.

3) Create your MySQL database. If you are on a virtual host, consult your server documentation about what you need to do to create (or request that your server administrator create) a MySQL database for you.  Once the initial database is created, you will select (or receive) the following:

• The database name
• The user id associated with this database
• The password associated with this user id

4) Download and install the FLATTEXT for MySQL admin utility. Install and invoke the admin utility according to the instructions that came with it.  If you encounter problems with the installation, please contact us. 

5) Create a table.  Using the FLATTEXT for MySQL admin utility connect to database, select the option to Add a Table on the main options screen.  Provide a name for each column, making sure not to use any of the names reserved by MySQL.

6) Set column properties for table.  The above step created all of your columns as type: varchar(255)  This is a variable length field that can hold up to 255 characters.  Use the option to Set or Change Column Types, Sizes, or Names, giving each column a type and size that best fits the data that you intend to store in it.  Please make sure that you read the column type tutorial, since not understanding the basic column types can cause serious heartache down the road.

7) Import your text file.  In Step 4, above, you installed the admin utility and at the top of the script, you specified a full or relative path to your MySQL working directory.  Send your tab delimited text file using a program like WsFTP to your working directory in ASCII mode.  Use the option Import a Delimited Text File into Existing Table to import your file into your table.  Note: before actually importing it, make sure to select the option to Examine Text File... first. This will catch many (but not all!) mismatches between your actual data and your column configuration.

8) Make sure that your table imported correctly and fully.  Use the Query a Table option on the main admin page to search your database for various columns.  Make sure that each column imported correctly and fully.  If you plan to create a script that is addable or editable online, then you need to have a column that contains unique values to key all of your changes to.  You can add a unique column automatically by using the Add a Unique Column option in the admin tool.  If your records imported fully and correctly, you are ready to click the Design a Web Interface in Perl option to create your script.  If it didn't, flush the data out of your table using the Empty a Table option, check your column properties and delimiter, and try Step 7 again.

9) Follow instructions in the script generator and install instructions when your script is generated. Each script has a unique script id. You can use this code to edit your script up to three months after it was first created.  However, if you change the field order or structure of your MySQL table, you will need to create a new script to work with this "new" table.