Import Text Wizard issues

rxp409

New Member
Joined
Apr 27, 2010
Messages
17
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

STRIKECALLVTYDELTAGAMMATHETAVEGA
8222.010100
8519.010100
85.518.510100
97.56.5312.50.981.610
986.03100.991.070
98.55.5411.30.972.30
995.05100.972.410
99.54.5710.60.953.790.01
1004.08100.944.650.01
100.53.619.40.925.770.010.04
1013.149.10.897.40.01
101.52.698.80.869.370.010.06
1022.268.80.8111.540.01
102.51.858.40.7513.850.010.09
1031.478.10.6816.160.02
103.51.137.80.618.180.020.11
1040.837.70.5119.140.02
104.50.597.50.4119.030.020.11
1050.47.30.3117.760.01
105.50.257.10.2315.50.010.08
1060.167.10.1612.390.01
106.50.17.20.119.380.010.06
1070.067.30.076.740.01
107.50.047.30.044.600.03

<tbody>
</tbody>

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.
 
Last edited:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
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

New Member
Joined
Apr 27, 2010
Messages
17
Hi
Yes that is checked by default, without it checked it is all over the place.
 

rxp409

New Member
Joined
Apr 27, 2010
Messages
17
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

Well-known Member
Joined
Dec 28, 2011
Messages
3,638

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Well-known Member
Joined
Dec 28, 2011
Messages
3,638

ADVERTISEMENT

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

New Member
Joined
Apr 27, 2010
Messages
17
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.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,569
Messages
5,770,908
Members
425,651
Latest member
Mark Cashin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top