Excel - get external data, delimiter prob?

[900xs]

I can import data from this site :

http://sc.sc8.finance.lycos.com:443/?User=demo&Pswd=demo&DataType=DATA&Symbol=$indu&Interval=1

the data appears but not in the correct format i.e. columns are not dividing in the right place so it may be something to do with the delimiters (comma on the web page!) seems that the data is imported as a table.

I just want the correct data in the correctly spaced column

I can import it as a text file (correct spacing) but then cannot refresh it (automatically - every minute)

I have Excel 2000

Any ideas out there

Thank you

 

[sidinuk]

when you try to open the file, Excel will run the import wizard. Make sure 'delimited' is selected on the first page and 'comma' is ticked on the second page. On the 3rd page you can convert the date by highlighting column A and selecting the correct date format from the list.

When the file has opened save it as an Excel file.

HTH

 

[edeast]

900xs- Try slecting "Data" then text to columbs - save as .xls- Cheers ED

 

[mutantcar]

900xs,

Correct me if I'm wrong, but you're using a Web query to bring the data in?

After a quick look, I think you'll need some code (macro) to format the dates etc. correctly. If you don't know how to do this, I'll have a further look tomorrow.

Mute.

 

[900xs]

Thank you, Thank you: sidinuk, edeast, mutantcar.

That did it just as I had hoped, great advice...............ah! but then I had it set to refresh at 1 min interval and......in cell A1 the data arrived again (in the original comma delimited syle)

don't have the experience needed......but will return tomorrow to do battle again

this is frustrating......but will rest easier with all your help.

Thanks

900xs

 

[900xs]

yes mutancar......using a Web query. It worked just fine until the first refresh (after 1 min)......however I'm confident it can be solved. I have tried for a long while to get data automatically into an excel file. (doesn't seem to exist ........not to my (tired) web browsing eyes anyway.)

 

[mutantcar]

I've done this some time ago with yahoo data, but from what I can remember had lots of problems with it - something to do with my connection going down. I may have been on dial-up then though.

Formatting's not difficult to solve, (Have you tried 'Preserve formatting' in query properties?) but is for another day - or later today anyway!

Cheers,

Mute.

 

[900xs]

Here is what I mean.

if you download this, it will change after 1 min because: auto refresh is set at 1 min and the columns I want refreshed, are not.

any thoughts??

 

[rog1111]

900xs

Since your data has 7 fields, you could just put formulae in columns B:H eg
In cell B2 put =left(a2,6)
In cell c2 put = mid(a2,8,4)
etc

If the field lengths vary then you could make use of Excel's FIND worksheet function & look for the commas in the A column. Also TRIM removes spaces & might be useful.

Then each time the data refreshes you'll have what you need in columns B:H

rog1111

 

[900xs]

Thanks rog1111.........that worked a treat (learnt something new