3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2019 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
10 | CiviCRM is free software; you can copy, modify, and distribute it |
11 | under the terms of the GNU Affero General Public License |
12 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
14 | CiviCRM is distributed in the hope that it will be useful, but |
15 | WITHOUT ANY WARRANTY; without even the implied warranty of |
16 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
17 | See the GNU Affero General Public License for more details. |
19 | You should have received a copy of the GNU Affero General Public |
20 | License and the CiviCRM Licensing Exception along |
21 | with this program; if not, contact CiviCRM LLC |
22 | at info[AT]civicrm[DOT]org. If you have questions about the |
23 | GNU Affero General Public License or the licensing of CiviCRM, |
24 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
25 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2019
33 class CRM_Import_DataSource_CSV
extends CRM_Import_DataSource
{
35 NUM_ROWS_TO_INSERT
= 100;
38 * Provides information about the data source.
41 * collection of info about this data source
43 public function getInfo() {
44 return ['title' => ts('Comma-Separated Values (CSV)')];
48 * Set variables up before form is built.
50 * @param CRM_Core_Form $form
52 public function preProcess(&$form) {
56 * This is function is called by the form object to get the DataSource's form snippet.
58 * It should add all fields necessary to get the data
59 * uploaded to the temporary table in the DB.
61 * @param CRM_Core_Form $form
63 public function buildQuickForm(&$form) {
64 $form->add('hidden', 'hidden_dataSource', 'CRM_Import_DataSource_CSV');
66 $config = CRM_Core_Config
::singleton();
68 $uploadFileSize = CRM_Utils_Number
::formatUnitSize($config->maxFileSize
. 'm', TRUE);
69 //Fetch uploadFileSize from php_ini when $config->maxFileSize is set to "no limit".
70 if (empty($uploadFileSize)) {
71 $uploadFileSize = CRM_Utils_Number
::formatUnitSize(ini_get('upload_max_filesize'), TRUE);
73 $uploadSize = round(($uploadFileSize / (1024 * 1024)), 2);
74 $form->assign('uploadSize', $uploadSize);
75 $form->add('File', 'uploadFile', ts('Import Data File'), 'size=30 maxlength=255', TRUE);
76 $form->setMaxFileSize($uploadFileSize);
77 $form->addRule('uploadFile', ts('File size should be less than %1 MBytes (%2 bytes)', [
80 ]), 'maxfilesize', $uploadFileSize);
81 $form->addRule('uploadFile', ts('Input file must be in CSV format'), 'utf8File');
82 $form->addRule('uploadFile', ts('A valid file must be uploaded.'), 'uploadedfile');
84 $form->addElement('checkbox', 'skipColumnHeader', ts('First row contains column headers'));
88 * Process the form submission.
90 * @param array $params
92 * @param \CRM_Core_Form $form
94 public function postProcess(&$params, &$db, &$form) {
95 $file = $params['uploadFile']['name'];
96 $result = self
::_CsvToTable($db,
98 CRM_Utils_Array
::value('skipColumnHeader', $params, FALSE),
99 CRM_Utils_Array
::value('import_table_name', $params),
100 CRM_Utils_Array
::value('fieldSeparator', $params, ',')
103 $form->set('originalColHeader', CRM_Utils_Array
::value('original_col_header', $result));
105 $table = $result['import_table_name'];
106 $importJob = new CRM_Contact_Import_ImportJob($table);
107 $form->set('importTableName', $importJob->getTableName());
111 * Create a table that matches the CSV file and populate it with the file's contents
114 * Handle to the database connection.
115 * @param string $file
117 * @param bool $headers
118 * Whether the first row contains headers.
119 * @param string $table
120 * Name of table from which data imported.
121 * @param string $fieldSeparator
122 * Character that separates the various columns in the file.
125 * name of the created table
127 private static function _CsvToTable(
132 $fieldSeparator = ','
135 $fd = fopen($file, 'r');
137 CRM_Core_Error
::fatal("Could not read $file");
139 if (filesize($file) == 0) {
140 CRM_Core_Error
::fatal("$file is empty. Please upload a valid file.");
143 $config = CRM_Core_Config
::singleton();
144 // support tab separated
145 if (strtolower($fieldSeparator) == 'tab' ||
146 strtolower($fieldSeparator) == '\t'
148 $fieldSeparator = "\t";
151 $firstrow = fgetcsv($fd, 0, $fieldSeparator);
153 // create the column names from the CSV header or as col_0, col_1, etc.
155 //need to get original headers.
156 $result['original_col_header'] = $firstrow;
158 $strtolower = function_exists('mb_strtolower') ?
'mb_strtolower' : 'strtolower';
159 $columns = array_map($strtolower, $firstrow);
160 $columns = str_replace(' ', '_', $columns);
161 $columns = preg_replace('/[^a-z_]/', '', $columns);
163 // need to take care of null as well as duplicate col names.
164 $duplicateColName = FALSE;
165 if (count($columns) != count(array_unique($columns))) {
166 $duplicateColName = TRUE;
169 // need to truncate values per mysql field name length limits
170 // mysql allows 64, but we need to account for appending colKey
172 foreach ($columns as $colKey => & $colName) {
173 if (strlen($colName) > 58) {
174 $colName = substr($colName, 0, 58);
178 if (in_array('', $columns) ||
$duplicateColName) {
179 foreach ($columns as $colKey => & $colName) {
181 $colName = "col_$colKey";
183 elseif ($duplicateColName) {
184 $colName .= "_$colKey";
189 // CRM-4881: we need to quote column names, as they may be MySQL reserved words
190 foreach ($columns as & $column) {
191 $column = "`$column`";
196 foreach ($firstrow as $i => $_) {
197 $columns[] = "col_$i";
201 // FIXME: we should regen this table's name if it exists rather than drop it
203 $table = 'civicrm_import_job_' . md5(uniqid(rand(), TRUE));
206 $db->query("DROP TABLE IF EXISTS $table");
208 $numColumns = count($columns);
209 $create = "CREATE TABLE $table (" . implode(' text, ', $columns) . " text) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
212 // the proper approach, but some MySQL installs do not have this enabled
213 // $load = "LOAD DATA LOCAL INFILE '$file' INTO TABLE $table FIELDS TERMINATED BY '$fieldSeparator' OPTIONALLY ENCLOSED BY '\"'";
214 // if ($headers) { $load .= ' IGNORE 1 LINES'; }
215 // $db->query($load);
217 // parse the CSV line by line and build one big INSERT (while MySQL-escaping the CSV contents)
225 while ($row = fgetcsv($fd, 0, $fieldSeparator)) {
226 // skip rows that dont match column count, else we get a sql error
227 if (count($row) != $numColumns) {
237 // CRM-17859 Trim non-breaking spaces from columns.
240 return trim($string, chr(0xC2) . chr(0xA0));
242 $row = array_map(['CRM_Core_DAO', 'escapeString'], $row);
243 $sql .= "('" . implode("', '", $row) . "')";
246 if ($count >= self
::NUM_ROWS_TO_INSERT
&& !empty($sql)) {
247 $sql = "INSERT IGNORE INTO $table VALUES $sql";
257 $sql = "INSERT IGNORE INTO $table VALUES $sql";
263 //get the import tmp table name.
264 $result['import_table_name'] = $table;