HTML tag used for links in table columns present in exported csv/excel file

[ambarishp]

Currently we are using 4.0.1 superset version. One of the table chart widget , i m using image tag , to show the status. When we download the table data as excel/cvs, the html tags shown in the exported file. Is there any option to remove that specific column which is having html tags, so that it will not shown up in the exported file.

widget

excel

[dosubot[bot]]

Hey there, @ambarishp! I'm here to help you with any issues or questions you may have. Let's tackle this together!

Yes, there is an option to remove a specific column containing HTML tags from being included in the exported Excel or CSV file in Apache Superset version 4.0.1. You can achieve this by filtering out the column in the useTableColumns function.

Here is the relevant part of the code where you can filter out the column:

export const useTableColumns = (
  colnames?: string[],
  coltypes?: GenericDataType[],
  data?: Record<string, any>[],
  datasourceId?: string,
  isVisible?: boolean,
  moreConfigs?: { [key: string]: Partial<Column> },
  allowHTML?: boolean,
) => {
  const [originalFormattedTimeColumns, setOriginalFormattedTimeColumns] =
    useState<string[]>(getTimeColumns(datasourceId));

  const onTimeColumnChange = (
    columnName: string,
    columnType: FormatPickerValue,
  ) => {
    if (!datasourceId) {
      return;
    }
    if (
      columnType === FormatPickerValue.Original &&
      !originalFormattedTimeColumns.includes(columnName)
    ) {
      const cols = getTimeColumns(datasourceId);
      cols.push(columnName);
      setTimeColumns(datasourceId, cols);
      setOriginalFormattedTimeColumns(cols);
    } else if (
      columnType === FormatPickerValue.Formatted &&
      originalFormattedTimeColumns.includes(columnName)
    ) {
      const cols = getTimeColumns(datasourceId);
      cols.splice(cols.indexOf(columnName), 1);
      setTimeColumns(datasourceId, cols);
      setOriginalFormattedTimeColumns(cols);
    }
  };

  useEffect(() => {
    if (isVisible) {
      setOriginalFormattedTimeColumns(getTimeColumns(datasourceId));
    }
  }, [datasourceId, isVisible]);

  return useMemo(
    () =>
      colnames && data?.length
        ? colnames
            .filter((column: string) => Object.keys(data[0]).includes(column))
            .filter((column: string) => column !== 'column_with_html_tags') // Add this line to filter out the specific column
            .map((key, index) => {
              const colType = coltypes?.[index];
              const firstValue = data[0][key];
              const originalFormattedTimeColumnIndex =
                colType === GenericDataType.Temporal
                  ? originalFormattedTimeColumns.indexOf(key)
                  : -1;
              const isOriginalTimeColumn =
                originalFormattedTimeColumns.includes(key);
              return {
                // react-table requires a non-empty id, therefore we introduce a fallback value in case the key is empty
                id: key || index,
                accessor: row => row[key],
                Header:
                  colType === GenericDataType.Temporal &&
                  typeof firstValue !== 'string' ? (
                    <DataTableTemporalHeaderCell
                      columnName={key}
                      datasourceId={datasourceId}
                      onTimeColumnChange={onTimeColumnChange}
                      isOriginalTimeColumn={isOriginalTimeColumn}
                    />
                  ) : (
                    key
                  ),
                Cell: ({ value }) => {
                  if (value === true) {
                    return BOOL_TRUE_DISPLAY;
                  }
                  if (value === false) {
                    return BOOL_FALSE_DISPLAY;
                  }
                  if (value === null) {
                    return <CellNull>{NULL_DISPLAY}</CellNull>;
                  }
                  if (
                    colType === GenericDataType.Temporal &&
                    originalFormattedTimeColumnIndex === -1 &&
                    typeof value === 'number'
                  ) {
                    return timeFormatter(value);
                  }
                  if (typeof value === 'string' && allowHTML) {
                    return safeHtmlSpan(value);
                  }
                  return String(value);
                },
                ...moreConfigs?.[key],
              } as Column;
            })
        : [],
    [
      colnames,
      data,
      coltypes,
      datasourceId,
      moreConfigs,
      originalFormattedTimeColumns,
    ],
  );
};

In the above code, replace 'column_with_html_tags' with the actual name of the column you want to exclude ^[1].

To continue talking to Dosu, mention @dosu.

[ambarishp]

thanks @dosu . Let me try this

[dosubot[bot]]

You're welcome! If you encounter any issues or need further assistance, feel free to ask. Good luck with your implementation!

To continue talking to Dosu, mention @dosu.

[ambarishp]

@dosu this is filtering the html column from the view as table modal in UI, but its not filtering from exported excel/csv

[dosubot[bot]]

To ensure that the HTML column is excluded from the exported Excel or CSV files, you need to modify the export logic in the backend. Specifically, you can customize the SqlResultExportCommand class in the superset/commands/sql_lab/export.py file.

Here is an example of how you can modify the run method to exclude the HTML column:

def run(
    self,
) -> SqlExportResult:
    self.validate()
    blob = None
    if results_backend and self._query.results_key:
        logger.info(
            "Fetching CSV from results backend [%s]", self._query.results_key
        )
        blob = results_backend.get(self._query.results_key)
    if blob:
        logger.info("Decompressing")
        payload = utils.zlib_decompress(
            blob, decode=not results_backend_use_msgpack
        )
        obj = _deserialize_results_payload(
            payload, self._query, cast(bool, results_backend_use_msgpack)
        )
        df = pd.DataFrame(
            data=obj["data"],
            dtype=object,
            columns=[c["name"] for c in obj["columns"]],
        )
    else:
        logger.info("Running a query to turn into CSV")
        if self._query.select_sql:
            sql = self._query.select_sql
            limit = None
        else:
            sql = self._query.executed_sql
            limit = ParsedQuery(
                sql,
                engine=self._query.database.db_engine_spec.engine,
            ).limit
        if limit is not None and self._query.limiting_factor in {
            LimitingFactor.QUERY,
            LimitingFactor.DROPDOWN,
            LimitingFactor.QUERY_AND_DROPDOWN,
        }:
            # remove extra row from `increased_limit`
            limit -= 1
        df = self._query.database.get_df(
            sql,
            self._query.catalog,
            self._query.schema,
        )[:limit]

    # Exclude the HTML column
    if 'HTML' in df.columns:
        df = df.drop(columns=['HTML'])

    csv_data = csv.df_to_escaped_csv(df, index=False, **config["CSV_EXPORT"])

    return {
        "query": self._query,
        "count": len(df.index),
        "data": csv_data,
    }

This modification ensures that the HTML column is excluded from the DataFrame before it is converted to CSV or Excel format ^[1].

To continue talking to Dosu, mention @dosu.

[ambarishp]

I m facing another issue with downloaded csv showing long number in exponential notation. Is there fix can apply on this file.

[dosubot[bot]]

To ensure that large numerical values in downloaded CSV files are displayed in their full numeric form instead of scientific notation, you can adjust the number format settings in the control panel configuration. Specifically, you should configure the y_axis_format to display numbers in a full numeric form.

Here is an example of how you can set the y_axis_format to a specific number format:

controlOverrides: {
  y_axis_format: {
    label: t('Number Format'),
    default: '.2f', // Example format to display numbers with two decimal places
  },
},

In this example, the format '.2f' ensures that numbers are displayed with two decimal places. You can adjust the format string according to your needs to avoid scientific notation. For instance, you can use '.0f' to display integers without any decimal places.

Make sure to apply this configuration in the appropriate section of your control panel configuration ^[1].

To continue talking to Dosu, mention @dosu.

[ambarishp]

@dosu this is fixing the format in UI, but the downloaded file still showing scientific notation.

[dosubot[bot]]

To ensure that long numbers in the downloaded CSV file are displayed in their full numeric form instead of scientific notation, you can modify the df_to_escaped_csv function to convert long numbers to strings before exporting. This approach ensures that the numbers are treated as text in the CSV file, avoiding scientific notation.

Here is an example of how you can modify the df_to_escaped_csv function:

import pandas as pd
import numpy as np

def df_to_escaped_csv(df: pd.DataFrame, **kwargs: Any) -> Any:
    def escape_values(v: Any) -> Union[str, Any]:
        return escape_value(v) if isinstance(v, str) else v

    # Escape csv headers
    df = df.rename(columns=escape_values)

    # Escape csv values and convert long numbers to strings
    for name, column in df.items():
        if column.dtype == np.dtype(object):
            for idx, value in enumerate(column.values):
                if isinstance(value, str):
                    df.at[idx, name] = escape_value(value)
        elif column.dtype == np.int64 or column.dtype == np.float64:
            df[name] = df[name].apply(lambda x: f'{x:.0f}' if x > 1e15 else x)

    return df.to_csv(escapechar="\\", **kwargs)

This modification ensures that long numbers are converted to strings, preventing them from being displayed in scientific notation in the exported CSV file ^[1].

To continue talking to Dosu, mention @dosu.