473,288 Members | 2,725 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,288 developers and data experts.

Import csv (Comma Separated Value) File

MMcCarthy
14,534 Expert Mod 8TB
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
0 18199

Sign in to post your reply or Sign up for a free account.

Similar topics

26
by: Christina | last post by:
I have a post-form that holds a listbox with mulitple selected items. When the form is posted to the server ASP file, I want to loop through the selected items, to insert each of them into a table....
8
by: Johnny | last post by:
I was looking for information on how to import an excel file or other text file into a MySql database. I have done this with both access and SQL Server and am looking for a way to do this in...
1
by: DCM Fan | last post by:
Access 2K, SP3 on Windows 2K, SP4 All, I have an import spec set up with quoted Identifiers and comma-separated values. The text file is produced by a 3rd-party program of which I have no...
20
by: Steve Jorgensen | last post by:
Hi all, I've just finished almost all of what has turned out to be a real bear of a project. It has to import data from a monthly spreadsheet export from another program, and convert that into...
0
by: Shawn Ferguson | last post by:
With the help of you, I've been able to successfully load my database with the contents of a "comma" delimited file. It loads about 5000 records in about 3 seconds:) Now I have another problem. ...
17
by: mac | last post by:
Hi, I'm trying to write a fibonacci recursive function that will return the fibonacci string separated by comma. The problem sounds like this: ------------- Write a recursive function that...
13
by: mac | last post by:
Hi, I'm trying to write a fibonacci recursive function that will return the fibonacci string separated by comma. The problem sounds like this: ------------- Write a recursive function that...
1
by: Nashwa24 | last post by:
Hi all, i'm very new to access and VB so please need your help. can i Import csv (Comma Separated Value) File by browsing my computer instead of specifying an exact file path for the file ??? ...
4
by: sufian | last post by:
Below is the field where user enters his/her email address and the AJAX post request is sent to the server and the user sees the message: echo("<div id=\"message\" class=\"success\">Thank you! You...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.