Import csv (Comma Separated Value) File

[MMcCarthy]

This is a module that imports information to a Query or a Table from comma separated values file in a text format! It is very helpful for getting information from other applications and from files!

The function that does this work is: import_csv_file_to_SQL(File_name, SQL, Optional text_delimiter, Optional Field_delimiter)

Parameters:

File_name: The file name of the respective file from which should be done the import. Should be the name of a real existing file. Here you can also specify the path of the respective file

SQL: A SQL expression, name of a query or a table where the information will be saved! The order of the columns is very important! The first value will be in the first column, the second in the second one and so on! So take care to arrange your columns so to fit to the imported information! Please use updatable SQL expressions! IF not the operation should be with import error!

Text_delimiter: Not obligatory! Sometimes, some programs, to distinguish the strings put before and after them “. Instead of importing the “ symbol in the respective field in Access the function can remove all sorts of similar symbols that shouldn’t be in the database. This argument can be left blank, if the function is run from Visual Basic! Running it from a query instead of blank use an empty string “”

Field_delimiter: Not obligatory! Specifies the delimiter of the fields. If it is ommited the default is ,

The study of this module can teach you:

• Creating functions in Visual Basic with arguments
• Declaring and using Optional arguments in functions
• Declaring variables
• Opening files using Scripting.FileSystemObject
• Working with the file like as stream
• Stream properties and behaviour
• Working with arrays
• Opening recordsets into a database
• Accessing all fields into a recordset and assigning them a value

'-------------------------------------------------------------------------
' This code is written by Vladimir Dimitrov
' It is not to be altered or distributed,
' except as part of an application.
 
          ' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of Vladimir Dimitrov
'
'------------------------------------------------------------------------
Function import_csv_file_to_SQL(File_name, SQL, Optional text_delimiter, Optional Field_delimiter)
On Error Resume Next
Dim mydb As Database
Dim myr As Recordset
Dim line
Dim i
Dim frfile
Dim myupd_last, myupd_current
 
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
 
Dim fs, f
Dim ts
Dim myarray, myarrct
 
 
 
   If IsMissing(Field_delimiter) = True Then Field_delimiter = ","
    If IsNull(Field_delimiter) = True Then Field_delimiter = ","
 
 
    Set mydb = CurrentDb()
    Set myr = mydb.OpenRecordset(SQL)
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(File_name)
    Set ts = f.OpenAsTextStream(ForReading, TristateUseDefault)
 
                Do While Not ts.AtEndOfStream
        line = ts.ReadLine
        myarray = Split(line, Field_delimiter)
        myarrct = UBound(myarray)
        myr.AddNew
        i = 0
 
          Do While (i < MinNumb(myr.fields.Count, myarrct + 1))
            ' Stop            
            If IsMissing(text_delimiter) Then
                myr(i) = myarray(i)
            Else
                If IsNull(text_delimiter) = True Then
                    myr(i) = myarray(i)
                Else
                    myr(i) = remove_symbol(myarray(i), text_delimiter)
                End If
            End If
 
                i = i + 1
 
        Loop
 
              myr.Update
        myarray = Nothing
        myarrct = 0
 
      Loop
 
        Set fs = Nothing
    Set f = Nothing
 
 
    ts.Close
    myr.Close
    mydb.Close
 
    import_csv_file_to_SQL = True
 
End Function
 
'------------------------------------------------------------------------
Function MinNumb(a, b)
Dim aa As Double
Dim bb As Double
 
    If a < b Then
        MinNumb = a
    Else
        MinNumb = b
    End If
 
End Function
 
'------------------------------------------------------------------------
Function ns(Stri) As String
On Error Resume Next
Dim result As String
 
 
    If IsNull(Stri) Then
        result = ""
    Else
        result = Stri
    End If
 
      ns = result
 
 
End Function
 
 
'-----------------------------------------------------------------------
Function remove_symbol(Stri, Sym)
On Error GoTo err_symbol
Dim result
 
      result = Stri
 
      If (result = "") Or (IsNull(result)) Then Exit Function
 
      Do While ((Mid(result, 1, 1) = Sym) Or (Mid(result, Len(result), 1) = Sym))
        If Mid(result, 1, 1) = Sym Then
            result = Mid(result, 2, Len(result) - 1)
        Else
            If Mid(result, Len(result), 1) = Sym Then
                result = Mid(result, 1, Len(result) - 1)
            End If
        End If
    Loop
 
      remove_symbol = result
 
  err_symbol:
 
        remove_symbol = result
    Exit Function
 
  End Function
 
  '-------------------------------------------------------------------------