Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Invalid types in INSERT if not all columns are imported from CSV #327

Closed
nummacway opened this issue Aug 13, 2018 · 9 comments · Fixed by #989
Closed

Invalid types in INSERT if not all columns are imported from CSV #327

nummacway opened this issue Aug 13, 2018 · 9 comments · Fixed by #989
Labels
Milestone

Comments

@nummacway
Copy link
Contributor

The CSV import feature is mostly useless. If the file is missing columns, they must be the last few columns (not in-between or at the start) or the table must be of only one type.

Steps to reproduce this issue

(Minimal example.)

  1. Create a table with two columns. They must have different types and the first column must either be nullable or auto-increment.
  2. Create a CSV file with values for the second column. So basically the file is a list of one value per line, not actually comma-separated.
  3. Import CSV. Make sure to uncheck the first column, as it's not in the file.

Current behavior

Heidi tries to use the first column's type to insert the second column's values as it doesn't care what you checked or not when it determines the column types.

Expected behavior

Heidi must consider unchecked columns when determining what type to import a value as.

Environment

each and any

See also

#242

@fifonik
Copy link

fifonik commented Aug 14, 2018

I'm unable to re-produce the issue (HeidiSQL 9.5.0.5280 + MySQL 5.7.19).

Here are my steps:

  1. Created tables and with data using the script:
DROP TABLE IF EXISTS src;

DROP TABLE IF EXISTS dest;

CREATE TABLE `src` (
	  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
	, `val` VARCHAR(15) NOT NULL
);

CREATE TABLE `dest` (
	  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
	, `val` VARCHAR(15) NOT NULL
);

INSERT INTO src (val) VALUES ('a1'), ('a2'), ('a3'), ('a4'), ('a5');

  1. Refreshed DB

  2. Clicked on 'src' table and selected Data tab

  3. Right mouse button over grid | 'Export grid rows' | Excel CSV, Complete, Include column names. , OK
    export

  4. Checked content of the z.txt file. There is a single val column with values in there
    file

  5. Clicked on 'dest' table and then Tools | Import CSV file. Selected z.txt,, unticked id column and pressed Import! button.
    import

  6. Pressed F5 to refresh 'dest' table and checked that everything as expected.

P.S. I do not have INSERTs in log while doing import. LOAD DATA INFILE there in my case.

@nummacway
Copy link
Contributor Author

This issue is about Heidi parsing the file (client parses file contents), which is your only option on MSSQL. With your settings (LOAD DATA INFILE, or server parses file contents), Heidi does not even convert any types, so no conversion can fail.

@fifonik
Copy link

fifonik commented Aug 14, 2018

I see.
Sorry, MS SQL was not mentioned in original post as any other settings/screenshots that could point in the right direction.

@fifonik
Copy link

fifonik commented Aug 15, 2018

I have tested again with MySQL and "Client parses file content" option selected (everything else as in my previous reply).

HeidiSQL issued the following query on import:
INSERT INTO test.dest (val) VALUES ('a1'), ('a2'), ('a3'), ('a4'), ('a5"\r');

So there is no issue with first auto increment column, however there is an issue with last row's value (notice quote with \r added). I've created a separate ticked for the issue.

@nummacway
Copy link
Contributor Author

Because I'm running into this bug again, I'm gonna post a minimal example:

  1. I run this on my MSSQL server to create a table:
CREATE TABLE [dbo].[Test_HeidiTicket327](
	[Column1] [int] NULL,
	[Column2] [varchar](20) NULL
)
  1. Then I import the following CSV file, but uncheck Column1, because the file contains only string values for Column2:
lorem
ipsum
dolor
sit
amet
  1. Then I get 5 rows with Column1=NULL (which is what I expected) and Column2=0 each. Heidi seems to think that the column I want to import was of type int, because the first column of the table as a whole is int. But the first column I want to import is varchar(20).

nummacway added a commit to nummacway/HeidiSQL that referenced this issue Apr 15, 2020
ansgarbecker pushed a commit that referenced this issue Apr 15, 2020
@ansgarbecker
Copy link
Collaborator

Are you sure that fixes the issue?

@nummacway
Copy link
Contributor Author

Somewhat. Before committing the fix, I built the project and successfully imported a previously unimportable TSV file (which turned some string columns into '0') that contains the columns highlighted in blue in the following design:
grafik

@ansgarbecker
Copy link
Collaborator

Ok, so I guess I can close this.

@ansgarbecker ansgarbecker added this to the v11.1 milestone Apr 16, 2020
@ansgarbecker ansgarbecker linked a pull request Apr 16, 2020 that will close this issue
@nummacway
Copy link
Contributor Author

@ansgarbecker Please close #242, seems to be a duplicate.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants