MySQL export to Fixed-Width (.prn?) file

[Zellofellow]

Does anybody know if MySQL can natively export to a fixed-width format? I have tried "mysqldump" and also "SELECT * INTO OUTFILE", but both of these methods add undesirable "tab" spaces into the export.

I have specified column lengths for the table during creation, so that should not be an issue.

I know this is possible, as I have done it via a 3rd party utility called MYSQLyog, but I need to be able to do this from a PHP script, or at the very least from the MySQL prompt.

Help?

- ZelloFellow

 

[sleipnir214]

You're not going to be able to do this from the mysql prompt. MySQL doesn't support fixed-width exports.

You can do it from PHP, but you'll have to implement the fixed width output yourself.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[Zellofellow]

Thanks, Sleipner...Could you point me in the direction of the PHP functionality to accomplish this? I have been looking into this for a couple of days now with no success, and finally gave in and came to the forum to ask for help.

%-)

 

[sleipnir214]

There's not much to it.

Connect to MySQL, select the appropriate database, then pass the SELECT query to the database server. (Take a look at the sample code here:

http://us4.php.net/manual/en/function.mysql-fetch-assoc.php)

I would then open the output file and loop through the returned data from the query, writing the records in fixed form to the file (See sample code:

http://us4.php.net/manual/en/function.fwrite.php,

http://us4.php.net/manual/en/function.sprintf.php)

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[hvass]

Less elegant than sleipnir solution but for a one off try

select into outfile where you specify fields terminated by ''

but you will have to tediously prepare all of your variables with something like RPAD(str,len,padstr) to get them to always output as a fixed length