How to auto FK when import from excel

[tomkris]

Hi,

how to fill FK in detail when import data from xls?
list not show the import data because FK is NULL, i don't want to add FK in xls it is possible?

Thanks.

[MichaelG]

You can use the Row_Import server event to set up the foreign key.

[tomkris]

i've try but not work

Here is my table

T_Header

IDH (PK)
FreeText

-T_Detail

IDD (PK)
IDH (FK)
Timestamp
Order
Measure

Excel File Header only 3 column without IDH from T_Header

Timestamp | Order | Measure

I try to use Row_Import

public bool Row_Import(Dictionary<string, object> row, int cnt) {
//Log(cnt); // Import record count
//Log(row); // Import row
//return false; // Return false to skip import
row["IDH"] = ExecuteScalar("SELECT IDH FROM T_Header WHERE IDH ='" + AdjustSql(row["IDH"]) + "'");
}

What i want in T_Detail auto fill IDH

IDD | IDH | Timestamp | Order | Measure

[MichaelG]

row["IDH"] = ExecuteScalar("SELECT IDH FROM T_Header WHERE IDH ='" + AdjustSql(row["IDH"]) + "'");

The code does not do anything. If row["IDH"] exists, you are just getting the same value. If it does not exist, you does not get anything.

If you need to retrieve the FK, you need to have something in the row data which can relate to the FK.

[tomkris]

MichaelG wrote:

row["IDH"] = ExecuteScalar("SELECT IDH FROM T_Header WHERE IDH
='" + AdjustSql(row["IDH"]) + "'");

The code does not do anything. If row["IDH"] exists, you are just getting
the same value. If it does not exist, you does not get anything.

If you need to retrieve the FK, you need to have something in the row data which can
relate to the FK.

Ok Thank you, i develop another app to modified excel file.

is it possible to upload only created table on database?

Example :
In excel i have 5 column
Col1 | Col2 | Col3 | Col4 | Col5

In database i have only 3
Col1 | Col4 | Col5

[MichaelG]

Use the Page_Importing server event to set up the headers and skip the first header record.

[tomkris]

MichaelG wrote:

Use the
Page_Importing
server event to set up the headers and skip the first header record.

Thank you for ur help.