Import Text Wizard issues

[rxp409]

Hi
I have a text file containing data which shows correctly in a web browser, looks like garbage in notepad, then when i import it through the text wizard using space as the delimiter it shows correctly except that some of the rows are shifted across by one column and others aren't. For example

[TABLE="width: 660"]
<tbody>[TR]
[TD]STRIKE[/TD]
[TD]CALL[/TD]
[TD]VTY[/TD]
[TD]DELTA[/TD]
[TD]GAMMA[/TD]
[TD]THETA[/TD]
[TD]VEGA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]22.01[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]19.01[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]85.5[/TD]
[TD="align: right"]18.51[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]97.5[/TD]
[TD="align: right"]6.53[/TD]
[TD="align: right"]12.5[/TD]
[TD="align: right"]0.98[/TD]
[TD="align: right"]1.61[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]6.03[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0.99[/TD]
[TD="align: right"]1.07[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]98.5[/TD]
[TD="align: right"]5.54[/TD]
[TD="align: right"]11.3[/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]2.3[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]5.05[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]2.41[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]99.5[/TD]
[TD="align: right"]4.57[/TD]
[TD="align: right"]10.6[/TD]
[TD="align: right"]0.95[/TD]
[TD="align: right"]3.79[/TD]
[TD="align: right"]0.01[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]4.08[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0.94[/TD]
[TD="align: right"]4.65[/TD]
[TD="align: right"]0.01[/TD]
[/TR]
[TR]
[TD="align: right"]100.5[/TD]
[TD="align: right"]3.61[/TD]
[TD="align: right"]9.4[/TD]
[TD="align: right"]0.92[/TD]
[TD="align: right"]5.77[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]0.04[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]3.14[/TD]
[TD="align: right"]9.1[/TD]
[TD="align: right"]0.89[/TD]
[TD="align: right"]7.4[/TD]
[TD="align: right"]0.01[/TD]
[/TR]
[TR]
[TD="align: right"]101.5[/TD]
[TD="align: right"]2.69[/TD]
[TD="align: right"]8.8[/TD]
[TD="align: right"]0.86[/TD]
[TD="align: right"]9.37[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]0.06[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]2.26[/TD]
[TD="align: right"]8.8[/TD]
[TD="align: right"]0.81[/TD]
[TD="align: right"]11.54[/TD]
[TD="align: right"]0.01[/TD]
[/TR]
[TR]
[TD="align: right"]102.5[/TD]
[TD="align: right"]1.85[/TD]
[TD="align: right"]8.4[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]13.85[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]0.09[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]1.47[/TD]
[TD="align: right"]8.1[/TD]
[TD="align: right"]0.68[/TD]
[TD="align: right"]16.16[/TD]
[TD="align: right"]0.02[/TD]
[/TR]
[TR]
[TD="align: right"]103.5[/TD]
[TD="align: right"]1.13[/TD]
[TD="align: right"]7.8[/TD]
[TD="align: right"]0.6[/TD]
[TD="align: right"]18.18[/TD]
[TD="align: right"]0.02[/TD]
[TD="align: right"]0.11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]0.83[/TD]
[TD="align: right"]7.7[/TD]
[TD="align: right"]0.51[/TD]
[TD="align: right"]19.14[/TD]
[TD="align: right"]0.02[/TD]
[/TR]
[TR]
[TD="align: right"]104.5[/TD]
[TD="align: right"]0.59[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]0.41[/TD]
[TD="align: right"]19.03[/TD]
[TD="align: right"]0.02[/TD]
[TD="align: right"]0.11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]7.3[/TD]
[TD="align: right"]0.31[/TD]
[TD="align: right"]17.76[/TD]
[TD="align: right"]0.01[/TD]
[/TR]
[TR]
[TD="align: right"]105.5[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]7.1[/TD]
[TD="align: right"]0.23[/TD]
[TD="align: right"]15.5[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]0.08[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]0.16[/TD]
[TD="align: right"]7.1[/TD]
[TD="align: right"]0.16[/TD]
[TD="align: right"]12.39[/TD]
[TD="align: right"]0.01[/TD]
[/TR]
[TR]
[TD="align: right"]106.5[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]7.2[/TD]
[TD="align: right"]0.11[/TD]
[TD="align: right"]9.38[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]0.06[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]0.06[/TD]
[TD="align: right"]7.3[/TD]
[TD="align: right"]0.07[/TD]
[TD="align: right"]6.74[/TD]
[TD="align: right"]0.01[/TD]
[/TR]
[TR]
[TD="align: right"]107.5[/TD]
[TD="align: right"]0.04[/TD]
[TD="align: right"]7.3[/TD]
[TD="align: right"]0.04[/TD]
[TD="align: right"]4.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.03[/TD]
[/TR]
</tbody>[/TABLE]

So from strikes 82 to 100 all of the data has been shifted right one column, which is ok i can deal with that. But from then on each alternate column is out of line. This happens throughout the file across hundreds of sets of data. What should i be looking for? I've tried changing the file origin to ANSI and 1252 etc but no change.

NB: in the pasted sample above i've not included the far left column, but obviously that is correspondingly out of line too, there is no missing data.

 

[Firefly2012]

Hi

In the Text import wizard when specifying a delimiter, you should have the option available to "Treat consecutive delimiters as one" - if you check this does it help?

 

[rxp409]

Hi
Yes that is checked by default, without it checked it is all over the place.

 

[rxp409]

From looking at the rest of the data it seems all of the strikes under 100 and those using decimals are shifted to the right. For example in the above sample anything above 100 and decimal is in the right place, everything else is shifted right. This is the case in the rest of the data. And where there is no decimal, for example where strikes range from 1300 - 2175 in increments of 5, it is all shifted to the right.

I can spot this pattern but i have no idea for the rationale behind why this may be.

 

[Firefly2012]

Have you tried using Fixed Width and applying the delimiting columns yourself? This may well not improve matters but could be worth a shot

 

[Andrew Poulsom]

What is the character before the strike value when the data is shifted right? You can use the CODE function to check. It might be character 160, in which case you need to specify that as Other in the Text Import Wizard. To do that hold down Alt and type 0160 on the numeric keypad.

 

[Firefly2012]

Also, have you tried using other delimiters (typically Tab)? Do you have any control over the output format of the text file? Where is it sourced from?

 

[rxp409]

Cheers, i tried the code function, but the cell before the shifted-right-strikes are just blank so it just returns #VALUE. The rest are 49.

On the other delimiters, tab just puts it all in one column. I would like to share the text file, so posted it here: fut_20121112.txt - 4shared.com - document sharing - download

its about 3.5MB Would be really grateful for any help. If you open it in a browser to start with that shows how it should be laid out. I don't have any control over how its generated.

 

[Firefly2012]

I can't donwload anything at work but will try to look at it tonight.

 

[Andrew Poulsom]

It seems that the Text Import Wizard doesn't take account of leading spaces. One solution is to open the file in a single column, use the TRIM function to remove the leading spaces, convert to values and the use Text To Columns.