14,534
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
Expand|Select|Wrap|Line Numbers
- '-------------------------------------------------------------------------
- ' 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
- '-------------------------------------------------------------------------
