Column mismatch caused by needless TABs

One user sometimes comes to me to solve the excel upload problem at the month-end, the scenario is a typical case:

• Uploaded txt file contains more than 200 columns and may over 5000 lines;
• The TXT file separated by TAB and generated by an external system (NO-SAP);
• Very few cases could get dump ‘CONVT_NO_NUMBER’ / ‘CX_SY_CONVERSION_NO_NUMBER’ when running the customized upload program.

It’s very clear from the dump error, and through debug can find out that one specific column which is a long text with TAB inside the specific cell. Because the program using TAB as a separator then leads to column mismatch.

After I debug and help users to check what’s the wrong column number and cell number two times, I thought maybe I should change the upload program to eliminate the extra TAB. This upload program used by many sites located in different countries, and only 1 site has this issue occasionally. There’ll some impacts like user habit/ file conversion if I change the separator from TAB to another separator or request using a different file extension other than TXT. So not a good idea to change the upload program and can’t change the generated program out of my control.

Finally, it turns out to be how to remove needless TAB at the TXT file (open with Excel).

1.Replace TAB using “0009” by the numeric keypad

Because the tab symbols is not visible inside the excel, this article suggests press and holds the “Alt” key and type “0009” on the numeric keypad to stand for TAB; then using Space to replace it. But it not works for me…

2. Using formula SUBSTITUTE using “CHAR(9)”

The below formula will replace TAB in ASCII which is CHAR(9) with “” (space) at cell A1. And it works very well. Just create a new column to store substituted text and paste it back to the original column will do.

SUBSTITUTE(A1,CHAR(9),””)

3. Using Text-To-Columns separated by TAB

Choose the column which contains the TAB, and goto DATA->Text-To-Columns using fixed delimiter TAB will split 1 column into multiple columns if any cell exists extra TAB. Then the user can know the line number, fix it themselves without any debug, and no bother me anymore for such kind of issue.

I’m not an excel expert, only using basic Excel functions 😛 Any better approach to deal with such issues?