Excel - get external data, delimiter prob?

900xs · Mar 16, 2004

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 · Mar 16, 2004

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 · Mar 16, 2004

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

mutantcar · Mar 17, 2004

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 · Mar 17, 2004

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 · Mar 17, 2004

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 · Mar 17, 2004

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 · Mar 17, 2004

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 · Mar 17, 2004

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 · Mar 17, 2004

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

Excel - get external data, delimiter prob?

900xs

Junior member

sidinuk

Established member

edeast

Junior member

mutantcar

Active member

900xs

Junior member

900xs

Junior member

mutantcar

Active member

900xs

Junior member

Attachments

rog1111

Established member

900xs

Junior member

Similar threads