Hi guys, I have been searching for a solution to this issue through the web, without success.

I need to read a very simple database (just one table) with MySQL but the fields of this database are not delimited by any delimiters. Of course I have a codebook telling me the columns containing each field, but I don't know how to say that to MySQL.

Does anyone have a clue?

Try something like:

mysql -u <user name> -D <database name> -e "select * from Table" > output.filename

You may have to put in a password, too, depending on your installation, if you're running this locally or over a network, etc. Read the MAN page on the mysql command for more info.

The command above will create an ouput file, containing the data. It will be something like:

--------------------------
Col1 | Col2 | Col3 | Col4
--------------------------
data | data | data | data
..
..
..


Simple matter of running that through SED or something else, to change the "|" to a comma if you need to.

Sorry, I probably have not clear enough

You assume I already created the table into mysql server. But I did not, because I don't know how to do it.
I may create the table, but as far as I understand when I want to load the data into it I have to specify the field delimeter, but there's not any as fields are not delimited.

So my problem is a bit more complicated than that

Is there any way for you to re-export the source data? There really is no way to do this without specifying some sort of delimiter for your fields:

http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

It can also be done if you have fixed width fields or there is something IN the data that sort of shows where one field stops and another one starts.

Could you post a couple lines of example data (don't use real data), and we might be able to help.

Forrest

Well, you do have to create the table first, and that would entail knowing what kind of data you have to manage, so you can determine what data types to use, and how big to make them.

To load it, that's a different matter, and will depend on what you're using as a data source. If you've got, say, a spreadsheet, you should be able to export it as a comma-separated values file. How you import it is up to you...you can

- Write a program in bash/ksh/perl/ruby/etc., to take the CSV file and insert the records
- Use a tool like PHPmyadmin, which may (don't know), be able to do it.

The fields in the database aren't delimited..they are just separate columns. The program/whatever you use to import the data, has to be able to find them.

You'll have to get your table built first. What are you using to import your data, and what's your original data source?

My data source is a simple ascii file with data. Together with it I have a codebook from where I know to which variable a column (or set of columns) refer.
The codebook is like that:
the first line of data is then something like this:
meaning that for first observation Variable1=111, Variable2=22, Variable3=3333, and Variable4=4444

Read the documentation on the MySQL LOAD DATA INFILE command. It does support a fixed-width format, but it's not too flexible. It selects the field width based on the size of the columns in the database table. So if you have numeric columns that are shorter than the size MySQL uses, or if you want character columns longer that what's in the actual data file, you could have problems.

Personally, I'd just write a script to convert the data file into a comma-separated format (or whatever is most appropriate for your case). You could even do that with a simple call to Perl. For example:
Once you have a character-separated file, you'll have some more flexibility with loading the file.

That seems a really elegant solution, will try that, thanks