LEN

PHP csv 配置转换sql Demo
<?php // 铺单csv 文件解析生成sql $csvFile = $argv[1]; if (!f...
扫描右侧二维码阅读全文
19
2019/03

PHP csv 配置转换sql Demo

<?php

// 铺单csv 文件解析生成sql

$csvFile = $argv[1];
if (!file_exists($csvFile)) {
    echo '文件不存在', $csvFile, PHP_EOL;

    return;
}

$data = csvFileToArray($csvFile);

$fieldList = current($data);

unset($fieldList['priceFloatPercentageMin'], $fieldList['priceFloatPercentageMax'], $fieldList['numberFloatMin'], $fieldList['numberFloatMax'], $fieldList['changeTrustNumberFloatMin'], $fieldList['changeTrustNumberFloatMax']);

$fieldList = array_keys($fieldList);

// 字段列表
$fieldList = array_merge($fieldList, ['priceFloatPercentage', 'numberFloat', 'changeTrustNumberFloat']);

$dataList = [];
foreach ($data as $key => $value) {
    $value['priceFloatPercentage'] = implode('-', [$value['priceFloatPercentageMin'], $value['priceFloatPercentageMax']]);
    $value['numberFloat'] = implode('-', [$value['numberFloatMin'], $value['numberFloatMax']]);
    $value['changeTrustNumberFloat'] = implode('-', [$value['changeTrustNumberFloatMin'], $value['changeTrustNumberFloatMax']]);

    $tmpData = [];
    foreach ($fieldList as $field) {
        $tmpData[$field] = $value[$field];
    }

    $dataList[] = $tmpData;
}


$sql = ' insert into pudan_list_box (';
$sql .= '`' . implode('`,`', $fieldList) . '`';
$sql .= ')VALUES';


// 数据处理
foreach ($dataList as $line) {
    $sql .= "('" . implode("','", $line) . "'),";
}

var_dump(trim($sql, ','));

/**
 * @param $csvFile
 * @param bool $isHead
 * @param string $eol 根据平台选择
 * windows平台相当于         "\r\n";
 * unix\linux平台相当于      "\n";
 * mac平台相当于             "\r";
 * @param string $delimiter
 * @return mixed
 * ------------------------------------------------------------
 */
function csvFileToArray($csvFile, $isHead = true, $eol = "\r", $delimiter = ',')
{
    $contents = file_get_contents($csvFile);
    // 分行
    $lineArray = explode($eol, encodeToUtf8($contents));

    if ($lineArray) {
        $lineArray = array_map(function ($line) use ($delimiter) {
            return array_map('trim', explode($delimiter, $line));
        }, $lineArray);
    }

    // 第一行当做头部处理

    $fieldList = array_shift($lineArray);

    if ($isHead) {

        foreach ($lineArray as $key => $values) {

            $lineArray[$key] = array_combine($fieldList, $values);
        }
    }

    return $lineArray;
}


/**
 * @编码格式转换
 * @param $content
 * @return string
 * @throws Exception
 * ------------------------------------------------------------
 */
function encodeToUtf8($content)
{
    $newContent = iconv('', 'UTF-8', $content);
    if (is_bool($newContent)) {
        throw new Exception('编码错误' . $content, 500);
    }

    return $newContent;
}
Last modification:March 19th, 2019 at 10:11 am
If you think my article is useful to you, please feel free to appreciate

Leave a Comment