How to fix Excel import and compare error?

I'm comparing two Excel files and want to write matches to new file using some filters.

I'm comparing two Excel files and if there is matching in Make, Model, Modification, Horse Power and also from "WheelSizeFullDB_new" year is in range of year start and year stop of "sql-autobaza", then I want to create a new file and add to it all column of "sql-autobaza" and also 2 last column from "WheelSizeFullDB_new" Tire Size Front and Tire Size Back

dowoload files:

sql_base : drive.google.com/open?id=1Dk_1q9n5RgKFRawT7qBwyMY4ldGUL0fb

sab_base : drive.google.com/file/d/1AewxBR9p0Tgxi2i-iXS_9RDCd90hsA4G

import pandas as pd
import re

sab_base = pd.read_excel('C:\\Users\\x\\Desktop\\Reziko\\Programming\\Visual Studio 2019\\WheelSizeFullDB_new.xlsx')
sql_base = pd.read_excel('C:\\Users\\x\\Desktop\\Reziko\\Programming\\Visual Studio 2019\\sql-autobaza.xlsx')

sqlbase = sql_base.loc[
                        (sql_base['Make'].str.contains('%s[a-z]*'%sab_base['Make'], flags=re.I, regex=True)) &
                        (sql_base['Model'].str.contains('%s[a-z]*'%sab_base['Model'], flags=re.I, regex=True)) &
                        (sql_base['Modification'].str.contains('%s[a-z]*'%sab_base['Modification'], flags=re.I, regex=True)) &
                        (sql_base['Horse Power'].str.contains('%s[a-z]*'%sab_base['Horse Power'], flags=re.I, regex=True)) &
                        (sql_base['Year Start'] < sab_base['Year']) &
                        (sql_base['Year Stop'] > sab_base['Year'])
                        ]

print(sqlbase)

sqlbase.to_excel('sab_base_update.xlsx', index=False)

I expect to create new file and add to it all column of "sql-autobaza" and also two last columns from "WheelSizeFullDB_new" Tire Size Front and Tire Size Back but my code does not work

1 answer

  • answered 2019-04-21 10:20 Shakeela Shaheen

    The best method is to press Ctrl + F (known as the find function) and then select the tab that says Replace. Type “#REF!” in the Find field and leave the Replace field empty, then press Replace All. This will remove any #REF Excelerrors from formulas and thus fix the problem.