Results 1 to 4 of 4

How to sort csv file first before extracting rows of data from it

Threaded View

  1. #1
    Registered User
    Join Date
    04-26-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2003 2007
    Posts
    69

    How to sort csv file first before extracting rows of data from it

    I have this code that works fine before, now the source csv file data is arranged in descending order, how to add code to sort the csv file to ascending order first, before extracting the last no. rows of data,

    some csv are having header in row 1 (Date Time ......) and some don't have header, how to ignore/delete the header before applying the codes.

    How to modify the following code, thanks

    
    Option Explicit
    
    Sub ConvertCSVs()
    
        Dim wkbDest As Workbook
        Dim wksDest As Worksheet
        Dim numrows As Integer
        Dim startrows As Integer
        Dim linrows As Integer
        Dim strPath As String
        Dim strFile As String
        Dim strData As String
        Dim X As Variant
        Dim Cnt As Long
        Dim r As Long
        Dim c As Long
        Dim i As Long
    
        Application.ScreenUpdating = False
    
        strPath = "D:\Docs\CSV\"                        ' Change Path of files
        
        If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
        
        strFile = Dir(strPath & "*.csv")
          
        Do While Len(strFile) > 0
        
            Cnt = Cnt + 1
            
            If Cnt = 1 Then
                Set wkbDest = Workbooks.Add(xlWBATWorksheet)
            End If
        
            Open strPath & strFile For Input As #1
           
            numrows = 0
                Do Until EOF(1)
                    Line Input #1, strData
                    numrows = numrows + 1
                Loop
                
            Close #1
    
        startrows = numrows - 1000                               'Extract last 1000 Rows from CSV files
    
            Open strPath & strFile For Input As #1
            
                Set wksDest = wkbDest.Worksheets.Add
                
                wksDest.Name = Left(strFile, InStr(1, strFile, ".csv") - 1)
                
                r = 2
                c = 1
            linrows = 0
                Do Until EOF(1)
                    Line Input #1, strData
                    linrows = linrows + 1
                    If linrows > startrows Then
                        X = Split(strData, ",")
                        For i = LBound(X) To UBound(X)
                                Cells(r, c).Value = X(i)
                                c = c + 1
                        Next i
                        r = r + 1
                        c = 1
            End If
                Loop
                
            Close #1
            
            strFile = Dir
            
        Loop
        
       If Cnt > 0 Then
            Application.DisplayAlerts = False
            wkbDest.Worksheets(wkbDest.Worksheets.Count).Delete
            Application.DisplayAlerts = True
            Application.ScreenUpdating = True
            MsgBox "Completed...", vbInformation
        Else
            Application.ScreenUpdating = True
            MsgBox "No CSV files found...", vbExclamation
        End If
        
    End Sub
    Last edited by bezbid; 08-09-2015 at 01:05 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Extracting data from one sheet to sort in another
    By Smedlex in forum Excel General
    Replies: 2
    Last Post: 03-17-2015, 12:13 AM
  2. [SOLVED] Extracting data from a CSV file
    By pernes in forum Excel General
    Replies: 4
    Last Post: 03-22-2014, 01:42 PM
  3. Extracting data from file in directory and extracting filename
    By brad999 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-20-2014, 11:21 AM
  4. Extracting data from corrupted file
    By Merlinti in forum Excel General
    Replies: 2
    Last Post: 11-27-2013, 02:54 PM
  5. Replies: 6
    Last Post: 08-18-2012, 05:00 AM
  6. Extracting data from a text file
    By lamore48 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-19-2012, 09:53 PM
  7. Extracting Data from a .txt file
    By baby_rach in forum Access Programming / VBA / Macros
    Replies: 4
    Last Post: 06-15-2010, 10:09 AM

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