This isn't really a VBA issue, but it was strange enough that I thought I'd mention it here and ask if anyone else has encountered it.
Our MRP system has text exports that I frequently import into Excel.
I recently attempted to import a data file that has our internal spec #, a description, and some cost information. The description has +/- in it, and when I import it into Excel, that string is somehow being ignored and replaced by an empty string, which results in a character shift.
The data looks like this in notepad:
95-200154 1 5/8x3.94"+/-1/4"NYLN6BLK SLT 09/21/04 0.11793 EA
And like this in Excel:
95-200154 1 5/8x3.94"1/4"NYLN6BLK SLT 9/21/2004 0.11793 EA
Has anyone ever seen this before?
I've done hundreds of reports here, and have never encountered this issue before. I'm wondering if there was a software update or something that caused it (Excel 2003 (11.8211.8202) SP3).
Hi Mill,
I can't reproduce it with Excel 2002. I assume you checked if the problem is dependent on the import settings? Especially the "Text Qualifier" setting?
Colin Legg 06-11-2008, 02:13 AM Hi Mill,
Is this a Tab-delimited text file (just a hunch)?
Colin
Hi Mill,
I can't reproduce it with Excel 2002. I assume you checked if the problem is dependent on the import settings? Especially the "Text Qualifier" setting?
I'm not sure what you mean. I'm just importing the file using the text import wizard.
Hi Mill,
Is this a Tab-delimited text file (just a hunch)?
Colin
No, it's supposed to be fixed width. Everything is good, but when these characters get removed, it changes the width for that field in that row, thus messing up the import wizard.
I'm attaching a small sample of the data file. I've changed the numbers so that I don't reveal anything that my company may not want out there.
Import the file into Excel
Use the Fixed Width Option
Hit Next to go to the second screen
Scroll down to 95-200151 and see how it looks
Open the same file in Notepad, and note that the Description in that field is different when viewed in Excel vs Notepad.
Grrr... for some reason, it won't let me upload the file. Just a sec.
I don't know if it's this site or if its my firewall that's preventing it, but I can't upload the text file.
Hmmmm...
How about this? Copy & paste?
PART................ DESC.......................... ZPUR_EFF_ ZPUR_COST... ZPUR_FREIGHT ZPUR_MISC... ZPUR_TOTAL.. PUR.UOM
95-200148 2001003203M0090 FGUARD X 2120 09/21/04 0.11111 0.11111 0.11111 EA
95-200150 2001003803M0890 F.GUARD X 2120 05/22/08 0.11111 0.11111 0.11111 EA
95-200151 32MM X 1450E 365MM +/-6.4MM 10/25/04 0.11111 0.11111 0.11111 EA
95-200152 2001001903M0140 140MM FLEX 10/25/04 0.11111 0.11111 0.11111 EA
95-200154 1 5/8x3.94"+/-1/4"NYLN6BLK SLT 09/21/04 0.11111 0.11111 0.11111 EA
95-200156 19MM FLEXGUARD x 2120 360MM 09/21/04 0.11111 0.11111 0.11111 EA
95-200157 19MM FLEXGUARDx2120 205MM 09/21/04 0.11111 0.11111 0.11111 EA
95-200158 1553005116M0300 51MM X1450E 05/22/08 0.11111 0.11111 0.11111 EA
95-200159 2001001903M0420 2120 10/25/04 0.11111 0.11111 0.11111 EA
95-200160 2001001903M0255 F. GUARD 2120 05/22/08 0.11111 0.11111 0.11111 EA
95-200162 2001001903M0470 F GRD470mm 09/21/04 0.11111 0.11111 0.11111 EA
95-200163 2001001903M0590 F.GUARD 590mm 05/22/08 0.11111 0.11111 0.11111 EA
95-200164 2001001303M0085 F.GUARD 2120 10/25/04 0.11111 0.11111 0.11111 EA
95-200165 2006001903M0183 F. GUARD 2130 05/22/08 0.11111 0.11111 0.11111 EA
95-200167 2001001903M0340 19mm F. GUARD 05/22/08 0.11111 0.11111 0.11111 EA
95-200168 2001001903M0325 19mm F. GUARD 05/22/08 0.11111 0.11111 0.11111 EA
95-200169 2001001903M0110 F. GUARD 2120 05/22/08 0.11111 0.11111 0.11111 EA
95-200170 1553003216M0122 REFLECT 1450E 05/22/08 0.11111 0.11111 0.11111 EA
95-200171 2001002203M0184 F. GUARD 2120 05/22/08 0.11111 0.11111 0.11111 EA
95-200172 2001001303M0450 F. GUARD 2120 05/22/08 0.11111 0.11111 0.11111 EA
95-200173 2001001903M0490 F. GUARD 2120 01/09/08 0.11111 0.11111 0.11111 EA
95-200174 DO NOT USE - SEE 95-200167 09/21/04 0.11111 0.11111 0.11111 EA
95-200175 2001001903M0282 F. GUARD 2120 05/22/08 0.11111 0.11111 0.11111 EA
95-200176 2001001903M0400 F. GUARD 2120 05/22/08 0.11111 0.11111 0.11111 EA
I was referring to the second screen along the "Delimited" route, because I wasn't clear on whether you were splitting on spaces or on fixed widths. But as it's the latter, my suggestion is moot. :-\
Is the +/- in row 3 affected as well, or just the one in row 5 which you used as your first example?
Colin Legg 06-11-2008, 06:50 AM I think we can safely assume this is an issue with Excel 2003 version. Like Cas, I can do this no problem with your instructions in Excel XP.
So, this is a very long shot because it's not in the listed known issues for this security update, but it could possibly be the cause since it effected other similar "txt filey" things:
http://support.microsoft.com/kb/943985
• When you open a file that uses ANSI encoding and that is in tab-delimited (*.txt) format, the East Asia characters in the file may not be displayed. For example, the East Asia characters can be Korean characters or Chinese characters.
• You save an Excel worksheet in which a cell has multiple lines that are saved as a .txt file. However, when you use Excel 2003 to open the .txt file, the multiple lines that were previously in the cell may be separately displayed in multiple cells.
Note If you want to add an additional line in a cell in Excel, press ALT+ENTER when you type the text in the cell.
• The contents of the following files may be displayed incorrectly when you open the files in Excel 2003: • Tab-delimited (*.txt) files that use ANSI encoding
• Comma-delimited (*.csv) files that use UTF-8 encoding
(Now you see why I had a hunch about tab-delimited txt file).
The thing in it's favour is that it's a recent security update, which might explain why you're only seeing the issue now. Might be worth checking if it's installed on your PC? Edit - If it is installed, then you could try to uninstall it and see if your text import works then?
If you establish that it is the cause, then since this isn't listed in the known issues, I very much doubt that the hotfix (http://support.microsoft.com/kb/952208/) will fix it, so you might have to report this new issue to them....
Sorry I can't be of more help, Mill. I'll post back if I have any other ideas.
Colin
Thanks guys.
And yeah, the row in line 3 is also messed up.
Reading the article Colin linked, it sounds like you might want to try saving the file in a different format. I tried the four kinds Notepad produces (ANSI + three Unicode flavours) and still saw no difference in 2002, though.
Reading the article Colin linked, it sounds like you might want to try saving the file in a different format. I tried the four kinds Notepad produces (ANSI + three Unicode flavours) and still saw no difference in 2002, though.
I could try that - open it in Notepad first and then do a Save As.
I posted this comment on the Microsoft forum but didn't get any replies.
|