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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 
Joined
Jul 25, 2004
Messages
5,456
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
 

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

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

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
 
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.
 
Joined
Jul 25, 2004
Messages
5,456
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Top