String to array using split

Hi all,

I have many csv files which I searching. If some data fit my conditions I put this lines into excel sheet.
Example content of file

10-02-2022 02:09:26;lider;0123456789;1295.606;6.646416;40.26785;6.856215;219.5962;7.735233;7.840924;5.326111;5.34971;3.108031
10-02-2022 02:09:36;lider;0123456789;180.3252;6.055035;0.0;5.745333;19.70758;7.860642;7.872498;5.408381;5.459498;0.0
10-02-2022 02:09:46;lider;0123456789;1068.001;6.895689;24.23427;7.128262;220.8212;7.932885;7.978186;5.763059;5.788158;2.269124
10-02-2022 02:09:56;lider;0123456789;759.2748;6.048777;12.00476;6.061655;216.1708;7.951935;7.983704;4.823963;4.857661;1.581083
10-02-2022 02:10:06;lider;0123456789;1004.858;7.143832;27.27258;7.321005;220.8766;7.987683;8.026818;5.918257;5.943105;2.714073

Files I open this function

Please Login or Register  to view this content.

content of file I assign to array this function

Please Login or Register  to view this content.

to put data into range I use this code

Please Login or Register  to view this content.

The problem is with some date format. For example date 10-02-2022 02:09:26 is shown in excel cell as 02-10-2022 02:09:26

How to fix this problem?

Originally Posted by CheeseSandwich

Maybe you could try the QueryTable method:

Please Login or Register  to view this content.

Thanks, I tried but it's take much time when processing thousands of files Current method is quicker

Originally Posted by mtcas

Hi all,
The problem is with some date format. For example date 10-02-2022 02:09:26 is shown in excel cell as 02-10-2022 02:09:26

Just loop through tabi, and swap the day/month or follow your regional settings if the line is like "##-##-#### ##:##:##".

Yes, this is the way I will take, but want to avoid additional loop if possible

I don't understand why people doesn't like loops...
Loop within array(memory) is almost instant.

I like loops but sometimes I'm looking for very efficient solutions. I'll measure time maybe indeed difference is imperceptible

Speed. Of course if less lines is also good.

Originally Posted by kvsrinivasamurthy

Have you seen post #5.

Sorry, I missed, but tested and unfortunately result is the same as previous - nothing changed

Originally Posted by mtcas

How to fix this problem?

Hi,

attach at least a source csv text file and accordingly the exact expected workbook result …

Files attached

This is my code.
I think all functions are unnecessary to explain, the problem is only with date formatting.

Please Login or Register  to view this content.

According to your post #15 attachment a VBA demonstration for a starting point to paste only to the Sheet2 (all) worksheet module :

PHP Code: 

Sub Demo1()
  Const L = #10/2/2022 10:17:00 AM#, U = #10/2/2022 10:27:00 AM#
    Dim C$, F%, V
        C = ThisWorkbook.Path & "\20220210_103831.csv":  If Dir(C) = "" Then Beep: Exit Sub
        F = FreeFile
        UsedRange.Clear
        Open C For Input As #F
        V = Split(Input(LOF(F), #F), vbCrLf)
        Close #F
        Application.ScreenUpdating = False
    With [A1].Resize(UBound(V) + 1)
       .Value2 = Application.Transpose(V)
       .TextToColumns , 1, xlTextQualifierNone, , , True, , , , , , "."
    End With
    With UsedRange.Columns
       .Item(1).AutoFilter 1, "<" & Format(L, [A2].NumberFormat), 2, ">=" & Format(U, [A2].NumberFormat)
        If Application.Subtotal(103, .Item(1)) > 1 Then .Offset(1).EntireRow.Delete
       .AutoFilter
'       .AutoFit
    End With
        Application.ScreenUpdating = True
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Originally Posted by Marc L

According to your post #15 attachment a VBA demonstration for a starting point to paste only to the Sheet2 (all) worksheet module :

PHP Code: 

Sub Demo1()
  Const L = #10/2/2022 10:17:00 AM#, U = #10/2/2022 10:27:00 AM#
    Dim C$, F%, V
        C = ThisWorkbook.Path & "\20220210_103831.csv":  If Dir(C) = "" Then Beep: Exit Sub
        F = FreeFile
        UsedRange.Clear
        Open C For Input As #F
        V = Split(Input(LOF(F), #F), vbCrLf)
        Close #F
        Application.ScreenUpdating = False
    With [A1].Resize(UBound(V) + 1)
       .Value2 = Application.Transpose(V)
       .TextToColumns , 1, xlTextQualifierNone, , , True, , , , , , "."
    End With
    With UsedRange.Columns
       .Item(1).AutoFilter 1, "<" & Format(L, [A2].NumberFormat), 2, ">=" & Format(U, [A2].NumberFormat)
        If Application.Subtotal(103, .Item(1)) > 1 Then .Offset(1).EntireRow.Delete
       .AutoFilter
'       .AutoFit
    End With
        Application.ScreenUpdating = True
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Thanks, but in may sheet still shows 02-10 instead of 10-02

So your bad 'cause you misread my post #14 as my demonstration reproduces exactly your attachment expected result workbook !

Originally Posted by Marc L

So your bad 'cause you misread my post #14 as my demonstration reproduces exactly your attachment expected result workbook !

Ok, maybe my english is not enough, but I copied your code into new worksheet and result is - date is incorrect(day swapped with month). What I do bad?

Originally Posted by CheeseSandwich

Maybe:

Please Login or Register  to view this content.

Thanks, probably additional loop will be the simplest solution.

Originally Posted by mtcas

What I do bad?

Your post #15 attachment does not have the exact expected result workbook as requested in my post #14 …

Originally Posted by Marc L

Your post #15 attachment does not have the exact expected result workbook as requested in my post #14 …

Ok.
Do you like wasting time? Just read post #1 and you should know that result I want to reach is "For example date 10-02-2022 02:09:26 is shown in excel cell as 02-10-2022 02:09:26
How to fix this problem?"
Yes, my bad, in the file "result" is wrong date format

I tried also set cell formatting as "TEXT" but then debugger stops on line when tried fill the range. So excel is forcing change 10.02 for 02.10

Please Login or Register  to view this content.

Next time you will create a thread you should be more accurate in your initial post
as is it so difficult to indicate the text file date format like to attach the exact expected result ?‼

As a reminder VBA CDate function well works only if the Windows Regional Settings are the same than the date format used within the csv file …

So according to the post #15 bad attachment, according to Excel basics & VBA help,
a VBA demonstration for starters to paste only to the Sheet2 (all) worksheet module :

PHP Code: 

Sub Demo2()
    Dim S$, L As Date, U As Date, F%, V, R&, D As Date
        S = ThisWorkbook.Path & "\20220210_103831.csv":  If Dir(S) = "" Then Beep: Exit Sub
        L = DateSerial(2022, 2, 10) + TimeValue("10:17")
        U = DateSerial(2022, 2, 10) + TimeValue("10:27")
        F = FreeFile
        UsedRange.Clear
        Open S For Input As #F
        V = Split(Input(LOF(F), #F), vbCrLf)
        Close #F
    For R = 1 To UBound(V)
        If V(R) > "" Then
            D = DateSerial(Mid(V(R), 7, 4), Mid(V(R), 4, 2), Left(V(R), 2)) + TimeValue(Mid(V(R), 13, 8))
            If D < L Or D >= U Then V(R) = False Else Mid(V(R), 1, 5) = Format$(D, "mm-dd")
        Else
            V(R) = False
        End If
    Next
        V = Filter(V, False, False)
    With [A1].Resize(UBound(V) + 1)
       .Value2 = Application.Transpose(V)
       .TextToColumns , 1, xlTextQualifierNone, , , True, , , , , , "."
    End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Ok, problem solved. Im not pasted this code which is executed after all

Please Login or Register  to view this content.

this replaced me the dates.

Thank you all for help