php - 在 PHP 中转换 Base64 编码的制表符分隔文件

Question

我正在编写一个报告应用程序，它需要使用以 base 64 编码字符串形式存储在数据库中的日志。我能够毫无问题地解码它们，但是，我在将它们正确输入 str_getcsv() 时遇到了一些问题。

下面是我正在处理的数据、代码和输出。在我看来，一旦解码，文件就无法识别为制表符分隔。但是，如果我使用此 URL 对其进行解码并将其另存为文本文件，则可以在 excel 中正确打开它。

https://www.base64decode.org/

然而，在 PHP 中，识别某些制表符似乎是个问题，并且换行符似乎完全消失了。我认为这与编码有关，数据库表和列都是 UTF-8。它们被识别为 ASCII - 它是 UTF-8 的一个子集，但我不确定它们是否需要明确为 UTF-8 才能工作(工作的站点使用 UTF-8)。

代码:非常简单(虽然在这一点上我可能会过分使用编码)

            // get the stored result (laravel eloquent)
        $media_result = MediaResult::where("video_id", "=", $media_benchmark->id)->firstOrFail();


        # decode the access_log stored as b64 string
        $tab_file = base64_decode(mb_convert_encoding($media_result->access_log, "UTF-8"));

        $encoding =  mb_detect_encoding($tab_file); // I was using iconv() so I grabbed this - it is always ASCII

        $new_file = mb_convert_encoding($tab_file,'UTF-8');

        $encoding_new =  mb_detect_encoding($new_file);

        #if I were to echo both encoding variables, it would be ASCII - no matter what I do.


        # convert the supposed tab-delimited file into an array
        $full_stats = str_getcsv($new_file, 0, "\t");

这是一个 base64 编码的示例日志:

VVJJCXNlcnZlckFkZHJlc3MJbnVtYmVyT2ZTZXJ2ZXJBZGRyZXNzQ2hhbmdlcwltZWRpYVJlcXVlc3RzV1dBTgl0cmFuc2ZlckR1cmF0aW9uCW51bWJlck9mQnl0ZXNUcmFuc2ZlcnJlZAludW1iZXJPZk1lZGlhUmVxdWVzdHMJcGxheWJhY2tTdGFydERhdGUJcGxheWJhY2tTZXNzaW9uSUQJcGxheWJhY2tTdGFydE9mZnNldAlwbGF5YmFja1R5cGUJc3RhcnR1cFRpbWUJZHVyYXRpb25XYXRjaGVkCW51bWJlck9mRHJvcHBlZFZpZGVvRnJhbWVzCW51bWJlck9mU3RhbGxzCW51bWJlck9mU2VnbWVudHNEb3dubG9hZGVkCXNlZ21lbnRzRG93bmxvYWRlZER1cmF0aW9uCWRvd25sb2FkT3ZlcmR1ZQlvYnNlcnZlZEJpdHJhdGVTdGFuZGFyZERldmlhdGlvbglvYnNlcnZlZE1heEJpdHJhdGUJb2JzZXJ2ZWRNaW5CaXRyYXRlCXN3aXRjaEJpdHJhdGUJaW5kaWNhdGVkQml0cmF0ZQlvYnNlcnZlZEJpdHJhdGUKaHR0cDovL3Zldm9wbGF5bGlzdC1saXZlLmhscy5hZGFwdGl2ZS5sZXZlbDMubmV0L3Zldm8vY2gxLzAxL3Byb2dfaW5kZXgubTN1OAk4LjI1NC4yMy4yNTQJMAkwCTAuNjc4MjgwNzA5CTEwOTk2MTIJMwkyMDE2LTA1LTEwIDE5OjIxOjE4ICswMDAwCTdBMTI5MERDLTE2MzAtNDlGQy1BQTY0LUNDNzZDMTgxQzcyQQk0MglMSVZFCTAuMjUzMjk3OTg0NjAwMDY3MQkxNi4wODMyNjU5NjAyMTY1MgkwCTAJMwkxOAkwCS0xCTI1NTcyOTAxLjM4MzMwNzg3CTE4MjA3OTg3LjMyODUyNTkJMTAxMTU1NDguNzgzODE4MjUJNDkyMDAwCTIxMDI1OTU1LjA1Mzg4OTI0Cmh0dHA6Ly92ZXZvcGxheWxpc3QtbGl2ZS5obHMuYWRhcHRpdmUubGV2ZWwzLm5ldC92ZXZvL2NoMS8wNi9wcm9nX2luZGV4Lm0zdTgJOC4yNTMuMzIuMTI2CTgJMAkzNS43NDAxNjM2MjIJMTIzNDgxOTcyCTQzCTIwMTYtMDUtMTAgMTk6MjE6MzQgKzAwMDAJN0ExMjkwREMtMTYzMC00OUZDLUFBNjQtQ0M3NkMxODFDNzJBCTU4LjAyODk5NDM1OAlMSVZFCTAJMjQxLjkyNjk3NTk2NTQ5OTkJMAkwCTQzCTI1OAkwCS0xCTQ2ODg1OTAzLjAzNTk4OTkzCTEwODA3NDU3LjM4MjQwNjY3CS0xCTQwMDAwMDAJMzE3ODIzNjAuNjE0NTI4NjM=

这是解码后的同一个字符串:

URI serverAddress   numberOfServerAddressChanges    mediaRequestsWWAN   transferDuration    numberOfBytesTransferred    numberOfMediaRequests   playbackStartDate   playbackSessionID   playbackStartOffset playbackType startupTime    durationWatched numberOfDroppedVideoFrames  numberOfStalls  numberOfSegmentsDownloaded  segmentsDownloadedDuration  downloadOverdue observedBitrateStandardDeviation    observedMaxBitrate observedMinBitrate   switchBitrate   indicatedBitrate    observedBitrate http://vevoplaylist-live.hls.adaptive.level3.net/vevo/ch1/01/prog_index.m3u8    8.254.23.254    0   0   0.678280709 1099612 3   2016-05-10 19:21:18 +0000   7A1290DC-1630-49FC-AA64-CC76C181C72A    42  LIVE    0.2532979846000671  16.08326596021652   0   0   3   18  0   -1  25572901.38330787   18207987.3285259    10115548.78381825   492000  21025955.05388924 http://vevoplaylist-live.hls.adaptive.level3.net/vevo/ch1/06/prog_index.m3u8  8.253.32.126    8   0   35.740163622    123481972   43  2016-05-10 19:21:34 +0000   7A1290DC-1630-49FC-AA64-CC76C181C72A    58.028994358    LIVE    0   241.9269759654999   0   0 43    258 0   -1  46885903.03598993   10807457.38240667   -1  4000000 31782360.61452863

最后，这是结果数组:

Array ( [0] => URI  serverAddress   numberOfServerAddressChanges    mediaRequestsWWAN   transferDuration    numberOfBytesTransferred    numberOfMediaRequests   playbackStartDate   playbackSessionID   playbackStartOffset playbackType    startupTime durationWatched numberOfDroppedVideoFrames  numberOfStalls  numberOfSegmentsDownloaded  segmentsDownloadedDuration  downloadOverdue observedBitrateStandardDeviation observedMaxBitrate observedMinBitrate  switchBitrate   indicatedBitrate    observedBitrate http://vevoplaylist-live.hls.adaptive.level3.net/vevo/ch1/ [1] => 1/prog_index.m3u8 8.254.23.254    [2] => 0 [3] => .67828 [4] => 7 [5] => 9    1 [6] => 99612  3   2 [7] => 16- [8] => 5-1 [9] => 19:21:18 + [10] => [11] => [12] => [13] => 7A1290DC-1630-49FC-AA64-CC76C181C72A42    LIVE    [14] => .2532979846 [15] => [16] => [17] => 671 16. [18] => 8326596 [19] => 21652   [20] => 03  18  [21] => -1255729 [22] => 1.3833 [23] => 787 182 [24] => 7987.3285259    1 [25] => 115548.78381825   492 [26] => [27] => [28] => 21025955.05388924 http://vevoplaylist-live.hls.adaptive.level3.net/vevo/ch1/06/prog_index.m3u88.253.32.126  8   [29] => 35.740163622123481972   43  2 [30] => 16- [31] => 5-1 [32] => 19:21:34 + [33] => [34] => [35] => [36] => 7A1290DC-1630-49FC-AA64-CC76C181C72A58. [37] => 28994358   LIVE    [38] => 241.9269759654999 [39] => 043   258 [40] => -1468859 [41] => 3. [42] => 3598993 1 [43] => 8 [44] => 7457.3824 [45] => 667   -1  4 [46] => [47] => [48] => [49] => [50] => [51] => 31782360.61452863 )

Answer 1

请记住 str_getcsv()

• 仅解析 csv 文件的一行
• 期望分隔符“\t”是第二个参数，而不是第三个

你可能想要这样的东西:

$full_stats = [];
foreach(explode("\n", $decoded) as $line) {
    $full_stats[] = str_getcsv($line, "\t");
}
var_dump($full_stats);

这将输出一个包含 3 个数组(也称为行)的数组，每个数组包含 24 个项目(也称为列)。

参见 http://sandbox.onlinephpfunctions.com/code/1ccf5115df6f8c342ff7c7e451f3ea26e081197e用于工作示例和生成的输出。

关于导入包含换行符的数据，您应该切换到正确处理换行符的 fget_csv():

$csv = <<< eot
"first","my data
with line breaks"
"second", "simple data"
eot;

// We need to "convert" the string to a file handle
$fp = fopen('data://text/plain,' . $csv,'r');
while ($data = fgetcsv($fp)) {
    var_dump($data);
}