How to stop Excel Reader from treating numbers as dates?

[jbh_CO]

When I use the Excel reader, it is automatically converting anything that looks like an Excel date serial number to a date, even when I change the column to string in the Transformation tab. I have tried changing the format in the Excel file to general, to number, and even adding something like .123 to the number so it looks like a number. How can I get the Excel Reader to treat these as a number (or preferably, a string)?

[takbb]

Hi @jbh_CO. Welcome to the KNIME forum.

Can you upload a simple workflow that demonstrates the problem you are having, including a sample xlsx.

Which version of KNIME are you using?

[mlauber71]

@jbh_CO

or preferably, a string

You can force the import of all columns as strings and convert them later.

Excel Reader node - Transform tab - SET all columns to String

@Felipereis50 I once proposed this solution: Also you could try and force the types later. Using a sample table: Also you could try and use openpyxl to import the file and force strings import pandas as pd # Specify your file path and sheet name file_path = 'your_file.xlsx' sheet = 'YourSheetName' # Replace with your sheet name # Read the Excel file, specifying the sheet and forcing all columns to strings df = pd.read_excel(file_path, sheet_name=sheet, dtype=str, engine='openpyxl') …

[jbh_CO]

After some time away from the problem, I began to think it’s most likely the Excel sheet itself and today I confirmed that by copy/pasting the values only into a new sheet, which works as it should.

Interestingly, I tried clearing the formats of all cells on the original sheet and when I did, all those integer values suddenly became dates (in Excel). So it’s a problem with the sheet itself, except I have no control over how the sheet is built.

Thanks for the Openpyxl solution. I’ve used Openpyxl in the past, but I did not know I could use it in KNIME. I’m going to give it a try.