Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using ADO with txt files (2003)

    Is it possible to use ADO in Excel to import a text or csv file. Specifically I want to only import a certain number of rows. Does anyone have sample code to demostrate this? Thanks, Andy.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Using ADO with txt files (2003)

    Does it have to be ADO?
    There is some code at ExcelTips: Selectively Importing Records which use Excel's VBA and could be modified...

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using ADO with txt files (2003)

    I think it may depend on what your "certain number" consists of. If it is the first x of the total number of records, and assuming the total records are less than 65,000, the simplest solution might be to use DoCmd.TransferText to import the whole file, and then to create a range consisting of rows from x+1 to the end, and then delete those rows.

    Nick

  4. #4
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using ADO with txt files (2003)

    Thanks, I'll check this out. Andy

  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using ADO with txt files (2003)

    Could be more than 65,000. Wouldn't I need a reference to the Access Object Model to use DoCmd? Andy

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using ADO with txt files (2003)

    Excel has its own text import wizard indeed.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using ADO with txt files (2003)

    The linked item uses file I/O which I might consider. Is it not possible to treat a text file as though it were a database table, so that I could then use a SQL statement (with a WHERE clause) to import the rows I need? Andy

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Using ADO with txt files (2003)

    If the final output can be more than 65,000 rows using XL2003, you might also take a look at: ExcelTips: Importing Huge Data Files which has code to break up the textfile into various sheets...

    Steve

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Using ADO with txt files (2003)

    For a CSV file you can use code like:
    <pre>Sub GetCSVData()
    ' Sample demonstrating how to return a recordset from a CSV file
    Dim cn As ADODB.Connection, strQUery As String, rst As ADODB.Recordset
    Dim lngFieldCount As Long
    Set cn = New ADODB.Connection
    With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=C:" & _
    ";Extended Properties='text;HDR=NO;FMT=Delimited'"
    .Open
    End With
    strQUery = "SELECT * FROM [INDEX.csv];"
    Set rst = New ADODB.Recordset
    rst.Open strQUery, cn, adOpenKeyset, adLockOptimistic, adCmdText
    lngFieldCount = rst.Fields.Count
    With ActiveSheet
    .Range(.Cells(1, 1), .Cells(10, lngFieldCount)).Value = _
    Application.Transpose(rst.GetRows(10))
    End With
    Set rst = Nothing
    cn.Close
    Set cn = Nothing
    End Sub
    </pre>


    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using ADO with txt files (2003)

    Hey, this looks good. I assume it should be possible to reduce the no. of imported records by modifying the SQL statement? I notice the statement uses the syntax '[index.csv]'. Is there any other specific syntax I need to know when apply a WHERE clause?

    Thanks, Andy.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using ADO with txt files (2003)

    Rory uses GetRows(10) to retrieve only 10 records from the CSV file. If you want to retrieve 37 records, change both occurrences of 10 in the code to 37.

    You can add a WHERE clause and/or SORT BY clause to the SQL string the usual way, e.g.
    <code>
    strQuery = "SELECT * FROM [INDEX.csv] WHERE [Field1]='London' SORT BY [Field2]"</code>

  12. #12
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using ADO with txt files (2003)

    Well this looks like an ideal solution. I'd prefer to use ADO rather than File I/O, particularly as it can immediately restrict the amount of data that is imported.

    One more question: Can the code be modified to deal with text files that are, for example, tab delimited? Andy.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using ADO with txt files (2003)


  14. #14
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using ADO with txt files (2003)

    Thank you. A very thorough article. Andy

  15. #15
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using ADO with txt files (2003)

    How would the code be written to import a large , more than 65000 records, text file that consisted of 8 different reports ,each report with a title, into 8 different spread sheets in the same workbook? Especially when the reports do not have a fixed length. Would you have to declare the work sheets first? I can see how you might pull the first report but how is the code written to pull out the middle reports?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •