#14923: Import data from Excel file, provide default value for blank cells to non-nullable columnns

lcotton reported 2017-01-12T14:44:32Z · last modified 2017-01-17T16:15:03Z

Import data from Excel file, provide default value for blank cells to non-nullable columnns

Dev	tomconrad
QA	SachinPrakash

Priority	Low
Complexity	Unknown

Component	Tools - Excel Import
Version	17.0

When I import from Excel 2007 file into an existing table, I often have non-nullable bit-type columns in the existing table, but haven't provided a column for that column.

In the Destination Table Column Mapping block, I can choose to select no import-source column to map to the non-nullable column, but ADS by default attempts to assign (null) to the import, which fails as the column is non-nullable.

Is there a way to specify a default value to insert during import if the incoming column is blank? Could this be added?

My workaround is to to the source Excel file and add columns with all FALSE or TRUE values so that I can have something to map.

The non-nullable column does have a default value (0) defined in the table definition, BTW, but this is not used in the import function. Even just honoring that would be helpful.

10 attachments

OracleRecreate.sql

2017-01-12T20:59:05Z

1 KB
SQLServerRecreate.sql

2017-01-12T20:59:05Z

2 KB
Cricket.xlsx

2017-01-12T20:59:05Z

5 KB
DB2Recreate.sql

2017-01-12T20:59:05Z

2 KB
InsertedData.png

2017-01-12T21:54:05Z

77 KB
ExcelImportNullError.png

2017-01-12T21:54:05Z

108 KB
DataAfterInsert.png

2017-01-12T21:54:05Z

26 KB
CreateDDL.png

2017-01-12T21:54:06Z

104 KB
ExcelImportSecondScreen.png

2017-01-12T21:54:06Z

111 KB
SybaseASERecreate.sql

2017-01-12T22:23:19Z

2 KB

All Comments (5) Change History

SachinPrakash 2017-01-12T15:29:20Z

The non-nullable column does have a default value (0) defined in the table definition, BTW, but this is not used in the import function. Even just honoring that would be helpful.

@tom: pls look into this aspect first.

tomconrad 2017-01-12T23:19:39Z · (edited)

We can recreate the issue and have a fix.

To recreate the issue, use the sample attached ddl like SQLServerRecreate.sql to

create the tables. See CreateDDL screenshot.

Select table Cricket_data from the schema tree and use the attached

Cricket.xlsx to launch Import Excel. See InsertedData screenshot.

Inside of Import Excel, notice the Playing column is ignored

in the second screen. See ExcelImportSecondScreen screenshot. Importing into

the table creates null errors since the Playing column is not null and Import

Excel is attempting to insert nulls. See ExcelImportNullError screenshot.

The fix is to eliminate the columns in the insert statement that have a source

which is ignored when a table already exists. The final output after applying the

fix is shown in the DataAfterInsert screenshot. Notice the Playing column now

has the default value and data is inserted without error.

tomconrad 2017-01-13T23:38:49Z · (edited)

svn #52669

svn #52688 - ADS 17.0.11-3.

Hi QA Team,

I have added the fix to V18 initially and will retrofit it to V17 after testing is complete.

Please regression test Import Excel. Additionally, across all supported databases, test that

default column values are being used on non-null columns when the source column is ignored.

I created a small test case(attached) with some sample ddl and data. This tests different

aspects and combinations of ignored source columns such as embedded, default or no default,

null or not null. I tested this with SQLServer, DB2, Oracle and SyebaseASE. Please use

this as a base to do your testing.

Thanks in advance,

Tom

SachinPrakash 2017-01-16T21:28:00Z

Hi Lance,

The non-nullable column does have a default value (0) defined in the table definition, BTW, but this is not used in the import function. Even just honoring that would be helpful.

Below is a patch which fixes the above reported issue. Let us know how this works for you.

Patch : http://www.aquafold.com/download/v17.0.0/ads-17.0.11-3-patch.zip

Update Instructions : http://www.aquafold.com/support-update.html#v17

I've logged a new enhancement for a future release that would provide the ability to specify a constant value for a given destination column while importing data from Excel file. ADS #14932

lcotton 2017-01-17T15:58:12Z

I have confirmed that the patched version does not exhibit the problem (it is fixed.)

Thank you.

Search Tips

Aqua Data Studio / nhilam

Import data from Excel file, provide default value for blank cells to non-nullable columnns

10 attachments

Issue #14923

Completion

Search Tips

Aqua Data Studio / nhilam

Title

Import data from Excel file, provide default value for blank cells to non-nullable columnns

10 attachments

Issue #14923

Completion