Update: Well, this helps but doesn’t completely solve the problem. Tab characters still stay in the fields not converted to numbers or dates. If this doesn’t work, I’ve added a couple of steps that will work involving using a robust text editor, Notepad++.
A recent Rappatoni MLS update broke data export. It appears that a hidden tab character is placed between fields as a delimiter, changing numeric fields into character fields. This causes issues when you try to use a workbook like Don Machholz’s 1004MC because the 1004MC workbook relies on the data types to be correct. The calculator chokes whenever it finds text in a numeric field like list price (or sale price, or DOM, etc.). This only affects Rappatoni system MLSes. Metrolist was not affected (yet) because they run their own code and do not appear to have run the update that broke stuff.
Here’s a quick way to know if your MLS has been affected. Take a look at the Selling Price column. If it lines up on the left, your MLS has the issue. If it lines up on the right, you’re good to go.
Here’s an example with the problem:
Here’s how it should look:
Here’s an easy workaround for the latest version of Excel.
- Download your data out of MLS and save it to a csv file.
- Open up an Excel workbook.
- Click on the cell you want the downloaded data to appear in.
- Click on the Data Tab and select From Text/CSV
- Select your csv file containing the data you need and hit the Import button
- Excel will open a data import wizard. This should show your data with the correct data formats and aligned correctly (numbers aligned to the right, text to the left, dates looking like dates). Click the Transform Data button and you should be good to go.
Older versions may require more work to find the data import wizard. This link shows the process using a slightly older version of Excel. Brings me back to my days as an EDI analyst….
I’m learning from others that this fix doesn’t work universally. If you see this in your Excel file, you still have problems.
Here are additional steps that you can do to fix it. The simple way is to place your cursor in the edit bar to the immediate left of the data you want to keep, hit <Backspace>, then enter. This deletes the hidden Tab character but needs to be repeated throughout the file. Another option is to simply not copy text fields into the 1004MC since they are not required to run the calculator.
If you’re interested in a better solution, download a robust text editor like Notepad++ (link). Use Notepad++ to open the csv file.
Go to View, Show Symbol, Show All Characters
Notepad++ will then show the Tab characters
Select one of the now visible tab characters and type ctl + c to copy the Tab character. In Notepad++, select Search then Replace
Click on the Find what: box and paste the Tab you selected previously. Leave the Replace with: box blank. Click on the Replace All button and then save changes.
Now you should be good to go.
(There are many ways to skin a cat. Another way would be to import your files into an Access database, delete the tabs, then output as an Excel file. If you know how to do this, you don’t need my instructions)
Good luck and contact me directly at firstname.lastname@example.org with questions. Hopefully Rappatoni fixes this soon.
Thank you, Joe!
I struggled with my Excel sheet all weekend wondering why Excel was broken on my computer. And it turns out to be caused by a download glitch.
You are a gem of a human being.
You know you’re the reason why I put this together, right?
Awesome Joe, thank you so much!