+ Reply to Thread
Results 1 to 9 of 9

Import text file via Query

  1. #1
    Registered User
    Join Date
    01-08-2009
    Location
    Colorado
    MS-Off Ver
    Excel 2007, 2010 PC, 2011 Mac
    Posts
    41

    Import text file via Query

    I have created a sub to import tab delimited files into a specific sheet. Everything works but it is so horribly slow it is unusable. The files I am importing will start now with around 4-5000 lines and will grow every day at a rate of approximately 5000 lines per year. Right now it is taking around 27 seconds to import 5 lines. Each line has 7 tab separated values. If I take out the single line that actually puts the value in the cell the code goes through the entire file almost instantly.

    Just looking to see if there is something I can do to make it faster. I have tried this in Excel 2007 on Windows, and Excel 2011 for Mac with the same results.

    Here is the entire sub. I have comments in there to show where it is slow.
    Please Login or Register  to view this content.
    This code is being called in the Workbook_Open() event. Not sure if that matters or not.

    Here is a sample couple of lines of the data in the text file.
    65089 4/8/2011 3/9/2011 550321187 DELPHI ELECTRONICS & SAFETY 456 DIC
    65088 4/8/2011 3/9/2011 550321449 DELPHI ELECTRONICS & SAFETY 1682 DIC
    65087 4/8/2011 3/9/2011 550321449 DELPHI ELECTRONICS & SAFETY 234 DIC
    65086 4/8/2011 3/9/2011 100655-001 "The AVC Group, LLC." 1382.65 DIC
    65085 4/8/2011 3/9/2011 60272 DYNA GRAPHICS CORP 174.3 DIC

    I am also attaching a partial file of the data to this post "tabdata.txt".
    There is a timer in there that will pop up a message box every 5 lines it imports.

    I'm not sure if there is any way to do this faster. I have searched the forum for import but nothing that I have found makes any sense to me how to improve this.

    Any help is greatly appreciated!

    Thanks
    Attached Files Attached Files
    Last edited by extrapulp; 10-15-2011 at 09:31 PM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    re: Import text file via Query

    Hi,

    It's a little difficult to offer specific help without knowing the overall objective. However my first thought is why are you choosing to grab one line at a time? Why don't you simply import the the whole file, then worry about analysing, editing or changing the data afterwards.

    Regards
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    01-08-2009
    Location
    Colorado
    MS-Off Ver
    Excel 2007, 2010 PC, 2011 Mac
    Posts
    41

    re: Import text file via Query

    Richard,

    The overall objective is to import three different text files that are generated out of our accounting software and create an excel workbook that shows week to date, month to date, and year to date sales info. Right now we are manually doing this. All the formulas and so on in the excel sheet are working we just need to get the data in the corresponding sheets. I would like to automate it so anyone that has access to the workbook and can create the tab delimited files can just open the workbook and the numbers all show up correctly. In the end I will control where the tab delimited files are located and when they are created. So the Sales Manager can just open this workbook every Friday and he will have all the info he needs to fill out his reports to the owners.

    The only reason that I am grabbing one line at a time is because that is the only way I know how to do it. I just need the entire file in separate cells in the same order they are in the text file starting at cell A2. If there is a better way I am open to that. The way I have it coded is the only way I could come up with that made any kind of sense to me.

    Thanks for the quick reply.

  4. #4
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    re: Import text file via Query

    hi, extrapulp, you can try to use either macro recording and do txt file import and edit the code you get or use something sort of that:

    Please Login or Register  to view this content.
    Try to run this code on a test workbook

  5. #5
    Registered User
    Join Date
    01-08-2009
    Location
    Colorado
    MS-Off Ver
    Excel 2007, 2010 PC, 2011 Mac
    Posts
    41

    re: Import text file via Query

    watersev,

    I modified the code you provided so I can pass in a file and the sheet I want the data imported to. I also took out the screen updating I have that handled outside this sub.

    It works great but it opens a new workbook and puts the data in there starting at cell A1 not A2 as called out. I would like to specify which sheet the data goes into. I thought with selecting the sheet that would do it. The following modified code imported 5295 rows of data in a second or two.

    Here is the modified code.
    Please Login or Register  to view this content.
    That is much much simpler and way faster. Can you give me any insight on how to get it to go into the specified sheet instead of putting it in a new workbook?

    Thank you for the reply!

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    re: Import text file via Query

    Maybe ...

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Registered User
    Join Date
    01-08-2009
    Location
    Colorado
    MS-Off Ver
    Excel 2007, 2010 PC, 2011 Mac
    Posts
    41

    re: Import text file via Query

    shg,

    Running the code you posted unaltered gives me an error out of resources (see attached jpg of error window) on the line
    Please Login or Register  to view this content.

    It is also opening another workbook. In both cases it opens a workbook named the same as the file name I am importing.

    I am still messing with the code you and watersev posted. Maybe I can stumble on some alteration of that to make it work. Either way I am seeing different perspectives on how to get the same results!

    As far as recording a macro of importing a file. I know how to record a macro but how would I import a file via Excels interface?

    Thanks for all the great replies!
    Attached Images Attached Images

  8. #8
    Registered User
    Join Date
    01-08-2009
    Location
    Colorado
    MS-Off Ver
    Excel 2007, 2010 PC, 2011 Mac
    Posts
    41

    re: Import text file via Query

    Alright I figured out how to record the macro of importing a file via the Excel interface. I have never done this before. Just always copy and paste and then text to columns... Learn something every day.

    This is what I ended up with and it works just great. Takes about 3 seconds for almost 6000 rows.

    Please Login or Register  to view this content.

    Thanks goes out to everyone that helped!!!!!

  9. #9
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    re: Import text file via Query

    Well done!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1