🎉Community Raffle - Win $25

An exclusive raffle opportunity for active members like you! Complete your profile, answer questions and get your first accepted badge to enter the raffle.
Join and Win

Why is

User: "SkyTrader"
New Altair Community Member
Updated by Jocelyn
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

Sort by:
1 - 10 of 101
    User: "SkyTrader"
    New Altair Community Member
    OP
    My comment above got posted accidentally without finihsing it. I've added the questions I have on an image of the failed Excel import.the faile
    User: "jacobcybulski"
    New Altair Community Member
    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.
    User: "SkyTrader"
    New Altair Community Member
    OP
    Updated by 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?
    User: "jacobcybulski"
    New Altair Community Member
    Accepted Answer
    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.
    User: "jacobcybulski"
    New Altair Community Member
    It is best to format your dates as proper date strings before exporting from Excel.
    User: "SkyTrader"
    New Altair Community Member
    OP
    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.

    User: "jacobcybulski"
    New Altair Community Member
    Great, well done!
    User: "SkyTrader"
    New Altair Community Member
    OP
    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,
    User: "jacobcybulski"
    New Altair Community Member
    "?" 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.
    User: "SkyTrader"
    New Altair Community Member
    OP
    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.