RJE Module for converting delimited text files to MySQL tables
Copyright © 2005 Richard J. Edwards - See source code for GNU License Notice
See SLiMSuite Blog for further documentation. See
This module is designed to take a bunch of delimited text files and create appropriate build statements for making
MySQL tables, checking the content if desired. The main MySQL class has the global parameters for the reading and
handling of input files, which are converted into Table objects. Each Table object itself has a number of Class
The script processes files and generates builds statements in the following way:
1. The next file to be processed is displayed along with the default table name, its first line (split into
fields), the delimiter used, and the number of fields. The list of headers (set by
2. There is then a user menu with the following options:
< S >kip file = Skip this file and move on to the next one.
< T >able Name/Descriptions = option to change the table name (default = file name without extension) and the
description for the table that is printed in the build file's comment lines
< H >eader Names/Descriptions = option to manually change the names and descriptions for field headers
< A >utoname Headers = if the file does not meet the given
3. The file is then read through to assign Types to each field:
(a) Field contents are scanned to see if they contain pure numeric values and, if so, pure integer values.
Otherwise, string contents are assumed.
(b) The presence of Null (empty) values are recorded. Also, unless
4. Field types are assigned according to the field content characteristics:
(a) Numeric fields are set as "Unsigned" if the minimum value is >= 0. This is used with the minimum and
maximum values to assign the integer type if an integer or FLOAT if non-integer.
(b) String fields are assigned a type based on the minimum and maximum lengths of the contents and whether
these two values are different. This way a CHAR, VARCHAR or BLOB is assigned of the correct length.
5. Unique fields without null entries are identified as potential Primary Keys. If
6. Each field can be selected to be indexed. By default, any non-numeric fields which have a maximum length in
the range set by
7. Based on all the information given, the build statement is generated. This consists of:
- Comment lines first identify the name, description, file and number of lines.
- DROP TABLE IF EXISTS command to clear existing data
- CREATE TABLE using the Types, Primary Key and Indexed fields determined above
- LOAD DATA statement, including the list of fields in the file if an auto-incremented key was added
- DELETE FROM statement to remove header lines, allowing for data truncation upon loading
Upon hitting <ENTER>, this will be written to the given output file (
© 2015 RJ Edwards. Contact: firstname.lastname@example.org.