Excel Text-File Export Issues
Transforming Excel Worksheet data into a tab-separated text file so that it can be loaded in MySQL has issues. There were to types of warnings in MySQL after the LOAD DATA INFILE statement was executed:
- Warnings about “more data than there were input columns.”
- Warnings about data truncation.The first issue was related to the way Microsoft Excel exports text files: extra, trailing delimiters are added to certain lines for reasons unknown to me. I was able to verify this by inserting dummy data in a few of the trailers and re-importing the text file. This issue can be ignored in such as case.
The second issue is a little tricky. First I had to verify that valid truncation was not taking place. This meant inserting a column in the offending Worksheet and filling down LEN(…). None of the lengths exceeded the limit. Then I began to examine the hidden characters of the offending lines with Visual Studio 2005 and Office VBA. There is this little trick from my Access 97 days: paste a character into this line for the VBA Immediate Window: ?ASC(' '). When 160 came back this meant that non-breaking space characters were causing the truncation. I know that the keyboard shortcut in Office (especially InfoPath) for non-breaking space characters is Ctrl + Shift+ Space. I tried this in the Excel Find/Change and received results that were not helpful and would take too long to explain here. So, back in Visual Studio 2005, my find change operation worked perfectly with Regular Expressions turned on and the hex version of the non-breaking space character in the Find box: \u00a0.