Export Mysql Recordset to TAB delimited file?

[A JM]

I was hoping someone could direct me to a simple script for exporting a recordset in a TAB delimited format - filed names would be helpful but not required...

 

Any help would be appreciated.

 

Thanks.

 

A JM,

[JustLikeIcarus]

Heres one way

 

SELECT * FROM passwd INTO OUTFILE '/tmp/file.txt'
FIELDS TERMINATED BY '	' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

[A JM]

Thanks for the post but I'm not sure how to implement your solution?

 

A JM,

[premiso]

It would help, A JM, if you posted some code for us to help you implement it, namely where you want this to occur.

[A JM]

Well I'm not certain on how/where I want to implement the script yet.. 

 

I have had some limited success with the following but I don't think its exporting the data correctly. I tried importing into Excel and the data possibly the field delimits were a little jacked up.

 

 

// Connect to the database

mysql_select_db($database_dbconn, $dbconn);

 

header("Content-type: application/octet-stream");

header("Content-Disposition: attachment; filename=\"testing.txt");

header("Pragma: no-cache");

header("Expires: 0");

 

$query = "SELECT * FROM myTable";

 

$rstQB = mysql_query($query, $dbconn) or die(mysql_error());

 

$tsv  = array();

while($row = mysql_fetch_array($rstQB, MYSQL_NUM))

{

  $tsv[]  = implode("\t", $row);

}

$tsv = implode("\r\n", $tsv);

 

print $tsv;

exit;

[A JM]

Hello premiso?

 

[A JM]

I've tried this also with limited success but it appears to get confused when a field containing text has a carriage return???

 

...

$result = mysql_query($query, $dbconn) or die(mysql_error());

 

$count = mysql_num_rows($result);

$fields= mysql_num_fields($result);

$data = "";

for ($i=0; $i < $fields; $i++) {

$field = mysql_fetch_field($result, $i);

$data .= $field->name;

$data .= "\t";

}

$data .= "\n";

while ($row=mysql_fetch_row($result)) {

for($x=0; $x < $fields; $x++) {

$field->name=$row[$x];

$data .= $field->name = $row[$x];

$data .= "\t";

}

$data .= "\n";

}

print $data;

exit;

[cags]

What is the objective of your tab delimited file, will it be used with another application? Obviously newline characters will cause a problem since a new line character is your delimiter between individual entries. Generally with a CSV file there is some support for this, by encasing the field in quotes etc. But the exact solution will depend on what you intend to do with the file.

 

The code provided by JustLikeIcarus looks like the most simple/likely solution. It's simply an SQL query statement. Whilst I've come across the syntax I've never worked with it. But since it is a simple query string you should just have to call mysql_query("Blah"); changing the filename as appropriate.

[A JM]

I will be using the export for another application so formatting is important.

 

The only thing about using JustLikeIcarus's solution is that the file needs to be saved on the server and I don't see any need for that. I also don't know what the coding for that would look like...

 

Creating the file on the fly is the option that I'm looking for.

 

I'm not sure I understand the problem - the query itself produces the correct output and the CR doesn't cause a problem when viewing in MySQL so it has to be in the script interpretation.

 

Is it because PHP gets confused when using "mysql_fetch_row($result)", is that where the CR is the problem?

 

Thanks,

[A JM]

Could someone explain these 3 lines to me?

 

$field->name=$row[$x];

$data .= $field->name = $row[$x];

$data .= "\t";

 

What does the "->" mean?

What does the "$data .=" mean?

 

[cags]

Let's take a look at the structure of a tab delimited table, I'll provide an example with the actual characters and then with the escaped character so you can actually see what's there (\t = tab, \r\n = newline and \s = space.) also note that I left the actually line breaks in to illustrate the point of how the file is parsed more clearly...

 

As Viewed:

id    name    address    phone
1    john    the moon    12345 678910
2    fred    mars    12345 678911

 

With Visible Characters:

id\tname\taddress\tphone\r\n

1\tjohn\tthe moon\t12345 678910\r\n

2\tfred\tmars\t12345 678911

To parse that string most applications will read it in parsing each \r\n as meaning a new row of data, thus meaning you have 3 rows of data each with 4 fields in them. Now lets look at what happens if you add in a field that includes a newline character...

 

As Viewed:

id    name    address    phone
1    john    the moon    12345 678910
2    fred    main street,
mars    12345 678911

 

With Visible Characters:

id\tname\taddress\tphone\r\n

1\tjohn\tthe moon\t12345 678910\r\n

2\tfred\tmain street,\r\n

mars\t12345 678911

Now when parsing, it will appear that we have 4 rows, 2 or which have 4 fields, 1 that has 3 and one that has 2. The way this is normally got around is by using "" characters to enclose fields. This means that if the \r\n occurs inside a set of "" then it is ignored as being part of the field itself not part of the file structure. Our file thus becomes...

 

With Visible Characters:

id\tname\taddress\tphone\r\n

"1"\t"john"\t"the moon"\t"12345 678910"\r\n

"2"\t"fred"\t"main street,\r\nmars"\t"12345 678911"

 

If you wish the file to end up on a client pc not on the server, I would still suggest using the method laid out by JustLikeIcarus. You can then just use readfile to serve it up to the client followed by unlink to remove it from the server.

 

With regards to your latest post the -> is used with OOP PHP and it means the value after the arrow is a field of the object stored in the variable before the arrow. The .= means append the value after to the end of the value before so $a .= $b is === $a = $a . $b.

[A JM]

cags, thanks for the post I'll read it in depth.

 

While I have your attention - the problem field is stored as a "textarea",  just wanted to add that piece of information.

 

[EDIT]

 

Thanks to your post I was able to somewhat figure out what my problem was.  I'm using "wrap virtual" in my textarea - and by using "/r/n" together and trying a find replace I am somewhat able to solve my problem.

 

 

 

Thanks,

[cags]

It can't be stored as a 'textarea', it most likely will be stored as type 'TEXT' (but was likely entered using a textarea). Since it is the linebreaks that causes the problem this was pretty much a given anyway.

[A JM]

Ultimately I solved it using the following, thanks for all the help, "Merry Christmas"  .

 

...

$result = mysql_query($query, $dbconn) or die(mysql_error());

 

$count = mysql_num_rows($result);

$fields= mysql_num_fields($result);

$data = "";

for ($i=0; $i < $fields; $i++) {

$field = mysql_fetch_field($result, $i);

$data .= $field->name;

$data .= "\t";

}

$data .= "\n";

while($row = mysql_fetch_row($result)) {

    $line = '';

    foreach($row as $value) {                                           

        if ((!isset($value)) OR ($value == "")) {

            $value = "\t";

        } else {

            $value = str_replace('"', '""', $value);

    $value = str_replace("\r\n", ' ', $value);

            $value = '"' . $value . '"' . "\t";

        }

        $line .= $value;

    }

    $data .= trim($line)."\n";

}

$data = str_replace("\r","",$data);

print $data;

exit;

 

[cags]

Glad you got it to work to your satisfaction. But it seems a bit of a less effective 'hack', especially as it removes all the linebreaks, which could be an issue (of course the program reading it might not accept them in which case fair enough). I'd have thought to get the contents of the database simply displayed on screen in a tab delimited manner that this would be far better...

 

$file = $_SERVER['DOCUMENT_ROOT'] . "/test.txt";
$sql = "SELECT * FROM table INTO OUTFILE '$file' FIELDS TERMINATED BY '\\t' ENCLOSED BY '\"' LINES TERMINATED BY '\\r\\n';";
mysql_query($sql) or trigger_error("SQL = $sql, ERROR: " . mysql_error(), E_USER_ERROR);
echo file_get_contents($file);
unlink($file);