By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 974 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Import csv (Comma Separated Value) File

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Expand|Select|Wrap|Line Numbers
  1. '-------------------------------------------------------------------------
  2. ' This code is written by Vladimir Dimitrov
  3. ' It is not to be altered or distributed,
  4. ' except as part of an application.
  5.  
  6.           ' You are free to use it in any application,
  7. ' provided the copyright notice is left unchanged.
  8. '
  9. ' Code Courtesy of Vladimir Dimitrov
  10. '
  11. '------------------------------------------------------------------------
  12. Function import_csv_file_to_SQL(File_name, SQL, Optional text_delimiter, Optional Field_delimiter)
  13. On Error Resume Next
  14. Dim mydb As Database
  15. Dim myr As Recordset
  16. Dim line
  17. Dim i
  18. Dim frfile
  19. Dim myupd_last, myupd_current
  20.  
  21. Const ForReading = 1, ForWriting = 2, ForAppending = 3
  22. Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
  23.  
  24. Dim fs, f
  25. Dim ts
  26. Dim myarray, myarrct
  27.  
  28.  
  29.  
  30.    If IsMissing(Field_delimiter) = True Then Field_delimiter = ","
  31.     If IsNull(Field_delimiter) = True Then Field_delimiter = ","
  32.  
  33.  
  34.     Set mydb = CurrentDb()
  35.     Set myr = mydb.OpenRecordset(SQL)
  36.     Set fs = CreateObject("Scripting.FileSystemObject")
  37.     Set f = fs.GetFile(File_name)
  38.     Set ts = f.OpenAsTextStream(ForReading, TristateUseDefault)
  39.  
  40.                 Do While Not ts.AtEndOfStream
  41.         line = ts.ReadLine
  42.         myarray = Split(line, Field_delimiter)
  43.         myarrct = UBound(myarray)
  44.         myr.AddNew
  45.         i = 0
  46.  
  47.           Do While (i < MinNumb(myr.fields.Count, myarrct + 1))
  48.             ' Stop            
  49.             If IsMissing(text_delimiter) Then
  50.                 myr(i) = myarray(i)
  51.             Else
  52.                 If IsNull(text_delimiter) = True Then
  53.                     myr(i) = myarray(i)
  54.                 Else
  55.                     myr(i) = remove_symbol(myarray(i), text_delimiter)
  56.                 End If
  57.             End If
  58.  
  59.                 i = i + 1
  60.  
  61.         Loop
  62.  
  63.               myr.Update
  64.         myarray = Nothing
  65.         myarrct = 0
  66.  
  67.       Loop
  68.  
  69.         Set fs = Nothing
  70.     Set f = Nothing
  71.  
  72.  
  73.     ts.Close
  74.     myr.Close
  75.     mydb.Close
  76.  
  77.     import_csv_file_to_SQL = True
  78.  
  79. End Function
  80.  
  81. '------------------------------------------------------------------------
  82. Function MinNumb(a, b)
  83. Dim aa As Double
  84. Dim bb As Double
  85.  
  86.     If a < b Then
  87.         MinNumb = a
  88.     Else
  89.         MinNumb = b
  90.     End If
  91.  
  92. End Function
  93.  
  94. '------------------------------------------------------------------------
  95. Function ns(Stri) As String
  96. On Error Resume Next
  97. Dim result As String
  98.  
  99.  
  100.     If IsNull(Stri) Then
  101.         result = ""
  102.     Else
  103.         result = Stri
  104.     End If
  105.  
  106.       ns = result
  107.  
  108.  
  109. End Function
  110.  
  111.  
  112. '-----------------------------------------------------------------------
  113. Function remove_symbol(Stri, Sym)
  114. On Error GoTo err_symbol
  115. Dim result
  116.  
  117.       result = Stri
  118.  
  119.       If (result = "") Or (IsNull(result)) Then Exit Function
  120.  
  121.       Do While ((Mid(result, 1, 1) = Sym) Or (Mid(result, Len(result), 1) = Sym))
  122.         If Mid(result, 1, 1) = Sym Then
  123.             result = Mid(result, 2, Len(result) - 1)
  124.         Else
  125.             If Mid(result, Len(result), 1) = Sym Then
  126.                 result = Mid(result, 1, Len(result) - 1)
  127.             End If
  128.         End If
  129.     Loop
  130.  
  131.       remove_symbol = result
  132.  
  133.   err_symbol:
  134.  
  135.         remove_symbol = result
  136.     Exit Function
  137.  
  138.   End Function
  139.  
  140.   '-------------------------------------------------------------------------
  141.  
  142.  
Jan 8 '07 #1
Share this Article
Share on Google+