Importing Data from a text file

[HajdukZD]

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

[q2009]

Create an empty Exel file. Select a cell where you want to download data. Then, select “Data” “from the text/CSV” tab and select your text file. In the dialog box, click the "Download" button. As a result, you get a table with your data where the dates are due to full dates.

[Logit]

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.

[HajdukZD]

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.

[alansidman]

Using q2009 instructions above, attached is the file

[HajdukZD]

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.

[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.

[HajdukZD]

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?

[Logit]

Open the attached.

Highlight the data / copy / paste to Excel range A1

[alansidman]

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

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

= Table.TransformColumnTypes(Source,{{"Column2", type time}})

[HajdukZD]

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

= Table.TransformColumnTypes(Source,{{"Column2", type time}})

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?

[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.

Sub Test()
    Dim objConn As Object, RS As Object
    Dim myFile As String, FileNum As Long, strSQL As String
    
    Const adOpenForwardOnly = 0
    Const adLockReadOnly = 1
    Const adCmdText = 1
    Const adUseClient = 3
    
    myFile = ThisWorkbook.Path & "\NQ 03-06_Last.txt"
    
    FileNum = FreeFile
    Open ThisWorkbook.Path & "\Schema.ini" For Output As #FileNum
        Print #FileNum, "[" & Dir(myFile) & "]"
        Print #FileNum, "Format=TabDelimited"
        Print #FileNum, "DateTimeFormat=dd.mm.yyyy"
        Print #FileNum, "ColNameHeader=False"
        Print #FileNum, "DecimalSymbol=."
        Print #FileNum, "TextDelimiter=None"
        Print #FileNum, "Col1=F1 DateTime"
        Print #FileNum, "Col2=F2 Long"
        Print #FileNum, "Col3=F3 Double"
        Print #FileNum, "Col4=F4 Double"
        Print #FileNum, "Col5=F5 Double"
        Print #FileNum, "Col6=F6 Double"
        Print #FileNum, "Col7=F7 Double"
    Close #FileNum
    
    Set objConn = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")
    
    #If Win64 Then
        objConn.Open "Driver=Microsoft Access Text Driver (*.txt, *.csv);" & _
                     "Dbq=" & ThisWorkbook.Path & ";Extensions=asc,csv,tab,txt;"
    #Else
        objConn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
                     "Dbq=" & ThisWorkbook.Path & ";Extensions=asc,csv,tab,txt;"
    #End If
    
    strSQL = "Select * From [" & Dir(myFile) & "]"
    RS.Open strSQL, objConn, adUseClient, adLockReadOnly, adCmdText
    
    Range("A2:G" & Rows.Count) = ""
    Range("A2").CopyFromRecordset RS
    
    Range("B2:B" & RS.RecordCount + 1).NumberFormat = "##"":""##"":""##"
    Kill ThisWorkbook.Path & "\Schema.ini"
    
    Set RS = Nothing
    objConn.Close
    Set objConn = Nothing
End Sub

Output is;

[HajdukZD]

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.

Sub Test()
    Dim objConn As Object, RS As Object
    Dim myFile As String, FileNum As Long, strSQL As String
    
    Const adOpenForwardOnly = 0
    Const adLockReadOnly = 1
    Const adCmdText = 1
    Const adUseClient = 3
    
    myFile = ThisWorkbook.Path & "\NQ 03-06_Last.txt"
    
    FileNum = FreeFile
    Open ThisWorkbook.Path & "\Schema.ini" For Output As #FileNum
        Print #FileNum, "[" & Dir(myFile) & "]"
        Print #FileNum, "Format=TabDelimited"
        Print #FileNum, "DateTimeFormat=dd.mm.yyyy"
        Print #FileNum, "ColNameHeader=False"
        Print #FileNum, "DecimalSymbol=."
        Print #FileNum, "TextDelimiter=None"
        Print #FileNum, "Col1=F1 DateTime"
        Print #FileNum, "Col2=F2 Long"
        Print #FileNum, "Col3=F3 Double"
        Print #FileNum, "Col4=F4 Double"
        Print #FileNum, "Col5=F5 Double"
        Print #FileNum, "Col6=F6 Double"
        Print #FileNum, "Col7=F7 Double"
    Close #FileNum
    
    Set objConn = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")
    
    #If Win64 Then
        objConn.Open "Driver=Microsoft Access Text Driver (*.txt, *.csv);" & _
                     "Dbq=" & ThisWorkbook.Path & ";Extensions=asc,csv,tab,txt;"
    #Else
        objConn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
                     "Dbq=" & ThisWorkbook.Path & ";Extensions=asc,csv,tab,txt;"
    #End If
    
    strSQL = "Select * From [" & Dir(myFile) & "]"
    RS.Open strSQL, objConn, adUseClient, adLockReadOnly, adCmdText
    
    Range("A2:G" & Rows.Count) = ""
    Range("A2").CopyFromRecordset RS
    
    Range("B2:B" & RS.RecordCount + 1).NumberFormat = "##"":""##"":""##"
    Kill ThisWorkbook.Path & "\Schema.ini"
    
    Set RS = Nothing
    objConn.Close
    Set objConn = Nothing
End Sub

Output is;

Did you code this all by hand by yourself?

[Haluk]

Did you code this all by hand by yourself?

Yes, I did. Pure hand made...

[HajdukZD]

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

[alansidman]

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

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

[HajdukZD]

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

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

[alansidman]

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

I am not understanding your issue. Perhaps you can show us what you get and then mock up what you expect. Your descriptions are not helpful to diagnose and offer a solution.