In the column mapping screen, we could add an option "Constant Value" below the "<empty>" option, and add a "Constant Value" column to the right of the "Position" column. When the user select Constant Value, he can then enter a value in the Constant Value column that will be inserted in the database column. Each database column can have a different constant value. The Constant Value should be disabled and blank for the database columns whose mapping is not "Constant Value".

I have added the screen-shots for illustrating 'Constant Value' for the columns which are not found in the source file.

Steps for CSV:

1. Click on 'Import Data' and select CSV file. Click 'Next' on the first step.
2. On the second step i.e. 'Format' tab. One can see that the 'File Column' drop down in the cell editor now contains the <constant value> option below the <empty> option.
3. On selection of this <constant value> option the 'Position' column changes its value to reflect the position in case it was left void.
4. Update the value for this constant value under "Constant Value" column. This would remain uneditable if this <constant value> option is not selected.

Steps for Excel:

1. Click on 'Import Data' and select an Excel sheet. Click 'Next' on the first step.
2. In "General" format tab, click and select a table from the drop down under the head "Destination Table". Click "Next".
3. In "Format" tab, under the "Source Name" column, select "<constant value>" option. On selecting this option, the first source type value available from the "DataTypeMapper" will be populated in the same row in "Source Type" column. This is because for a corresponding SQL type there could be multiple possible types.
4. In the "Constant Value" column the value could now be filled for this "<constant value>" option.
5. Click "Next".
6. Click "Next" on the "Options Tab" and then Click "Next" on the "Transactions" tab.

Results should be visible. In case, you have selected "Preview statements" in the "Options" tab. Generated statements should contain the corresponding constant values.

PS: The validation is performed only when the PreparedStatements function is invoked inside the ImportThread and not prior to that. In case, required we can replicate this behaviour earlier but it would mean invoking the complete validation functionality twice.

Please refer to the various screenshots I have attached. These illustrate how constant values do look like on both ImportDialog as well as ImportExcelDialog's "Option" tab.

Hi Hussain,

For the excel import, since a constant is related to the source field, it would be incorrect to set the destination drop down to use a constant value when a new table is created. We should keep the logic very simple. Therefore, I think you should eliminate <Constant Value> from the source name field drop down when the table already exists and do not add it to the destination field when a table does not exist. Keep the constant field that you already added and leave it editable. If there is a value in that constant field for that row, use it as input to the table. If there is no value in that column, use the source name column input. If either the source name column or the destination column is <ignore>, clear the constant column and ignore that row(Old logic). You should fix the constant field sizing,

For the csv import, leave the logic the way you have it since the destination fields can never be changed. Leave the <Constant Value> and fix the constant field sizing. 

A couple issues that I noticed with the csv import:

1. The position field increments every time you select <Constant Value>. This should be empty when selecting <Constant Value>.
2. Consider this use case... Select the <Constant Value> in the source field and add a constant value to the field. Now you change your mind and re select a source name field, we should probably clear the constant value from the field.

Thanks, Tom

Hi Tom,

Thanks for the input. I have implemented the changes as suggested by you for both CSV and EXCEL import functionality. Please have a look at them.

Hi Hussain,

For csv, there are four problems:

1. If you type in the constant field and hit the next button, the constant field is never committed so the insert into the table contains a null. When you lose focus on that cell, you should commit the content.
2. As noted above, if any other selections are made in the File Column, such as selecting a column name, the constant field should be cleared.
3. When clicking the next button...If the constant field is empty and File Column equals <Constant Value>, you should popup a warning that the constant field is empty. They could potentially want it to be empty but would have probably selected File Column equals <empty>. Try COptionPane.showMessageDialog to popup the warning message.
4. When you mouse click on the constant field, the color should change to white to show the user that field is editable.

For excel import, the behavior seems to be working ok. One problem:

1. When you mouse click on the constant field, the color should change to white to show the user that field is editable. Checkout how the size, precision and scale fields work when you import to a new table. Let's be consistent with the behavior.

Thanks, Tom

Hi Hussain,

I tested excel import for issue #14042 with Blank Data_xlsx_format2.xlsx and it throws an exception - See screenshot. I think you need to rethink your implementation of getConstantValueForThisColumn as data can be null. Why are you testing for a DOT_ZERO with a numeric?

Thanks, Tom

Hi Tom,

I have fixed all the four problems observed for the CSV import and also applied validation for the null value in the excel import. Please check these and let me know.

Hi Hussain,

For csv, there is a problem generating the insert statements in ImportThread line 770 because the fileColumnPos is null so isVaildQuery is not getting set to true. This returns a null statement. Please add isVaildQuery=true after line 778 like

Hi Hussain,

Issue for Excel Import:

1. When we select <constant value> as source name, then it should not allow to go next with empty constant value. (Validation is required)

Currently it allows to click next even though Constant value column is empty (not filled).

2. Constant value column should be Disabled/Non-editable for "Source Name" column other than <Constant Value>.

Currently it allows to enter values in the Constant Value column for the <ignore> and other source columns we imported.

3. Steps:

• a. Select excel as a source file with 1 column to be imported
• b. Import it into table which contains 3 columns 
• c. In Format tab, select <Constant Value> in "Source Name" column
• d. Enter value as per the datatype in "Constant Value" Column
• e. Change the "Source Name" to <Ignore> of that same row (refer step c.)

Expected: After changing the option to <Ignore>, it should remove the constant value

Current behavior: Constant value remains same even though you have changed the Source name to <Ignore> 

Thanks, Juhi

Hi Juhi,

As discussed in the afternoon. I have implemented the changes we have discussed. Please take a fresh build and test the same.

Hi Hussain, 

When you resolve an issue please add the fixed build in so that QA will know what build to look for. 

Thanks, Tom

Hi Asif,

Following are the commit id's in the ascending order.

Subversion: Committed revision 55985.
Subversion: Committed revision 55999.
Subversion: Committed revision 56003.
Subversion: Committed revision 56021.
Subversion: Committed revision 56045.

Juhi,
Please test it over build #19

Hi Tom, Nhi,

The behavior of this ticket is somewhat weird and its happening only on QA test machines now also. I have tested it on ADS 19.5.0-dev-20.

Current Issue:

Steps: 

1. Connect any of the database

2. Do right click on any selected table and goto Tools-->Import data

3. Import Excel sheet in existing table

4. Click Next button

Expected: It should display the imported source columns into the "Destination table columns mapping" field

Actual:  1. Destination table column mapping field is empty. see here      2. Once I click Next button in the format tab, it display pop up see here

As I have confirmed with Hussain also and this functionality is working fine on his machine. Please let me know the work around it.

Thanks, Juhi

Hi Juhi, 

This seems to be related to the Postgres change 15541 svn 56047. The extract query is invalid and not returning any data to populate the screen. Please have Pankaj investigate this line...

1223 -> sql.append(equalGreater10 ? ",is_identity AS isidentity " : ",NO AS isidentity ");

in ExtractTableUtil class. It's failing because it thinks NO is a field...

Also, please have Hussain or Pankaj add the changes back in for svn 56045. We will do a build after all these changes/fixes are in so that you can retest.

Thanks, Tom

Subversion commit #56074

Due to the lack of position for a constant column we have to directly determine the datatype from the Destinatin Column. If not provided this used to result into a nullPointer exception for Vertica DB where lack of datatype causes it to fail.

Juhi,

This fix would appear in build # 25. Please test the same over there.

Hi Tom,

Hussain has already fixed this issue as mentioned in the previous comment.

FYI:

Platform: Windows 8

Build version: 19.5.0-dev-24

This Functionality is working on most of the databases tested on Amazon Redshift, MySQL, Apache Derby, SQLite etc.

Issue Observed while importing .csv file in the table for Vertica:

Steps:

1. Import .csv file into table

2. Provide constant value for datatype other than int say varchar, text or so.

- Throwing NULL Pointer exception. see attached screenshots

1.  VerticaPreviewPane.png    2. ViewLogForConstantValueInVertica.png

Thanks, Juhi

Hi Juhi,

I gave this a try and it seems to work ok for me. Please supply the exact steps for your failing test case and I will have a look. It looks like Hussain added a fix into 19.5.0-dev-25. Please test with that version.

Thanks, Tom

Hi Tom,

Please follow below steps to reproduce the issue reported yesterday which hussain fixed in latest build.

Build no.: ads-windows-x64-19.5.0-dev-24 (its reproducible)

Steps to reproduce issue on Vertica database:

1. Select Vertica 9.1 database 

 2. Do right-click on any selected table (make sure table contains more than 1 column)

3. Go to tools --> Import data   

4. Select csv file to import  (make sure file contains 1 column) . see here till step 4

5. Click next button on General tab   

6. Select <Constant Value> as a source corresponding to the destination column 2 with some constant value as per the expected datatype (make sure datatype should be other than integer) See here

7. Click next till it execute

Issue: It was throwing NULL pointer exception and preview statement shows nothing. See issue

Hence, its fixed in ads-windows-x64-19.5.0-dev-25 build so marking it verified.

Thanks Juhi for the follow up.