Creating a Space Delimited Text File


php web development

Many website are built using php and MySQL

A few days ago I had to export data to create a space delimited text file. A space delimited text file is a flat data file similar to a .csv. The big difference is a space delimited file does not separate data with a comma, tab or other delimiter. A space delimited file simply relies on SPACING as the delimiter. For example, lets say we have a csv file with the following fruit related data columns FruitName, FruitColor, FruitTaste.  Your csv file might look something like this.

“apple”,”red”,”good”
“banana”,”yellow”,”yummy”
“water melon”,”green”,”delicious”

Well with a space delimited file you would have a defined length for each data column.  Lets say FruitName = 12 , FruitColor = 10, FruitTaste = 10.  This would give each row in your data file a total length of 32 spaces and would look something like this.

apple       red       good
banana      yellow    yummy
water melon green     delicious

If you copy the three lines of text above and past it into a .txt file it will line up into nice columns.

While there were quite a few resources showing how to create a tab delimited file or a comma delimited file there was virtually nothing out there discussing a space delimited file.  So after a few attempts and few really ugly looking text files here is what I came up with.

[php]
<!–?php
/* You should already be connected to you data base at this point */
$file = fopen("FileName.txt", "w");
$sqlExport = "SELECT * FROM `TableName`";
$results = mysql_query($sqlExport,$DBcon) or die("Error in query: $sqlExport " . mysql_error());
while($row = mysql_fetch_array($results)) {
$lenField1 = strlen($row[0]);
$fieled1 = $row[0];
While($lenParcel < 15) { /* 15 is the total length for this field */
$field1 = substr_replace($field1,’ ‘,$lenField1);
$lenField1++;
}
$lenField2 = strlen($row[1]);
$field2 = $row[1];
While($lenField2 < 24) { /* 24 is the total length for this field */
$field2 = substr_replace($field2,’ ‘,$lenField2);
$lenField2++;
}
$lenField3 = strlen($row[2]); /* All the data in this col was only 3 spaces */
$field3 = $row[2];

$row_to_export = "$field1$field2$field3\r\n"; /* the \r\n echos a CR and New Line */
fwrite($file, $row_to_export);
}
mysql_close ($DBcon); /* Close Database Connection */
fclose($file);
?–>
[/php]

That is pretty much it! The script above will export data to a space delimited text file name ‘FileName.txt’ The file will be created in the same folder where the script is executed. Feel free to comment and offer suggestions.  If you are not familiar with PHP I would highly suggest you have a look at the PHP Reference site.