For years I had a semi-automated system for generating BOMs (bills of materials) for electrical designs. Via a script in my CAD program, which runs some external code I wrote long ago, I get a tab-separated values (TSV) file. This process also copies a template XLS file. This file defines formatting for specific columns, which the TSV file can’t contain.
I would open the template XLS file in Excel, then do something like DATA, FROM TEXT FILE, give it the file name, and hit return 5 times to take a bunch of defaults. The data from the TSV file would appear in the spreadsheet, with the formatting from the template file. Some cells contained equations instead of direct data. This would all work as intended.
This procedure worked great for something like the last 15 years, until now. Apparently Microsoft updated Excel on my Win10 laptop in the last few weeks, and everything is now broken. What you get from DATA > FROM TEXT > filename is completely different. All the controls from before are now missing, and now it guesses wrong. I can find no obvious way to enable some “legacy” mode, nor do I see a way to give it more details. It just does what it does, which is different from before, and it breaks my process.
It seems there are two incompatibilities that are really messing things up:
- When the first cell in the first row contains a number, it now automatically adds its own title row. I was using cell A1 as the total number of boards to build, so various quantities were computed from that number. The remaining cells in the first row are my column titles. I’m OK with the first column having a number at top. That should be my business.
With it adding its own title row, all my equations are off by one vertically. What at mess!
- Equation import is broken. When it gets a cell that contains “=” as the first character, it adds a space in front of the “=”, then treats the rest as a string as a result. If I manually edit out the space in the spreadsheet, then I get the intended equation. There are hundreds of such cells, so manually fixing them all is not an option.
How do I get back to the previous operation? I’m no Excel whiz, and really don’t want to be. I want to get on with generating my BOMs.
As an experiment, I manually converted a few lines of the TSV file to a CSV file. Importing that results in the same problems. Just typing the CSV file name brings up Excel with the data loaded correctly, but of course it doesn’t know about my column formatting that way.
It shouldn’t be hard to find the program that generates the TSV file under a large layer of dust someplace, and make changes as necessary. For example, if I absolutely need a CSV file now, I can live with that. I think I created a TSV file long ago to get around special characters in text strings in some of the cells. Again, this all worked for years until a few weeks ago.