Always trouble importing CSV

mrfdes's profile image mrfdes posted 2 years ago in General Permalink

Hi, I always have problems importing CSV files in Heidi and often have to resort to going to my hosting's CPanel and importing from there. I have a tale with 3 fields: Nr (INT), Lied, Artiest (both VARCHAR) they contain the number, songtitle and artist of thsongs in my radio's repertoire. The latest CSV I tried to import looked like the one attached.

The semicolon at the beginning is there because I simply want the files in the CSV to be appended to the end of the already existing list. Also attached you will find an image of the import settings I use. After clicking "Import" I keep getting an error "Invalid integer in collumn 1 'Nr'", so there must be something wrong with my 1st field in my CSV field.

Could you please help me where I am going wrong?

Thank you so much in advance.

2 attachment(s):
ansgar's profile image ansgar posted 2 years ago Permalink

Two things which should solve your issue:

  • do not prepend rows with a semicolon. Instead, deactivate the "Nr" column, like in the screenshot below
  • prefer "Server parses" method if "Client parses" fails.

Description

mrfdes's profile image mrfdes posted 2 years ago Permalink

Hi, Thank you for your reply. I have tried in the past to leave Nr. out, but then I got the error message the column could not be NULL. I tried the LOAD DATA solution yesterday and got an error message saying that "This version of MariaDB does not support this command" (although I am on MySQL), but when I checked my DB, all my data had been added correctly. Thank you again for your help.

ansgar's profile image ansgar posted 2 years ago Permalink

I got the error message the column could not be NULL

Then alter your "Nr" column to a primary key column with auto_increment attribute. That way the database auto-creates a number for it, and does not complain about null.

The "Client parses" method may be also ok, but I think you really need to get rid of the trailing semicolon in that case.

mrfdes's profile image mrfdes posted 2 years ago Permalink

That is what surprises me so much, in my table the Nr column IS an auto incrementing primary key.

Nevertheless, thank you for your continuing help.

ansgar's profile image ansgar posted 2 years ago Permalink

Ok, then you might still have the "Nr" column checked in the "Destination" box of the import dialog. You need to uncheck it, along with getting rid of trailing semicolons.

mrfdes's profile image mrfdes posted 2 years ago Permalink

Hi, I tried your suggestions. The first time I got the warning "This command is not supported in this version of MariaDB", but all my rows had been added correctly to the DB.

However, the next time, same error message, nothing added, I tried combinations of all your other suggestions, all to no avail, nothing is added to the DB anymore.

Just in case, I attach the log file. Thank you.

1 attachment(s):
bitseeker's profile image bitseeker posted 2 years ago Permalink

@mrfdes

did you check if your user has global priviliges for FILE?

mrfdes's profile image mrfdes posted 2 years ago Permalink

Forgivemy ignorance, but how or where do Icheckor set that? Thank you.

bitseeker's profile image bitseeker posted 2 years ago Permalink

Open Tools > User Manager see screenshot...

1 attachment(s):
  • screenshot-2021-11-26_082109
mrfdes's profile image mrfdes posted 2 years ago Permalink

Ah, I see. Maybe that is why. When I go to Tools=>User Manager, I get what you see in attachment. How do I solve this?

Thank you.

1 attachment(s):
  • heidierror
ansgar's profile image ansgar posted 2 years ago Permalink

Well you need to ask someone with more privileges to give you that FILE privilege then. Or use a user which has FILE already.

mrfdes's profile image mrfdes posted 2 years ago Permalink

I'm afraid I am the only user.

Please login to leave a reply, or register at first.