Putting a Delimited Text File into a MySQL Table

1) Take a look at your existing field structure. Open your delimited text file in a text editor and take a careful look at your data.  Make a list of all fields that are in your data file and make a note of the type of information that each field contains.  Check your delimiter.  If your delimiter is not tab, pipe, comma , (without quotes), or comma "," (with quotes), then you'll need to convert your existing delimiter to one of these formats.  Also take a careful look at your file and make sure that no line hard wraps to a second line (each record should span only a single line and each field in each line should be separated by a delimiter.

2) 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

3) 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. 

4) 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.

5) 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.

6) Import your text file.  In Step 3, 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 existing data 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.

7) 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 6 again.

8) 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.