Altair RISE

A program to recognize and reward our most engaged community members

Nominate Yourself Now!

Why is

Hi, I've looked for a similar issue in search but can't see why the"Ignore Errors" button isn't working on an Excel file Import?

Find more posts tagged with

AI Studio

Errors

Excel

Accepted answers

jacobcybulski

Make sure that your first row are column names (it is also possible in one of the steps to define the first row as comment and then the second as column titles). Because your first row were numbers, the import wizard assumed that you have no column names and treated them as nominal values. This resulted in chaos later on. So delete the first row of numbers and make the column names as row one. Also some of your column names may be illegal in RM, e. g. remove special characters from column names, ideally use only characters and underscores. After those changes the CSV will read in.

All comments

SkyTrader

My comment above got posted accidentally without finihsing it. I've added the questions I have on an image of the failed Excel import.

Image: https://us.v-cdn.net/6030995/uploads/editor/0t/kbp69oo0dfof.png

the faile

jacobcybulski

Ignore Errors is designed for "little" errors, such as encountering a nominal value when numerics are expected or when the date is not formatted the way it is expected. When a "big" error is found, such as the corrupted Excel file or the file format in a very new Excel version, the reader gives up and crashes. I have always had better luck in reading CSV or JSON files, or even SAS files than Excel files. I suggest to save your Excel file into CSV and use a CSV reader.

SkyTrader

Thanks Jacob, well I got slightly further with a csv! But RM still thinks the variables in the first row are errors. They are variables periods for technical financial indicators like the RSI, as explained in the image above.

Oddly RM doesn't even pull in Row 1 (SMA and EMA etc variables like 20 day or 50 day EMAverage) on this import, yet still "calls them out" as being errors?

Would it be better if I just deleted Row 1 and its variables?

jacobcybulski

It is best to format your dates as proper date strings before exporting from Excel.

SkyTrader

Success. Thanks very much. My first large data set imported into RM.

I'd already decided to delete Row 1 and rename anything that looked similar.

"format your dates as proper date strings"
I think this is currently the best format dd/mm/yyyy.... but then I found about a years worth of dates with a different format so that was converted.

Then it showed I had commas on the Open data (Dow Jones), so I deleted those, then it found more in another column and another...
so eventually I selected every relevant column and formatted the whole workbook and then it imported fine.

Cheers again Jacob.

jacobcybulski

Great, well done!

SkyTrader

Ps/ On a column that doesn't calculate the first 19 cells like a 20 day moving average does it matter that RM depicts those empty column cells as a "?" Cheers,

jacobcybulski

"?" indicates missing values, if in the results tab you go to Statistics, you will see how many missing values you have in each column. You can then decide what to do with those columns / attributes, you can either remove such columns (using Select Attributes) or you can "impute" those missing values (e.g. with column average using Replace Missing Values). I suspect that in your case you'd want the column to calculate the value, in which case you probably want to delete that column and add a new column using a Generate Attribute which would have a calculation inside.

SkyTrader

Thanks, those kind of missing values are to be expected in Excel or RM because a 20 day moving average won't give an values until Row 20. I just wasn't sure how RM would treat them.