Importing Data from a text file

Hi,

I tried importing the following text file into excel.

There is a data and time stamp; open; high; low; close; volume

I don't know how to get excel to recognize the time stamp column.

Can anyone help?

Thanks,

Ivan

Just an observation.

I clicked on the text file. I opened in Notepad by itself (default selection on my computer). I then selected all, copied and pasted into Excel.

No conversion required.

that's great that worked for you.

That doesn't happen over here.

I really don't know how you did that and that it actually happened since there is no file posted under your response.

Originally Posted by alansidman

Using q2009 instructions above, attached is the file

I don't understand column 2 doesn't have a time format.

That's the trouble I am having the same as in your excel file.

Originally Posted by alansidman

Should the second column be time in military format. In the original text file this cannot be determined from the data shown. You would have needed to advise us of such. Are the six digit format supposed to be hh:mm:ss format? If so that needs to be programmed. If I assumed correctly then I have formatted the second column as time in Power query before closing and loading to native excel. You can review the mcode in the file.

Yeah I need military time. How did you convert the column to time?

Open the attached.

Highlight the data / copy / paste to Excel range A1

Originally Posted by alansidman

once loaded into power query I applied the following Mcode before loading and closing to Native Excel.

Please Login or Register  to view this content.

Ok I figured out what you did.

why is there this horrible formatting from this technique?

How do I get rid of this formatting and just have excel data like I gyped it in?

Does excel change this to some kind of table or something?

You guys are advanced.....so I did it like this.

And there is another issue I am having.

the current data downloaded is the 1 min time frame,

I need to convert and or adjust the data set for the 15 min, 60, and 240 minute time frames and chart it.

any ideas on how I can accomplish this?

Thanks

Originally Posted by alansidman

Please Login or Register  to view this content.

Because it is not excel but Power Query. You can format any way you like once you bring it into native excel.

It's not it changes the format of the entire workbook it's strange, even the dividing lines are gone

Originally Posted by Haluk

A VBA solution is given below where ADO and SQL used.

The important point is, file name should not involve "." so I renamed the file as NQ 03-06_Last.txt

Both the Excel file and the text file must be in the same folder.

Please Login or Register  to view this content.

Output is;

Did you code this all by hand by yourself?

Originally Posted by HajdukZD

Did you code this all by hand by yourself?

Yes, I did. Pure hand made...