Getting data from CSV file into a workbook

[AventAClue]

Hi All,

Hoping someone may be able to help out with some knowledge and code.

I have an excel workbook that I'd like to automate the process of me going to a csv file, copying it's data, and pasting it back to a worksheet within my workbook.

Ive just had some great help from Peter SSs to manipulate some data from worksheet to worksheet within the workbook, but now hope to go that little further in being able to have the csv data part of it included in the process.

Thinking along the lines of pressing a macro button within my workbook it:

-Asks which file i wish to open
-Upon me locating and selecting the csv, copies the sheet contents (can be the whole csv file sheet)
-Closes the csv file and pastes the copied data to worksheet called "DataDownload" in my workbook

It could then continue on and run the code that Peter SSs provided for me.

The thread that covers my previous 'worksheet to worksheet' code which may help in explaining what I'm after is located at: http://www.mrexcel.com/forum/showthread.php?p=2874620#post2874620


Thanks very much

 

[Jon von der Heyden]

Hi Jon,
Forgive my ignorance. I'm not sure what you mean by ADO?

ADO (ActiveX Data Objects) is Microsoft's technology of choice for performing client-server data access between any data consumer (the client) and any data source (the server or provider). In a nutshell, it's a technology that allows us to manage data in databases. A CSV file can be considered a data table so we can use ADO to grab the data from a CSV file.

 

[AventAClue]

I'm quite partial to a bit of ADO when it comes to importing CSV data.

Public Sub GetCSVData()
    Dim varFile As Variant
    Dim lngSplit As Long, strTable As String, strPath As String
    Dim objRS As Object
    Dim strConnection As String, strSQL As String
    Dim lngField As Long, varHeaders As Variant
    
    varFile = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , "Get CSV File", "Select", False)
    If varFile = False Then Exit Sub
    
    lngSplit = InStrRev(varFile, "\")
    
    strTable = Mid$(varFile, lngSplit + 1)
    strPath = Left$(varFile, lngSplit - 1)
    
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & strPath & ";" & _
                    "Extended Properties=Text;"
                    
    strSQL = "SELECT * FROM " & strTable & ";"

    Set objRS = CreateObject("ADODB.Recordset")
    Call objRS.Open(strSQL, strConnection, 1, 3, 1)
    
    With Sheets("DataDownload")
        .UsedRange.Clear
        For lngField = 0 To objRS.Fields.Count - 1
            .Cells(1, lngField + 1).Value = objRS.Fields(lngField).Name
        Next lngField
        Call .Cells(2, 1).CopyFromRecordset(objRS)
    End With
    
    objRS.Close
    Set objRS = Nothing
End Sub

Hi Jon

I just thought I'd try your code to see how it works, however it throws a run-time error '-2147217900(80040e14)':
Syntax error in FROM clause.
And on debugging has stopped at:
Call objRS.Open(strSQL, strConnection, 1, 3, 1)

Any clues on what that's telling me?

I am more than happy with the solution Peter has given me, as it does precisely what I requested, but more from a learning perspective I'm curious to see how yours works if that's still ok?

Thanks.

 

[Jon von der Heyden]

If you open the csv file in Excel, does it have column labels / field names in row 1?

Also, when the code breaks, can you please check what the value is for strTable? Use the locals window (View > Locals Window).

 

[AventAClue]

If you open the csv file in Excel, does it have column labels / field names in row 1?

Also, when the code breaks, can you please check what the value is for strTable? Use the locals window (View > Locals Window).

Hi Jon,

Yes as below: (another new trick I've learnt from Peter SSs )
Excel 2010<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(187, 187, 187); border-collapse: collapse;" cellpadding="2.5px" rules="all"><colgroup><col style="background-color: rgb(218, 231, 245);" width="25px"><col><col></colgroup><thead><tr style="background-color: rgb(218, 231, 245); text-align: center; color: rgb(22, 17, 32);"><th>
</th><th>A</th><th>B</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">1</td><td style="">Timestamp</td><td style="text-align: right;">22092011</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">2</td><td style="text-align: right;">6/06/2011 20:00</td><td style="text-align: right;">0</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">3</td><td style="text-align: right;">6/06/2011 20:05</td><td style="text-align: right;">102</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">4</td><td style="text-align: right;">6/06/2011 20:10</td><td style="text-align: right;">98</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">5</td><td style="text-align: right;">6/06/2011 20:15</td><td style="text-align: right;">77</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">6</td><td style="text-align: right;">6/06/2011 20:20</td><td style="text-align: right;">80</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">7</td><td style="text-align: right;">6/06/2011 20:25</td><td style="text-align: right;">78</td></tr></tbody></table>

22092011-6611-2594​

In regards to your 2nd question:

: strTable : "22092011-6611-2594.csv" : String

Which is the correct name of the csv I am trying to open

Thankyou

 

[Jon von der Heyden]

The hyphens in the table name is the culprit. We overcome this by enclosing the table name in square brackets:

Public Sub GetCSVData()
    Dim varFile As Variant
    Dim lngSplit As Long, strTable As String, strPath As String
    Dim objRS As Object
    Dim strConnection As String, strSQL As String
    Dim lngField As Long, varHeaders As Variant
    
    varFile = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , "Get CSV File", "Select", False)
    If varFile = False Then Exit Sub
    
    lngSplit = InStrRev(varFile, "\")
    
    strTable = Mid$(varFile, lngSplit + 1)
    strPath = Left$(varFile, lngSplit - 1)
    
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & strPath & ";" & _
                    "Extended Properties=Text;"
                    
    [highlight]strSQL = "SELECT * FROM [B][COLOR="Red"][[/COLOR][/B]" & strTable & "[B][COLOR="red"]][/COLOR][/B];"[/highlight]

    Set objRS = CreateObject("ADODB.Recordset")
    Call objRS.Open(strSQL, strConnection, 1, 3, 1)
    
    With Sheets("DataDownload")
        .UsedRange.Clear
        For lngField = 0 To objRS.Fields.Count - 1
            .Cells(1, lngField + 1).Value = objRS.Fields(lngField).Name
        Next lngField
        Call .Cells(2, 1).CopyFromRecordset(objRS)
    End With
    
    objRS.Close
    Set objRS = Nothing
End Sub

 

[AventAClue]

The hyphens in the table name is the culprit. We overcome this by enclosing the table name in square brackets:

Public Sub GetCSVData()
    Dim varFile As Variant
    Dim lngSplit As Long, strTable As String, strPath As String
    Dim objRS As Object
    Dim strConnection As String, strSQL As String
    Dim lngField As Long, varHeaders As Variant
    
    varFile = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , "Get CSV File", "Select", False)
    If varFile = False Then Exit Sub
    
    lngSplit = InStrRev(varFile, "\")
    
    strTable = Mid$(varFile, lngSplit + 1)
    strPath = Left$(varFile, lngSplit - 1)
    
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & strPath & ";" & _
                    "Extended Properties=Text;"
                    
    [highlight]strSQL = "SELECT * FROM [B][COLOR=Red][[/COLOR][/B]" & strTable & "[B][COLOR=red]][/COLOR][/B];"[/highlight]

    Set objRS = CreateObject("ADODB.Recordset")
    Call objRS.Open(strSQL, strConnection, 1, 3, 1)
    
    With Sheets("DataDownload")
        .UsedRange.Clear
        For lngField = 0 To objRS.Fields.Count - 1
            .Cells(1, lngField + 1).Value = objRS.Fields(lngField).Name
        Next lngField
        Call .Cells(2, 1).CopyFromRecordset(objRS)
    End With
    
    objRS.Close
    Set objRS = Nothing
End Sub

Hi Jon

Yes, that did the trick. Thankyou very much.

Thankyou to you all for all your help. I now have three successful, yet different methods of doing it.

I appreciate everyone's help! You're all very generous people!

Cheers
Aventaclue