Consulting

Results 1 to 7 of 7

Thread: Reverse date Import from .csv file

  1. #1

    Reverse date Import from .csv file

    Hello,

    i was wondering if there's a way to reverse the date order
    when we import the csv file at once?

    the date we are using in the file is yyyy-MM-dd

    because when we import the csv file we get the last date on top instead on the last place and the counts down till 1

    Thank you

  2. #2
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    534
    Location
    Hello GhostofDoom,

    If you are importing the CSV to Excel, simply filter the data from newest to oldest. If you are doing something else then you will to post a copy of the CSV for review.
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  3. #3
    Hello Leith Ross,

    this is the file
    and this is the code

    
    
    Dim txtFileName As Variant
    Dim destCell As Range
    Dim qt
    
    
    Set destCell = Worksheets("TestingImport").Cells(Rows.Count, "B").End(xlUp).Offset(1)
    If destCell.Row < 9 Then Set destCell = Worksheets("TestingImport").Range("B9")
    
    
    txtFileName = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", Title:="Select a CSV File", MultiSelect:=False)
    If txtFileName = False Then Exit Sub
        
    Set qt = destCell.Parent.QueryTables.Add(Connection:="TEXT;" & txtFileName, Destination:=destCell.Cells(1, 1))
    With qt
      .TextFileStartRow = 2    'this leaves the header away
      .TextFileParseType = xlDelimited
      .TextFileCommaDelimiter = False    'you might not need this line at all - it might even be better to make it False
      .TextFileOtherDelimiter = Empty
      .TextFileSemicolonDelimiter = True
      .RefreshStyle = xlOverwriteCells    '<<changed/added
      .Refresh BackgroundQuery:=False
      
      With Intersect(.ResultRange.EntireRow, .Parent.Range("S:S"))
        .ClearContents    '<<add
        '.Cells(1) = 1
        .Cells(1) = .Cells(1).Offset(-1).Value + 1
        .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False
      End With
        With Intersect(.ResultRange.EntireRow, .Parent.Range("A:A"))
        .ClearContents    '<<add
        '.Cells(1) = 1
        .Cells(1) = .Cells(1).Offset(-1).Value + 1
        .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False
      End With
     ' Intersect(.ResultRange.EntireRow, .Parent.Range("Q:Q")).Value = "x"
      Intersect(.ResultRange.EntireRow, .Parent.Range("Q:Q")).SpecialCells(xlCellTypeBlanks).Value = "x"
      lr = .ResultRange.Rows(.ResultRange.Rows.Count).Row
      .WorkbookConnection.Delete
      .Delete
    End With
    With Worksheets("TestingImport").Range("B9").ListObject
      Set TL = .Range.Cells(1)
      lc = TL.Column + .ListColumns.Count - 1
      .Resize Range(TL, .Parent.Cells(lr, lc))
      'delete all blank rows in the table:
      For i = .ListRows.Count To 1 Step -1
        With .ListRows(i)
          If Application.CountA(.Range) = 0 Then .Delete
        End With
      Next i
    End With


    Thank you
    Attached Files Attached Files

  4. #4
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    534
    Location
    Hello GhostofDoom,

    The dates shown in Excel CSV file also include the time. I am assuming the time of day is to be taken into account when sorting the dates, yes?
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  5. #5
    Quote Originally Posted by Leith Ross View Post
    Hello GhostofDoom,

    The dates shown in Excel CSV file also include the time. I am assuming the time of day is to be taken into account when sorting the dates, yes?
    Yes sir

  6. #6
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    534
    Location
    Hello GhostOfDoom,

    Forgot to ask earlier, does this CSV get updated automatically or is it manually updated?
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  7. #7
    Hello Leith Ross,

    it gets downloaded from the database
    and we save it always under different names
    so i believe automatically

Posting Permissions

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