Get data in csv file without opening it (have error).

[IceFrogBG]

Hi all,
This time I make VBA project to calculate data of many csv.
Someone give me idea to get data without open csv file (use ADODB).
But when I use have error.
My purpose : there are many file csv in a folder. But I only have to check some file
not all file, condition : file name is same as "17-06-**.csv"
(yy-mm-dd.csv format).
So please help me a method to solve this problem.
Thanks so much.

 

[Hui]

Try this:

Sub GetCsvData()
Dim ws As Worksheet
Dim fPath As String
fPath = Range("E19") 'Range(E19) is link include my csv file
Dim cn As Object, rst As Object
Dim l

Set cn = CreateObject("ADODB.Connection")
l = "16-08-11.csv"  ' --> example l is file name of csv file

On Error GoTo eh

With cn
  .Provider = "Microsoft.ACE.OLEDB.12.0"
  .ConnectionString = "Data Source=" & fPath & ";" & _
  "Extended Properties=""text;HDR=NO;FMT=Delimited;Imex=1;ImportMixedTypes=Text;"""
  .CursorLocation = 1
  .Open
End With

  strQuery = "SELECT * From [" & l & "]" ' ---> It can not open l
 'strQuery = "SELECT t2.[F1], t1.[F2], t1.[F3], t1.[F4], t1.[F5] From [16-08-11.csv] as t1 " & _
  ' "RIGHT OUTER JOIN [16-08-11.csv] as t2 " & _
  ' " ON t2.[F1] = t1.[F1];"

Set rst = CreateObject("ADODB.Recordset")
 rst.Open strQuery, cn, 1, 3

 Range("A1").CopyFromRecordset rst


 rst.Close
 cn.Close

eh:

End Sub

Note that if the file doesn't exist you need to allow for error handling

 

[IceFrogBG]

Thank Hui so much.
I will try on next week on my computer at company.
It is useful.

 

[Hui]

I also changed the line strQuery=

 

[IceFrogBG]

Hello Hui,
First of all, I want to say thank you for you because support me solve the problem.
Now I continue speed up excel file and make it more effective.
In my code, after check file name in folder, get data inside.
I have to calculate this data,
So are there any method to calculate data of csv file (do not import to excel file)?
Because when I listed folder (include sub folder) in source folder, there are over 500 folder.I have to check each folder,so it lost long time.

Sub GetCsvData(ByVal Link As String, ByVal year, month As Integer)
Dim ws As Worksheet
Dim fPath As String
Dim fso As Object
Dim mySource As Object, file1 As Variant
Dim myObject As Object
fPath = Link 'Range(E19) is link include my csv file
Dim cn As Object, rst As Object
Dim l As String
Dim stt, Pass, NG, Total, LastRow, n As Integer


Set fso = CreateObject("Scripting.FileSystemObject")
Application.DisplayAlerts = False
toolsname = ThisWorkbook.Name
Fder = Link
u = Right(Fder, 1)
If u = "\" Then
  Folder = Fder
Else
  Folder = Fder & "\"
End If
If Fder = "" Then
  Exit Sub
Else
Set mySource = fso.GetFolder(Folder)
  For Each file1 In mySource.Files
      l = Dir(file1)
      'If CInt(Left(l, 2)) = year And CInt(Mid(l, 4, 2)) = month Then
      ' file name is yy-mm-dd.csv
      If CInt(Left(l, 2)) = year And CInt(Mid(l, 4, 2)) = month Then
      stt = CInt(Mid(l, 7, 2))
      Set cn = CreateObject("ADODB.Connection")
'l = "16-08-11.csv"  ' --> example l is file name of csv file
'l = file
On Error GoTo eh
With cn
  .Provider = "Microsoft.ACE.OLEDB.12.0"
  .ConnectionString = "Data Source=" & fPath & ";" & _
  "Extended Properties=""text;HDR=NO;FMT=Delimited;Imex=1;ImportMixedTypes=Text;"""
  .CursorLocation = 1
  .Open
End With
  strQuery = "SELECT * From [" & l & "]" ' ---> It can not open l
'strQuery = "SELECT t2.[F1], t1.[F2], t1.[F3], t1.[F4], t1.[F5] From [16-08-11.csv] as t1 " & _
  ' "RIGHT OUTER JOIN [16-08-11.csv] as t2 " & _
  ' " ON t2.[F1] = t1.[F1];"
Set rst = CreateObject("ADODB.Recordset")
rst.Open strQuery, cn, 1, 3
Range("O1").CopyFromRecordset rst
rst.Close
cn.Close

                    Pass = 0
                    NG = 0
                    Total = 0
                    LastRow = Cells(Rows.Count, "O").End(xlUp).Row
                    For n = 9 To LastRow
                    If Cells(n, 15) = "PASS" Then
                    Pass = Pass + 1
                    End If
                    If Cells(n, 15) = "FAIL" Then
                    NG = NG + 1
                    End If
                    Total = Pass + NG
                    Next
                    Cells(stt + 5, 6) = NG
                    Cells(stt + 5, 7) = Total
        'Workbooks(toolsname).Sheets("LINK FOLDER").Cells(stt + 5, 5) = NG
        'Workbooks(toolsname).Sheets("LINK FOLDER").Cells(stt + 5, 6) = Total
'Purpose is calculate how many PASS and FAIL each file csv

End If
Application.DisplayAlerts = False
    Columns("O:O").Select
    ActiveWindow.ScrollColumn = 209
    Columns("O:HS").Select
    Selection.ClearContents
     
     
    Next
' I try to add this command to speed up code
Application.DisplayAlerts = False
End If

eh:

End Sub

 

[Hui]

Can you please start this as a new post

Also explain what you mean by "calculate this data"
What do you mean by that?
Is it for each file separately, or totals for all files

Please also attach some sample files

 

[IceFrogBG]

Dear Hui,
Can I continue this post?
I want to count how many cell is "PASS" or "FAIL".
Please you see attach picture for my explain.
Thanks for support.

 

[Chihiro]

No matter how you do it. Reading files from 500 separate folders and sub-folders is going to take time. Consider restructuring your document organization.

I.E. put all file into single folder. Use folder as pseudo database, using each csv as table.

You can then use single connection to folder/csv and use UNION and/or IN statement. Alternately use PowerQuery to merge all files within a folder.

 

[Hui]

Even though the files are related it is a different question

The reason to start a new post is that not everybody reads every post, especially when it gets past 2 or 3 responses and hence only a few people will read this and hopefully give you answers

Most people will read a new post and you will get a broader spread of answers

I think after answering 10,480 posts I know how to run this place as efficiently for you guys as possible