Can't import client csv file

Alan

Besides checking the characters in the field (I ran searches for the wrong apostrophes), I'd suggest halving the file etc, to slowly pin down which rows are the issues. If you're using Excel at any point in the process, you may also want to spin the csv up in Notepad++ or similar, to check, as Excel can often mess with formatting.

wrongecho

Special characters really shouldn't matter, but I bet we're not escaping them properly somewhere.

wrongecho

@athornfam2 After this (itflow-org/itflow1056) is merged, can you update & try the import again please?

Would be handy to see PHP error logs from the time of the import if there are still issues.

johnny

wrongecho We just pulled in @wrongecho PR so we are ready for the test

johnny

I would like to see the errors too, everything appears sanitized so I don't think it was an issue with escaping but it might even be a php timeout

athornfam2

Where's the log file? @wrongecho

wrongecho

athornfam2

Probably /var/log/apache2/error.log

Do something like tail -F /var/log/apache2/error.log

And then do the import so you can narrow down the spedific lines

athornfam2

wrongecho

[Fri Sep 20 12:33:53.573968 2024] [php:notice] [pid 3078232] [client 192.168.4.103:53355] PHP Notice: Only variables should be passed by reference in /var/www/itflow.##.com/post/client.php on line 386, referer: http://itflow.##.local/clients.php

[Fri Sep 20 12:33:53.575445 2024] [php:error] [pid 3078232] [client 192.168.4.103:53355] PHP Fatal error: Uncaught mysqli_sql_exception: Incorrect string value: '\\x92 Asso...' for column `itflow`.`clients`.`client_name` at row 1 in /var/www/itflow.##.com/post/client.php:531\nStack trace:\n#0 /var/www/itflow.##.com/post/client.php(531): mysqli_query()\n#1 /var/www/itflow.##.com/post.php(29): require_once('...')\n#2 {main}\n thrown in /var/www/itflow.##.com/post/client.php on line 531, referer: http://itflow.##.local/clients.php

wrongecho

athornfam2

To confirm, these are after todays update?

athornfam2

wrongecho That is correct.

I can't seem to import more than 3 at a time 4 if I'm lucky. It also has trouble with / ' , etc from what I've found when trying to import all these clients/leads

wrongecho

Can you share the line in the CSV that starts with \\x92 Asso... please?

athornfam2

wrongecho I don't have any line that starts with \\x92 asso or any combination of that.

wrongecho

Could you run the following database query, either via phpMyAdmin or running mysql as root, and share the output?

use itflow; -- (or whatever your ITFlow DB is named)

show variables like "character_set_database";

johnny

@wrongecho I think I know what the problem is: The function sanitizeInput() strips tags I wonder if we should probably just be using mysqlescapestring() function for CSV imports

wrongecho

johnny I don't see why stripping tags would be a problem.

johnny

yeah me either @wrongecho @athornfam2 could you share that file with us it will be confidential

Here's a secure drop link to send to us that CSV

https://cloud.itflow.org/s/5o4jFpcZb5ppGGm

athornfam2

johnny

Under the conditions that the data inside it will be confidential and only shared internally with ITflow staff I can do that.

athornfam2

Should have it now.

wrongecho

athornfam2

Thanks. Only Johnny and I have access to NextCloud, we wouldn't share this beyond us and will delete once we're done testing.

At first glance, I am thinking it's an encoding issue with the file and/or your database. You have backticks (`) rather than apostrophes('), so maybe we should just strip those? @johnny

wrongecho

@athornfam2

Correction here, the character is (’) - https://www.codetable.net/hex/92

This is apparently not a UTF-8 character but a Windows-1252 private use character - I wasn't even aware of the difference before looking this up. If you open your CSV in Notepad++ and select Encoding > UTF8 you see the same x92 we saw in your error message.

I've been pulling my hair out with this. It seems you'll only run into the issue when importing data via CSV because browsers will seemingly convert the character for you*?*

This PR should detect non-UTF8 characters and attempt to convert them. It might not get them 100% of the time, but it should sort it out for you based on what you were able to share.

itflow-org/itflow1067

Edit: This fix requires the MBString PHP extension.