使用PHP将制表符分隔文本文件读入MySQL表 [英] Read tab delimited text file into MySQL table with PHP

问题描述

我正在尝试将一系列制表符分隔的文本文件读入现有的MySQL表中。我的代码非常简单：

I am trying to read in a series of tab delimited text files into existing MySQL tables. The code I have is quite simple:

$lines = file("import/file_to_import.txt");

foreach ($lines as $line_num => $line) {
    if($line_num > 1) {
        $arr = explode("\t", $line);
        $sql = sprintf("INSERT INTO my_table VALUES('%s', '%s', '%s', %s, %s);", trim((string)$arr[0]), trim((string)$arr[1]), trim((string)$arr[2]), trim((string)$arr[3]), trim((string)$arr[4]));
        mysql_query($sql, $database) or die(mysql_error());
    }
}

但无论我做什么（因此之前的演员表） sprintf语句中的每个变量）我得到你的SQL语法中有错误;检查与你的MySQL服务器版本相对应的手册，以便在'第1行'附近使用正确的语法错误。

But no matter what I do (hence the casting before each variable in the sprintf statement) I get the "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1" error.

我回应代码，将其粘贴到MySQL编辑器中运行正常，它不会从PHP脚本执行。

I echo out the code, paste it into a MySQL editor and it runs fine, it just won't execute from the PHP script.

我做错了什么？

Si

更新：以下是回声的SQL ：

UPDATE: Here are the echoe'd SQL's:

INSERT INTO wheelbase (WheelBaseCode, LanguageCode, WheelBaseDescription) VALUES ('A1', 'GBEN', '2.50-2.99m')
INSERT INTO wheelbase (WheelBaseCode, LanguageCode, WheelBaseDescription) VALUES ('A2', 'GBEN', '3.00-3.49m')
INSERT INTO wheelbase (WheelBaseCode, LanguageCode, WheelBaseDescription) VALUES ('A3', 'GBEN', '3.50-3.99m')
INSERT INTO wheelbase (WheelBaseCode, LanguageCode, WheelBaseDescription) VALUES ('A4', 'GBEN', '4.00-4.49m')

<有趣的是，我现在让它在表中创建正确的行数，但它插入的值是空的...

Interestingly, I now have it creating the correct number of rows in the table, but the values it inserts are empty...

这可能是一个编码问题源文本文件??

Could this be an encoding issue in the source text file??

推荐答案

您不需要字符串强制转换，数据已经是字符串。

You don't need the string cast, the data will already be strings.

确保文件数据中没有引号。在运行之前回显sql字符串以查看是否存在明显错误。

Make sure there are no quotes in the file data. Echo out the sql string before you run it to see if there's something obviously wrong.

将SQL更改为：

"INSERT INTO my_table (`field1Name`, `field2Name`, `field3Name`, `field4Name`, `field5Name`) VALUES('%s', '%s', '%s', '%s', '%s');"

此更改包括字段名称，并引用最后两个值。

This change includes the field names, and quoting the last two values.

这篇关于使用PHP将制表符分隔文本文件读入MySQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！