Excel 2010: problems with text import wizard

[Dafuq]

I have a problem with the text import wizard while importing semicolon delimited files.
I get standardized files from my suppliers with product data and prices, in form of a semicolon separated text file. The problem is that sometimes excel doesn't recognize the semicolon as a delimiter, if it is preceded by other signs, e.g. diameter "ø" or even a comma ","

This would be a dataset that I can import without problems:

A;N;123456789;00;�example description;1;0;PCE;2345;6789;123; ;

But sometimes the item description contains one of the problematic signs just before the semicolon:

A;N;123456789;00;�example description ø;1;0;PCE;2345;6789;123; ;

In this case, the dataset is not imported correctly and the cells are shifted in relation to the other sets (they are one column shorter)

Is there any way to force excel to always recognize the semicolons as a delimiter, no matter what sign comes before it?

 

[Joe4]

I have Excel 2007 and it works fine for me. How exactly are you trying to open it.

I created a sample text file with the two rows you showed, and imported it. I created this little macro using the Macro Recorder. Try creating a text file with the two rows you posted above, and then try using this macro to import it and see what happens (be sure to update accordingly to reflect your file name).

Sub MyMacro()

    Dim myFile
    
'   Set file name to open
    myFile = "C:\temp\test.txt"

'   Open file
    Workbooks.OpenText Filename:=myFile, Origin:=xlWindows, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=True, OtherChar:=";", FieldInfo:=Array(Array(1, 1 _
        ), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1)), _
        TrailingMinusNumbers:=True
        
End Sub

 

[Dafuq]

This is really strange, I just tried it with only those two and it worked fine as you said... I copied those examples from a file with about 27000 rows, but I changed the values between the semicolons as those datasets contain data that maybe somewhat confidential.

So I thought: maybe I changed something that caused the error? To verify I copy/pasted a range of about 100 consecutive rows out of the file into a new one, and this range contained one of the problematic ones. And guess what: it worked fine this time! I have no idea how this is possible, I guess it must have something to do with the filesize?

But the problematic lines all had one thing in common, there was always some kind of non-letter sign or special letter in the item description, directly before the semicolon: various punctuation marks, signs like "ø", or french and german special letters like "é" or "ä". I must have imported the file at least 20 times now and its always the same lines that produce the error.

I will pm you a dropbox link with the file later, so you can check it out yourself.

 

[Joe4]

Where is this data coming from, and why does it have those special characters in them?
Is there any way to get the data without that junk in it?

If not, one option may be to import the file, importing everything into a single column (Column A).
Then, use Find and Replace to rid your data of all those special characters.
After that, you can use Text to Columns to separate your data into multiple columns (Text to Columns work the same way as Importing Text files).

 

[Dafuq]

Oh my god, I am so incredibly stupid... I noticed only now that for some reason in the wizard the file source was preset to "japanese (Shift JIS)", when I put it to "Windows (ANSI)" it works just fine. How could I not have seen that? I'm totally sorry for wasting your time!

 

[Joe4]

I noticed only now that for some reason in the wizard the file source was preset to "japanese (Shift JIS)", when I put it to "Windows (ANSI)" it works just fine. How could I not have seen that? I'm totally sorry for wasting your time!

No worries! We're just glad it is all sorted out and working for you now.