I try to read the Excel spreadsheet, which basically reads the date column and compares if it`s a future date. But I get errors like “Reading pane: no legal OleAut date”. The text was successfully updated, but the following errors occurred: Unfortunately, I can`t send an Excel example because it 🙁 Sensitive financial data contain. The problem is also that there are a lot of sheets in each Excel and hundreds of cells in each sheet, and I found a way to determine which cell(s) is causing the error. Thank you for your answer, I found a solution from someone. The problem was with the Excel spreadsheet. One row was Custom and the column type was General, so they conflicted. I get this exception in some Excel files when I run the asDataSet method. After implementation, it was able to save the values in Excel. Is there a way to convert data before 1/1/1900 for the entire table? I have a number of tables with several columns, including dates prior to 1.1.1900. This data comes directly from the ERP database and most of it cannot be modified in the ERP GUI (without manipulating the database). I agree, the error reported by the report server (this is our case) is insufficient. There is nothing but a basic error message, so it is necessary to search for all tables with date columns! Remove special characters from date field values This exception is thrown when an attempt is made to convert a value that is outside the valid Ole Automation date range to/from a .NET DateTime value (DateTime.FromOADate and DateTime.ToOADate methods, which are also implicitly used for COM Interop).
This will be a problem in your data. Look for a date earlier than 1/1/1900. Do you check the actual dates in your spreadsheet? You probably have a date earlier than 1/1/1900 in one of the date fields in the table you want to load. An OADate is represented by a double value whose value is the number of days from midnight on December 30, 1899 (negative values representing earlier dates). In this scenario, you can simply override any value before or on that date, and then specify what you want as output, which I @Zunair totally agree with. If the service data is not acceptable, the desktop application must report/warn/disallow. At the same time, it would be better if it simply accepted valid data. Thank you, it was a date before 1900 but really well hidden. 🙂 As the display format in the database was dd/mm/yy (01/01/0022 displayed as 01/01/22), this deceived me for 01/01/2022. Is there a way to disable this check? Technically, this is valid data. What if a museum wanted to categorize its artifacts by date? Not? Change the empty field values to NULL. See FAQ: Is it possible to assign null values to a field using the field calculator? for more information.
Has anyone had such problems? Any advice on how to solve it? This should be added as a #bug and marked as an error in the PBI desktop, as the PBI service cannot handle it. Instead of doing the whole loop yourself and creating the data table, just do something like: Thanks for the help, I`ll try your solution. Anyway, do you know if there is an easy way to convert an entire sheet into a table of data? The Excel file contains an OADate value = 2958466.0, which means that they cannot be represented as DateTime. You can set convertOADateTime to false so that ExcelDataReader does not attempt to convert OADates to DateTime. Thank you for contributing a response to Stack Overflow! Nice, glad you were finally able to discover and correct it. Appreciate the follow-up to clarify the issue. You`re welcome. I had the same problem and it was due to a strange reason because the “Reading Pane” activity couldn`t really retrieve the cell or would get it in the wrong format instead of just the cell value. It looks like this parameter is ignored for xlsx files.
What does Excel do for such cells? Do you have an example XLSX file I could check out? Loading the PBIX in Power BI Desktop doesn`t help too much because the update completed successfully. By clicking on “Post a reply”, you agree to our terms of use, privacy policy and cookie policy As I am not able to manage what goes into the sheet (the client is the one who sends the workbooks), I tried your Siggestion, but to no avail. This is the code where I set the property to false, but it seems to be ignored (it throws the `anyway):. When running an IFS Business Analytics report, I get the following error. I use a custom SQL source to get project management information. It worked perfectly for almost a year. Another option would be to try printing the cell`s value before doing anything with it to make sure you are returning a correct value. To remove special characters from the field value, double-click the cell, select them, and then press the Delete key on your keyboard. You can also set empty field values to null. See FAQ: Is it possible to assign null values to a field using the field calculator? to know the steps to follow.
Maybe a power query string? Table.ReplaceValue? Does anyone know what that means? To get this in Winforms C# applications: I`m lost on it. Any advice or help is appreciated. docs.microsoft.com/en-us/dax/switch-function-dax I think that to be valid for conversion to OADate, the .NET DateTime value must be strictly greater than 01/01/0100. The fact is that in workboks, there is one sheet that I have to read and another sheet that is garbage. It`s probably 99% that the problem is with one of these trash sheets, but I couldn`t find a way to skip these sheets in the asDataSet method, which could probably fix the problem too. (For example, read only sheets with “READ. THIS”) for (int i = 0; i < drive. FieldCount; I++) { object value = drive. GetValue(i); } What I found was that a column with a large row_id `257381195` was trying to be read by Excel as a date. In the end, I changed the data in this column to a string by preceding the row_id with a single quotation mark. That solved my problem.
I hope that helps. Since it was working and stopping, without any other changes, I`d start with the data first. It can be accepted in the field where you extract it, but when you browse the request, it is checked and rejected. Perhaps, but isn`t the problem in the underlying data rather than the app? For more information, check out our tips for writing good answers. To convert OADate to .NET DateTime value, the double value must be strictly greater than -657435 (= 01/01/0100) and strictly less than 2958466.0 (01/01/10000). No, there is no AsDataTable, but it could easily be added. Instead of AsDataSet, you can use NextResult, Read, and GetValue to read only the cells that you want. do { if (drive. Name==”READ.
CES”) { while (drive. Read()) { datatable = reader.asDatatable() } } } while (reader. NextResult()) This error occurs due to the following known causes: This means that somewhere in the program, an attempt to convert to or from an OLE Automation date is in progress outside the valid range 1-January-4713 BC. A.D. to December 31, 9999 A.D. It may have slipped in because OLE Automation dates are represented as a duplicate. The lesson is therefore to check if the cell types do not match. Could you guide me in the easiest and fastest way to read whole sheets of paper with names? Others are struggling with this problem. I suggest checking out these threads on DotNetNuke and DevShed.