How to get data from *csv file.

[IceFrogBG]

hi all,
Now I make a tool to get some data from csv file (no need all data).
But my code may be can not run,
pls someone help me.

Sub GetCsvData(ByVal Link As String)
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
Dim toolsname, u, Fder, Folder, strQuery

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
      'If CInt(Left(l, 2)) = year And CInt(Mid(l, 4, 2)) = month Then
      If Right(l, 4) = ".csv" 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 [" & 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

 

[Hui]

IceFroqBG

A few issues:

I would use a different approach

Sub GetCsvData()

Dim fso As Object, mySource As Object, myObject As Object
Dim Fder As Variant, Folder As Variant, file1 As Variant

Set fso = CreateObject("Scripting.FileSystemObject")
Application.DisplayAlerts = False

Fder = "C:\Users\PC_1\Downloads" 'Link

On Error GoTo eh

If Right(Fder, 1) = "\" 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
    If Right(Dir(file1), 4) = ".csv" Then
   
        Dim sWhole As String
        Dim myArr As Variant
        Open file1 For Input As #1
        sWhole = Input$(LOF(1), 1)
        Close #1
        myArr = Split(sWhole, vbNewLine)
   
        Range("O1").Resize(UBound(myArr)).Value = Application.Transpose(myArr)
   
    End If
  Next
End If

eh:
Application.DisplayAlerts = True

End Sub

The next question is that if there are multiple CSV files in the directory, what is meant to happen to the second, third files?

Do you want them appended to the end of Column O or in new columns ?

 

[Hui]

Also in VBA when you use a Dim statement like:
Dim stt, Pass, NG, Total, LastRow, n As Integer

This is actually saying

Dim stt as Variant
Dim Pass as Variant
Dim NG as Variant
Dim Total as Variant
Dim lastrow as Variant
Dim n as Integer

 

[IceFrogBG]

Hello Hui,
Thank you so much for your reply.
Yes I have many file in a folder and I have to get some data inside each file.
after that list these data to one sheets I created (sheets "RESULT").
So can you view my excel file and give me some idea.
Thanks again.

IceFroqBG

A few issues:

I would use a different approach

Sub GetCsvData()

Dim fso As Object, mySource As Object, myObject As Object
Dim Fder As Variant, Folder As Variant, file1 As Variant

Set fso = CreateObject("Scripting.FileSystemObject")
Application.DisplayAlerts = False

Fder = "C:\Users\PC_1\Downloads" 'Link

On Error GoTo eh

If Right(Fder, 1) = "\" 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
    If Right(Dir(file1), 4) = ".csv" Then
  
        Dim sWhole As String
        Dim myArr As Variant
        Open file1 For Input As #1
        sWhole = Input$(LOF(1), 1)
        Close #1
        myArr = Split(sWhole, vbNewLine)
  
        Range("O1").Resize(UBound(myArr)).Value = Application.Transpose(myArr)
  
    End If
  Next
End If

eh:
Application.DisplayAlerts = True

End Sub

The next question is that if there are multiple CSV files in the directory, what is meant to happen to the second, third files?

Do you want them appended to the end of Column O or in new columns ?

 

[IceFrogBG]

Hi Hui,
Because the code can work so I don't edit some line code.
But can I define Dim stt as Variant
And what is different between "Dim stt as Variant" and "Dim stt as Integer"?

Also in VBA when you use a Dim statement like:
Dim stt, Pass, NG, Total, LastRow, n As Integer

This is actually saying

Dim stt as Variant
Dim Pass as Variant
Dim NG as Variant
Dim Total as Variant
Dim lastrow as Variant
Dim n as Integer

 

[Hui]

Effectively when you Dim a line you are telling VBA what type of data and hence how much memory to set aside for the storage of that variable

Boolean's only require 1 bit of information to set True/False
Integers require 8 bits
Double 16 Bits
Long 32 bits
String ...
Variants are a special case as they are semi flexible, in that they can resize on the fly, ie if a variant stores an Integer it may use it's smallest size, but if it stores an array or other objects they can be huge

This is also why there are the data type conversion functions like cstr() etc
because VBA doesn't know how to append two integers until you convert them both to strings =var1 & var2, or to add two strings = var1 + var2 will result in an error if both are strings

Variants can store any data and in these days of large cheap memory you will mostly get away with using variants for everything

Just remember that processing Integers is faster than processing Double, which is faster than processing Long and that is faster than processing Varaints
This is especially important where you are doing large loops within loops, as every milli second adds up

I have seen examples of poor programing and use of incorrect data types where a program was taking several minutes to process and after re-coding was literally taking 15-20 milli-seconds

 

[Hui]

Hello Hui,
Thank you so much for your reply.
Yes I have many file in a folder and I have to get some data inside each file.
after that list these data to one sheets I created (sheets "RESULT").
So can you view my excel file and give me some idea.
Thanks again.

Can you mark in the Column N which records go to which fields in the Results tab
eg like:

I suspect that there is some logic involved in which rows are which, please explain everything


Are the files always the same layout or is each imported file slightly different?

 

[IceFrogBG]

Hi Hui,
Because I'm not professional so some times I used Variants to define.
After you explain, with easy understanding if I define suitability
It can work faster and lost fewer of memory.

Effectively when you Dim a line you are telling VBA what type of data and hence how much memory to set aside for the storage of that variable

Boolean's only require 1 bit of information to set True/False
Integers require 8 bits
Double 16 Bits
Long 32 bits
String ...
Variants are a special case as they are semi flexible, in that they can resize on the fly, ie if a variant stores an Integer it may use it's smallest size, but if it stores an array or other objects they can be huge

This is also why there are the data type conversion functions like cstr() etc
because VBA doesn't know how to append two integers until you convert them both to strings =var1 & var2, or to add two strings = var1 + var2 will result in an error if both are strings

Variants can store any data and in these days of large cheap memory you will mostly get away with using variants for everything

Just remember that processing Integers is faster than processing Double, which is faster than processing Long and that is faster than processing Varaints
This is especially important where you are doing large loops within loops, as every milli second adds up

I have seen examples of poor programing and use of incorrect data types where a program was taking several minutes to process and after re-coding was literally taking 15-20 milli-seconds

 

[IceFrogBG]

Can you mark in the Column N which records go to which fields in the Results tab
eg like:
View attachment 49519
I suspect that there is some logic involved in which rows are which, please explain everything


Are the files always the same layout or is each imported file slightly different?

Hello Hui,
Thanks for your answer.
May be my question is not clear.
I have many files in a folder and I want to get some importance data of each file (model,ID,status,....)
If status is PASS : all sub field status is PASS and I want to get some data detail
If status is FAIL : I wan to know which sub field is FAIL
And all file is same format.
You can open 2 files which I attached (open by notepad) to see easily.

 

[Hui]

It is unclear which entries map to which fields in Records

 

[IceFrogBG]

It is unclear which entries map to which fields in Records

View attachment 49521

Dear Mr Hui,
Please see my excel file attached.
I wrote some explain in this file.
Thanks so much.

 

[IceFrogBG]

Hello everyone,
are there any idea for me?

 

[Marc L]

Hi !

At least crytal clear explain what you expect …​