Import CSV with memo fields into Microsoft Access tables

0
votes

Let me first say, that I am not a programmer, so I am not familiar with Visual Basic for Access (VBA). I understand queries and macros to a point where I can automate database transactions that can get me from point A t B.

I am looking to take a CSV file saved from Microsoft Excel and import them into Microsoft Access 2003 on a daily basis. The CSV file is a daily feed I receive from another company.

What can a non-programmer do inside Microsoft Access to import this data once a day?

My biggest issue is with memo fields. It seems many of the simple ways of importing data like linking tables truncates memo fields into the first 255 characters. I need to bring all the data.

Any tips, suggestions, or tested procedures would be helpful.

2
databaseexcelms-accesscsvexport
In Access, if you set the data type of a field to Text then you are limited to 255 characters. You would have to set the data type to Memo if you need to store more than 255 characters. Actually to import CSV files will create problems for you. You are better off importing a tab delimited text file instead. VBA is the best and easiest way to get the job done. - Linger
Assuming the table is already created with the correct data types, the import of the data still truncates to 255 characters for memo fields. - Sun

2 Answers

1
votes

Check out this might give you what you need.

0
votes

You could try something like this code below. Paste this into a module and make the necessary changes to fit your needs. Design a table with the fields you need and their type and reference it below. Create a form with a command button that will run an event procedure. Call the module you created. 

Public Sub ImportTextFile()
' to use the ADODB.Recordset, be sure you have a reference set to ADO
Dim rst As ADODB.Recordset
Dim strFile As String
Dim strInput As String
Dim varSplit As Variant
Dim intCount As Integer


  Set rst = New ADODB.Recordset
' CHANGE THE TABLE NAME HERE
rst.Open "TblNameHere", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
' CHANGE THE TEXT FILE NAME AND LOCATION HERE
strFile = "C:\Desktop\fullextract.txt"

Open strFile For Input As #1

Dim i As Integer

Do Until EOF(1)
   ' This counter is just to get to the applicable line before importing
   intCount = intCount + 1
   ' reads the text file line by line
   Line Input #1, strInput
   ' starts importing on the second line.  Change the number to match which line you
   ' want to start importing from
   If intCount >= 2 Then
       ' creates a single dimension array using the split function
       varSplit = Split(strInput, ",", , vbTextCompare)
       ' adds the record
       With rst
           .AddNew
       ' change the range 0 To 254 to fit your needs. I was importing 254 fields.
            For i = 0 To 254
                .Fields(i) = varSplit(i)
           Next i
           .Update
       End With

   End If
Loop
' garbage collection
Close #1
rst.Close
Set rst = Nothing

End Sub