3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
6 | This work is published under the GNU AGPLv3 license with some |
7 | permitted exceptions and without any warranty. For full license |
8 | and copyright information, see https://civicrm.org/licensing |
9 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
17 class CRM_Import_DataSource_CSV
extends CRM_Import_DataSource
{
19 NUM_ROWS_TO_INSERT
= 100;
22 * Form fields declared for this datasource.
26 protected $submittableFields = ['skipColumnHeader', 'uploadField'];
29 * Provides information about the data source.
32 * collection of info about this data source
34 public function getInfo(): array {
35 return ['title' => ts('Comma-Separated Values (CSV)')];
39 * Set variables up before form is built.
41 * @param CRM_Core_Form $form
43 public function preProcess(&$form) {
47 * This is function is called by the form object to get the DataSource's form snippet.
49 * It should add all fields necessary to get the data
50 * uploaded to the temporary table in the DB.
52 * @param CRM_Core_Form $form
54 * @throws \CRM_Core_Exception
56 public function buildQuickForm(&$form) {
57 $form->add('hidden', 'hidden_dataSource', 'CRM_Import_DataSource_CSV');
59 $config = CRM_Core_Config
::singleton();
61 $uploadFileSize = CRM_Utils_Number
::formatUnitSize($config->maxFileSize
. 'm', TRUE);
62 //Fetch uploadFileSize from php_ini when $config->maxFileSize is set to "no limit".
63 if (empty($uploadFileSize)) {
64 $uploadFileSize = CRM_Utils_Number
::formatUnitSize(ini_get('upload_max_filesize'), TRUE);
66 $uploadSize = round(($uploadFileSize / (1024 * 1024)), 2);
67 $form->assign('uploadSize', $uploadSize);
68 $form->add('File', 'uploadFile', ts('Import Data File'), NULL, TRUE);
69 $form->setMaxFileSize($uploadFileSize);
70 $form->addRule('uploadFile', ts('File size should be less than %1 MBytes (%2 bytes)', [
73 ]), 'maxfilesize', $uploadFileSize);
74 $form->addRule('uploadFile', ts('Input file must be in CSV format'), 'utf8File');
75 $form->addRule('uploadFile', ts('A valid file must be uploaded.'), 'uploadedfile');
77 $form->addElement('checkbox', 'skipColumnHeader', ts('First row contains column headers'));
81 * Process the form submission.
83 * @param array $params
85 * @param \CRM_Core_Form $form
87 * @throws \API_Exception
88 * @throws \CRM_Core_Exception
90 public function postProcess(&$params, &$db, &$form) {
91 $file = $params['uploadFile']['name'];
92 $firstRowIsColumnHeader = $params['skipColumnHeader'] ??
FALSE;
93 $result = self
::_CsvToTable(
95 $firstRowIsColumnHeader,
96 CRM_Utils_Array
::value('import_table_name', $params),
97 CRM_Utils_Array
::value('fieldSeparator', $params, ',')
100 $form->set('originalColHeader', CRM_Utils_Array
::value('column_headers', $result));
101 $form->set('importTableName', $result['import_table_name']);
102 $this->updateUserJobMetadata('DataSource', [
103 'table_name' => $result['import_table_name'],
104 'column_headers' => $firstRowIsColumnHeader ?
$result['column_headers'] : [],
105 'number_of_columns' => $result['number_of_columns'],
110 * Create a table that matches the CSV file and populate it with the file's contents
112 * @param string $file
114 * @param bool $headers
115 * Whether the first row contains headers.
116 * @param string $tableName
117 * Name of table from which data imported.
118 * @param string $fieldSeparator
119 * Character that separates the various columns in the file.
122 * name of the created table
123 * @throws \CRM_Core_Exception
125 private static function _CsvToTable(
129 $fieldSeparator = ','
132 $fd = fopen($file, 'r');
134 throw new CRM_Core_Exception("Could not read $file");
136 if (filesize($file) == 0) {
137 throw new CRM_Core_Exception("$file is empty. Please upload a valid file.");
140 // support tab separated
141 if (strtolower($fieldSeparator) === 'tab' ||
142 strtolower($fieldSeparator) === '\t'
144 $fieldSeparator = "\t";
147 $firstrow = fgetcsv($fd, 0, $fieldSeparator);
149 // create the column names from the CSV header or as col_0, col_1, etc.
151 //need to get original headers.
152 $result['column_headers'] = $firstrow;
154 $strtolower = function_exists('mb_strtolower') ?
'mb_strtolower' : 'strtolower';
155 $columns = array_map($strtolower, $firstrow);
156 $columns = str_replace(' ', '_', $columns);
157 $columns = preg_replace('/[^a-z_]/', '', $columns);
159 // need to take care of null as well as duplicate col names.
160 $duplicateColName = FALSE;
161 if (count($columns) != count(array_unique($columns))) {
162 $duplicateColName = TRUE;
165 // need to truncate values per mysql field name length limits
166 // mysql allows 64, but we need to account for appending colKey
168 foreach ($columns as $colKey => & $colName) {
169 if (strlen($colName) > 58) {
170 $colName = substr($colName, 0, 58);
174 if (in_array('', $columns) ||
$duplicateColName) {
175 foreach ($columns as $colKey => & $colName) {
177 $colName = "col_$colKey";
179 elseif ($duplicateColName) {
180 $colName .= "_$colKey";
185 // CRM-4881: we need to quote column names, as they may be MySQL reserved words
186 foreach ($columns as & $column) {
187 $column = "`$column`";
192 foreach ($firstrow as $i => $_) {
193 $columns[] = "col_$i";
198 CRM_Core_DAO
::executeQuery("DROP TABLE IF EXISTS $tableName");
200 $table = CRM_Utils_SQL_TempTable
::build()->setDurable();
201 $tableName = $table->getName();
202 CRM_Core_DAO
::executeQuery("DROP TABLE IF EXISTS $tableName");
203 $table->createWithColumns(implode(' text, ', $columns) . ' text');
205 $numColumns = count($columns);
207 // the proper approach, but some MySQL installs do not have this enabled
208 // $load = "LOAD DATA LOCAL INFILE '$file' INTO TABLE $table FIELDS TERMINATED BY '$fieldSeparator' OPTIONALLY ENCLOSED BY '\"'";
209 // if ($headers) { $load .= ' IGNORE 1 LINES'; }
210 // $db->query($load);
212 // parse the CSV line by line and build one big INSERT (while MySQL-escaping the CSV contents)
220 while ($row = fgetcsv($fd, 0, $fieldSeparator)) {
221 // skip rows that dont match column count, else we get a sql error
222 if (count($row) != $numColumns) {
225 // A blank line will be array(0 => NULL)
226 if ($row === [NULL]) {
236 // CRM-17859 Trim non-breaking spaces from columns.
237 $row = array_map(['CRM_Import_DataSource_CSV', 'trimNonBreakingSpaces'], $row);
238 $row = array_map(['CRM_Core_DAO', 'escapeString'], $row);
239 $sql .= "('" . implode("', '", $row) . "')";
242 if ($count >= self
::NUM_ROWS_TO_INSERT
&& !empty($sql)) {
243 CRM_Core_DAO
::executeQuery("INSERT IGNORE INTO $tableName VALUES $sql");
252 CRM_Core_DAO
::executeQuery("INSERT IGNORE INTO $tableName VALUES $sql");
257 //get the import tmp table name.
258 $result['import_table_name'] = $tableName;
259 $result['number_of_columns'] = $numColumns;
264 * Trim non-breaking spaces in a multibyte-safe way.
265 * See also dev/core#2127 - avoid breaking strings ending in à or any other
266 * unicode character sharing the same 0xA0 byte as a non-breaking space.
268 * @param string $string
269 * @return string The trimmed string
271 public static function trimNonBreakingSpaces(string $string): string {
272 $encoding = mb_detect_encoding($string, NULL, TRUE);
273 if ($encoding === FALSE) {
274 // This could mean a couple things. One is that the string is
275 // ASCII-encoded but contains a non-breaking space, which causes
276 // php to fail to detect the encoding. So let's just do what we
277 // did before which works in that situation and is at least no
278 // worse in other situations.
279 return trim($string, chr(0xC2) . chr(0xA0));
281 elseif ($encoding !== 'UTF-8') {
282 $string = mb_convert_encoding($string, 'UTF-8', [$encoding]);
284 return preg_replace("/^(\u{a0})+|(\u{a0})+$/", '', $string);