使用PHP获取CSV文件的第一行并使用数据创建MySQL表 [英] Using PHP to take the first line of a CSV file and create a MySQL Table with the data

问题描述

我想要一个相当大的CSV文件，并将其插入到MySQL数据库中，以便在项目中引用。我想使用文件的第一行创建表使用适当的数据类型，而不是每列的varchar。最终目标是自动化这个过程，因为我有几个类似的文件，但每个在CSV文件中有不同的数据和不同数量的列。我所遇到的问题是gettype（）返回'string'为每个列，而不是int，float和字符串，因为我想它。

I am trying to take a rather large CSV file and insert it into a MySQL database for referencing in a project. I would like to use the first line of the file to create the table using proper data types and not varchar for each column. The ultimate goal is to automate this process as I have several similar files but the each has different data and a different amount of "columns" in CSV files. The problem that I am having is gettype() is returning 'string' for each column instead of int, float and string as I would like it to.

平台是PHP 5，OS是ubuntu 8.04

Platform is PHP 5, OS is ubuntu 8.04

这里是我的代码到目前为止：

here is my code so far:

<?php

// GENERATE TABLE FROM FIRST LINE OF CSV FILE

$inputFile = 'file.csv';
$tableName = 'file_csv';

$fh = fopen($inputFile, 'r');
    $contents = fread($fh, 5120); // 5KB
fclose($fh);

$fileLines = explode("\n", $contents); // explode to make sure we are only using the first line.

$fieldList = explode(',', $fileLines[0]); // separate columns, put into array
echo 'CREATE TABLE IF NOT EXISTS `'.$tableName.'` ('."<br/>\n";
for($i = 0; $i <= count($fieldList); $i++)
{
    switch(gettype($fieldList[$i])) {
        case 'integer':
            $typeInfo = 'int(11)';
            break;
        case 'float':
            $typeInfo = 'float';
            break;
        case 'string':
            $typeInfo = 'varchar(80)';
            break;
        default:
            $typeInfo = 'varchar(80)';
            break;
    }
if(gettype($fieldList[$i]) != NULL) echo "\t".'`'.$i.'` '.$typeInfo.' NOT NULL, --'.gettype($fieldList[$i]).' '.$fieldList[$i]."<br/>\n";
}
echo '  PRIMARY KEY  (`0`)'."<br/>\n";
echo ') ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;';

示例第一行：
1,0,0,0,0,0,0 ，0,0,0,0,0,0.000000,0.000000,0,0,0，，0,0,1,0,50，'Call of Recall（OLD）'，

Example First line: 1,0,0,0,0,0,0,0,0,0,0,0,0.000000,0.000000,0,0,0,,0,0,1,0,50,'Word of Recall (OLD)',

推荐答案

建立在 Nouveau 的代码你可以这样做

Building on Nouveau's code you could do this

for($i = 0; $i <= count($fieldList); $i++)
{
    if (is_numeric($fieldList[$i]))
    {
        if (strpos($fieldList[$i],'.') !== false){
            $fieldList[$i] = (int)$fieldList[$i];
        }else{
            $fieldList[$i] = (float)$fieldList[$i];
        }
    }

    switch(gettype($fieldList[$i])) {
        case 'integer':
            $typeInfo = 'int(11)';
            break;
        case 'float':
        case 'double':
            $typeInfo = 'float';
            break;

        case 'string':
            $typeInfo = 'varchar(80)';
            break;
        default:
            $typeInfo = 'varchar(80)';
            break;
    }
if(gettype($fieldList[$i]) != NULL) echo "\t".'`'.$i.'` '.$typeInfo.' NOT NULL, --'.gettype($fieldList[$i]).' '.$fieldList[$i]."<br/>\n";

}

：在交换机，但可能有一个更好的方法来做int / float检查，因为他们只能工作在标准uk / us号码。

That works, note the addition of "case 'double':" in the switch, but there may be a better way to do the int/float check as they would only work with standard uk/us numbers.

这篇关于使用PHP获取CSV文件的第一行并使用数据创建MySQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！