Working with Fixed Length Outfiles using MySQL and PHP

Creating ASCII Fixed Length Outfiles using MySQL

1. Step 1. Create a FileSpecification Mysql table.
2. Step 2. Export your MySQL data using the FileSpecification Table as a map

Details:

Step 1. Create a FileSpecification Mysql table.

Create a File Specification Table:

CREATE TABLE `filespecification` (
`id` int(11) NOT NULL auto_increment,
`speccode` varchar(50) NOT NULL default '',
`fieldname` varchar(100) NOT NULL default '',
`startpos` varchar(10) NOT NULL default '',
`endpos` varchar(10) NOT NULL default '',
`length` varchar(10) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=85 ;

Manually populate it using PHPMYAdmin or your favorite gui MySQL client.

Step 2. Export your MySQL data using the FileSpecification Table as a map

** Details forthcoming ***

Load a FLat File Into an Array

** Details forthcoming ***
check out unpack() function in php

Other Notes

Though CHAR() (as opposed to VARCHAR()) data is stored fixed-length, it is retrieved from the database without the padding. So data type definitions themselves do not seem to help.

And metadata definitions of tables are not easily retrievable until MySQL 5.x, which have information schema’s.

From the forum below, the winning answer seems to be the RPAD() function. Use it when retrieving data from database in the SQL query thus:

SELECT rpad( test, 10, ‘ ‘ )
FROM `test`
WHERE 1

Full solution forthcoming…
mysql_query(”SELECT RPAD( FName, 11 ,’ ‘ ) , RPAD( LName, 18 ,’ ‘ ) “) ; //etc

some discussion on topic

source: http://www.codingforums.com/archive/index.php?t-32333.html

ecroskey

01-28-2004, 09:12 PM

can anyone help me with creating fixed-length files using php? I have been looking everywhere to find some example and can’t. I have to create order files that are readable by our VAX and need to put all the order info into flf’s.Any ideas or examples for creating?Regards,
Eric

---

SDP2006

01-28-2004, 09:40 PM

Do you mean like a random file name/value and have the name/value a certain lenght?

---

ecroskey

01-29-2004, 02:28 PM

It’s going to be a fixed number of bits (characters) per field pulled from the database.Say the records are as follows:FName
LName
EMail
Address1and so on…My file has to be setup like:

FName(up to 11 bits-characters)LName(up to 18 bits-characters) and so on…

The file will be the first eleven characters-bits will contain the first name and however many blank spaces up to eleven, followed by the last name field containing the characters-bits and blank spaces up to another 18 spaces and so on throughout the order.

I can easily pull the info I need because I am already doing it, what is stumping me it limiting the fields to a certain length and the making sure the appropriate number of blank spaces are added (appended) to the end of each field to fill up the remaining bit length before the next field should start.

I hope I am making myself clear, but I am not sure I am.

Regards,
Eric

---

raf

01-29-2004, 03:30 PM

You mean to create a textfile with fixed width columns (where each startingposition of a value is fixed).What DB are you using? Most db’s have an exportfunction for fixed width or delimited files.If you want to do this in PHP then you will need to check the values length and fill the rest of the column up till one position before the start of the next value.
so it would be something like$endpost=array(”20″,”40″,”50″,”70″); //an array with each last position of the values
while ($row=mysql_fetch_rows($result)){
for($i=0; $i<=mysql_num_fields($result;$i++){
$file .= $row[{i}] . str_repeat(” “, $endpos[{i}]-strlen($row[{$i}])
}
$file .= ‘xxxx’ // xxxx needs to be replaced with your end of line seperator
}You see, it’s rather simple. You just need to fill in the array (one position for each field !!) and end of line symbol, and then everything should run automatically.

Can’t your VAX (whatever that is) take in delimited files?

---

firepages

01-29-2004, 11:59 PM

Your database can ( &should where possible ) do most of the work for you , e.g. in MySQL you could …mysql_query(”SELECT RPAD( FName, 11 ,’ ‘ ) , RPAD( LName, 18 ,’ ‘ ) “) ; //etcwhich would give you fixed length, space padded results , note if the returned data is bigger than the chars allotted it will be shortened to suit.If you need to do further munging of the data before you save it then you can use PHP as raf shows above.

Note: Data type used was CHAR. Other data types include: * CHAR(M) : Fixed length string. Always stores M characters whether it is holding 2 or 20 characters. Where M can range 1 to 255 characters. * VARCHAR(M) : Variable length. Stores only the string. If M is defined to be 200 but the string is 20 characters long, only 20 characters are stored. Slower than CHAR. * INT : Ranging from -2147483648 to 2147483647 or unsigned 0 to 4294967295 * FLOAT(M,N) : FLOAT(4,2) - Four digits total of which 2 are after the decimal. i.e. 12.34 Values are rounded to fit format if they are too large. * DATE, TEXT, BLOB, SET, ENUM

This entry was posted on Monday, August 21st, 2006 at 7:50 am and is filed under MySQL, Other Tech, WebTechnology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.