我有一个文本文件,里面有很多插入,看起来像这样:
INSERT INTO yyy VALUES ('1','123123','da,sdadwa','6.7','24f,5','f5,5','dasdad,fsdfsdfsfsasada dasdasd','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','dasdasd','q231e','','0','','g','1','123123','dasdadwa','6.7','24f,5','f5,5','dasdad,fsdfsdfsfsasada dasdasd','','','q231e','','0','','a','1','123123','dasdadwa','655.755','24f,5','f5,5','dasdad,fsdfsdfsfsasada dasdasd','','','q231e','','','','a');
INSERT INTO yyy VALUES ('2','123123','dasdadwa','6.8','24f,6','f5,5','dasdad,fsdfsdfsfsasada dasdasd','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','dasdasd','q231e','','0','','g','2','123123','dasdadwa','6.8','24f,6','f5,5','dasdad,fsdfsdfsfsasada dasdasd','','','q231e','','0','','a','2','123123','dasdadwa','6.8','24f,6','f5,5','dasdad,fsdfsdfsfsasada dasdasd','','','q231e','','','','a');
INSERT INTO yyy VALUES ('3','123123','dasdadwa','6.9','24f,7','f5,5','dasdad,fsdfsdfsfsasada dasdasd','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','dasdasd','q231e','','0','','g','3','123123','dasdadwa','6.9','24f,7','f5,5','dasdad,fsdfsdfsfsasada dasdasd','','','q231e','','0','','a','3','123123','dasdadwa','6.9','24f,7','f5,5','dasdad,fsdfsdfsfsasada dasdasd','','','q231e','','','','a');
INSERT INTO yyy VALUES ('4','123123','dasdadwa','6.10','24f,8','f5,5','dasdad,fsdfsdfsfsasada dasdasd','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','dasdasd','q231e','','0','','g','4','123123','dasdadwa','6.10','24f,8','f5,5','dasdad,fsdfsdfsfsasada dasdasd','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','','q231e','','0','','a','4','123123','dasdadwa','6.10','24f,8','f5,5','dasdad,fsdfsdfsfsasada dasdasd','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','','q231e','','','','a');
INSERT INTO yyy VALUES ('5','123123','dasdadwa','6.11','24f,9','f5,5','dasdad,fsdfsdfsfsasada dasdasd','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','dasdasd','q231e','','0','','g','5','123123','dasdadwa','6.11','24f,9','f5,5','dasdad,fsdfsdfsfsasada dasdasd','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','','q231e','','0','','a','5','123123','dasdadwa','6.11','24f,9','f5,5','dasdad,fsdfsdfsfsasada dasdasd','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','','q231e','','','','a');
我必须修改这个文本文件,使每行最多可以有50个字符。问题是,我不能简单地在50个字符之后加上一个结束行,因为这会打断那些插入的元素,所以我需要在最后一个逗号之前加上结束行。
对于第一行,它需要像这样:
INSERT INTO yyy VALUES ('1','123123','da,sdadwa',
'6.7','24f,5','f5,5',
'dasdad,fsdfsdfsfsasada dasdasd',
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',
'dasdasd','q231e','','0','','g','1','123123',
'dasdadwa','6.7','24f,5','f5,5',
'dasdad,fsdfsdfsfsasada dasdasd','','','q231e','',
'0','','a','1','123123','dasdadwa','655.755',
'24f,5','f5,5','dasdad,fsdfsdfsfsasada dasdasd',
'','','q231e','','','','a');
正如您所看到的,甚至在元素('da, saddwa ')中也可以有逗号,这使得这有点困难。我试着把所有东西都放进数组,但我遇到了一些问题,无法让它工作。我试过的:
if(is_array($testFileContents))
{
foreach($testFileContents as $line)
{
$j=0;
for($i=0;$i<=strlen($line);$i++)
{
//echo $line[$i];
$ct=1;
if($j==50)
{
if($line[$j]==",")
{
//$line[$j]=$line[$j].PHP_EOL;
}
else
{
$temporaryJ = $j;
while($line[$temporaryJ]!=",")
{
$temporaryJ--;
}
//$line[$temporaryJ] = $line[$temporaryJ].PHP_EOL;
//$j=$i-$ct*50;
$j=0;
$ct=$ct+1;
echo $ct." ";
}
}
else
{
$j++;
}
}
}
}
我知道应该有一种更简单的方法来解决这个问题,而不是使用数组,但我想不出来
可以使用preg_split()分隔行。我发现了另一个用户在这个答案中发布的一个模式,用于匹配INSERT语句的值:"~'(?:'''''|[^'])*'(*SKIP)(*F)|,~"
。这使用了特殊的回溯控制动词。
你可以在这个PhpFiddle中使用PHP代码。
foreach($lines as $line) {
$matches = preg_split("~'(?:'''''|[^'])*'(*SKIP)(*F)|,~",$line);
$currentIndex = 0;
$currentLine = '';
$outputLines = array();
$delimeter = ',';
while($currentIndex < count($matches)) {
if ($currentIndex == count($matches)-1 ) {
$delimeter = '';
}
$tempLine = $currentLine . $matches[$currentIndex] . $delimeter;
if (strlen($tempLine) <= 50) {
$currentLine .= $matches[$currentIndex] . $delimeter;
}
else {//push current line into array and start a new line
$outputLines[] = $currentLine;
$currentLine = $matches[$currentIndex] . $delimeter;
}
if ($currentIndex == count($matches)-1 ) {
$outputLines[] = $currentLine;
}
$currentIndex++;
}
//can use implode("'n",$outputLines) to write out to file
//or whatever your needs are
}