1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Importing External Data from a .csv file into Excel

Discussion in 'Business Applications' started by youwillbowdown, Nov 5, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. youwillbowdown

    youwillbowdown Thread Starter

    Joined:
    Nov 5, 2007
    Messages:
    7
    I am continually being caught by the 65536 row limit in microsoft excel. I have a text file that has over 200k+ lines of data in it. The bright side of this is that it is in a .csv format (even thought it is actually a text file). My dilemma is this, since I cannot import all 200000 lines of data using the import external data wizard, I am hoping to write a VBA function that will accomplish the same task. The data is HUGE. I can break it up into seperate files, and when I import it that way it works just fine. I need to have it split it at ~60000 lines onto a worksheet, create a new worksheet, place another 60000 lines, create a new worksheet and so on until it has imported all of the data. I cannot place it all on 1 sheet, due to it having almost 200 columns of data per line.

    Any help would be greatly appreciated.

    Questions or concerns, let me know.
     
  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
  3. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    With the varying versions of Excel and the row differences, I would incorporate a slight difference in that I would use a separate variable to track the rows. Plus, I would not select anything. Why Microsoft decided to publish a set of VBA code that has this, I have no idea, somebody should slap their wrist for it though ;) ...

    Code:
    Sub LargeFileImport()
        'Dimension Variables
        Dim wb As Workbook, ws As Worksheet
        Dim ResultStr As String
        Dim FileName As String
        Dim FileNum As Integer
        Dim Counter As Long, iRow As Long
        'Ask User for File's Name
        FileName = InputBox("Please enter the Text File's name, e.g. test.txt")
        'Check for no entry
        If FileName = "" Then Exit Sub
        'Get Next Available File Handle Number
        FileNum = FreeFile()
        'Open Text File For Input
        Open FileName For Input As #FileNum
        'Turn Screen Updating Off
        Application.ScreenUpdating = False
        'Create A New WorkBook With One Worksheet In It
        Set wb = Workbooks.Add(template:=xlWorksheet)
        Set ws = wb.Sheets(1)
        'Set The Counter to 1
        Counter = 1: iRow = 1
        'Loop Until the End Of File Is Reached
        Do While Seek(FileNum) <= LOF(FileNum)
           'Display Importing Row Number On Status Bar
            Application.StatusBar = "Importing Row " & _
               Counter & " of text file " & FileName
            'Store One Line Of Text From File To Variable
            Line Input #FileNum, ResultStr
            'Store Variable Data Into Active Cell
            If Left(ResultStr, 1) = "=" Then
               ws.Cells(iRow, 1).Value = "'" & ResultStr
            Else
               ws.Cells(iRow, 1).Value = ResultStr
            End If
            'For Excel versions before Excel 97, change 65536 to 16384
            If iRow = 65536 Then
               'If On The Last Row Then Add A New Sheet
               ActiveWorkbook.Sheets.Add
               iRow = 0
            End If
            'Increment the Counter By 1
            Counter = Counter + 1
            iRow = iRow + 1
        'Start Again At Top Of 'Do While' Statement
        Loop
        'Close The Open Text File
        Close
        'Remove Message From Status Bar
        Application.StatusBar = False
    End Sub
    Edit: Oh, by the way, there is a fairly easy method to bring up a file dialog picker so you could choose the file from an open window instead of typing in the name of the file (which would work best on an indexed system). Personally I like this way better, but if you'd like that just let us know and we'll whip an example out for ya.

    HTH
     
  4. youwillbowdown

    youwillbowdown Thread Starter

    Joined:
    Nov 5, 2007
    Messages:
    7
    Thanks for the help guys (or gals ;) ).

    Now just one more question for you. Once I import the data, I still have to seperate it into columns. I do this by using the code below (just basic text to columns). However, it takes FOREVER (even when I dedicate an entire server to it). Is there a better (or faster) way to perform this task?

    Code:
       Sub delimitedseperation()
    
        Columns("A:A").Select
        Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
            :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
            Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
            ), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
            (20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), _
            Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array( _
            33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), _
            Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array( _
            46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 1), _
            Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array( _
            59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, 1), _
            Array(66, 1), Array(67, 1), Array(68, 1), Array(69, 1), Array(70, 1), Array(71, 1), Array( _
            72, 1), Array(73, 1), Array(74, 1), Array(75, 1), Array(76, 1), Array(77, 1), Array(78, 1), _
            Array(79, 1), Array(80, 1), Array(81, 1)), TrailingMinusNumbers:=True
    End Sub
    
    TIA
    ywbd
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Whenever I am confronted with massive amounts of data, I import it into Access - for reasons I don't understand (but don't care, as long as it works), Access seems to use fewer resources to do the job. It also does not have the size limits. On top of that, once you have the headers, you can copy a table without the data and strip out the headers of the next file you import and do a union query to put the new data into a table with headers already there.
    What you decide to do with the data after getting it into Access is up to you - you could filter for certain data, or have an id for each row of data and just show the first 60000 lines in one query, the next 60000 in another and so on (I use 60000 from your first post) and you could copy this data from Access and put it in Excel.
     
  6. youwillbowdown

    youwillbowdown Thread Starter

    Joined:
    Nov 5, 2007
    Messages:
    7
    Thanks for the idea slurpee55, however that isn't a possibility. The end user needs to be able to run this every week, and they will not have Access installed on their computers (or anywhere they can access).
     
  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    I thought you sounded like the end user - and it wouldn't be feasible to have you do this either? Just suggesting that having someone take 30 minutes to do this in Access would be a huge savings over using up hours on a server...
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Can you post a few row examples of your data?
     
  9. youwillbowdown

    youwillbowdown Thread Starter

    Joined:
    Nov 5, 2007
    Messages:
    7
    @firefytr - No I cannot. The information is sensitive, and I cannot disclose any of it. Would have probably made my job easier if I could have!

    @slurpee55 - Honestly no. I don't really want to get into the reasons, but it would be too hard to use Access.

    After playing around, I found that if I have it perform the column separation after each line that it imports (using the aforementioned Microsoft code) then it performs the computations fairly quickly. I can live with it taking ~5 minutes.

    Thanks for the help guys. My problem is solved, how do we close this topic?

    ywbd
     
  10. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Well, sorry we couldn't be more help. Go to the top of the page and click on the thread tools and you can mark this Solved.
     
  11. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    You don't need to post your actual data, just a representative sample. I understand sensitive information (honestly IMO there is no information sensitive enough to be claimed as 'sesitive', I have my reasons ;) ), but I guarantee you there is a way to post a representative sample with dummy data. We just need the structure and logic.
     
  12. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Now, now...shall we post bank statements online? You first.
     
  13. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
  14. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
  15. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    1,111,111,111,111,111.00 overdrawn
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Solved Importing External
  1. NinjaGuy47
    Replies:
    1
    Views:
    258
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/648144

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice