Solved: Access 2007 CSV Import Utility

[lazyme]

Hi,

I am relatively new to VB and trying to write a CSV import utility that imports data in an Access database.

I have created a form in Access 2007 with two buttons browse and import. The browse button brings up a selection dialog to import the file and the import button imports the data from csv file to the database.

Here's how my code looks like.

Option Compare Database

Dim importFile As String

Private Sub BrowseButton_Click()

Dim fDialog As Office.FileDialog
   Dim varFile As Variant
   

   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      'Allow user to make multiple selections in dialog box.
      .AllowMultiSelect = False
            
      'Set the title of the dialog box.
      .Title = "Please select one file"

      'Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "Text files", "*.txt"

       If .Show = True Then
         'Loop through each file selected and add it to the list box.
         For Each varFile In .SelectedItems
         
         'TxtBoxImport has the location of the file to be imported
         TxtBoxImport.SetFocus
         TxtBoxImport.Text = varFile
         
         Next
         
      End If
   End With

End Sub

Private Sub ImportButton_Click()

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const ForReading = 1

Dim recordExits As Boolean
Dim noRecords As Integer
Dim logFile As String


Dim currentTime As String
Dim hour As String
Dim minute As String
Dim second As String

hour = DatePart("h", Now)
minute = DatePart("m", Now)
second = DatePart("s", Now)

currentTime = Format(Now(), "yyyymmdd")

'create logfile with timestamp
logFile = CurrentProject.Path & "\DataImportLog - " & currentTime & "_" & hour & minute & second & " PM.txt"
MsgBox (logFile)
Open logFile For Output As #1
'logfile to write logs into, just printed hello world atm
Print #1, "Hello world"
Close

Set objConnection = CreateObject("ADODB.Connection")
Set objrecordset = CreateObject("ADODB.Recordset")

'open connection to the database
objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = H:\MCA.mdb"

objrecordset.Open "SELECT * FROM MCATable", _
    objConnection, adOpenStatic, adLockOptimistic

Set objFSO = CreateObject("Scripting.FileSystemObject")

TxtBoxImport.SetFocus
importFile = TxtBoxImport.Text

'open the csv file to be imported
Set objFile = objFSO.OpenTextFile(importFile)

noRecords = 0

Do Until objFile.AtEndOfStream
'read from the text file
    strPatient = objFile.ReadLine
    attrPatient = Split(strPatient, ",")
    
    objrecordset.MoveFirst
    
    recordExits = False
    
    'check for duplicate records
    Do Until objrecordset.EOF
        If objrecordset!Name = attrPatient(0) Then
            recordExits = True
            Exit Do
        End If
        objrecordset.MoveNext
    Loop
    
    'if no duplicates, write record to file
    If recordExits = False Then
        noRecords = noRecords + 1
        objrecordset.MoveLast
        objrecordset.AddNew
        objrecordset("Name") = attrPatient(0)
        objrecordset("Department") = attrPatient(1)
        objrecordset.Update
    End If
Loop

answer = MsgBox("Successfully imported " & noRecords & " records. View Log?", vbInformation + vbYesNo, "Data Import Utility")

If answer = vbYes Then
    'implement the display of file
End If

objrecordset.Close
objConnection.Close

End Sub

The code works fine but I need help finding bugs in this. Also how can I do exception handling in VB say a csv with wrong format is selected the program must not terminate abruptly but handle the exception.

Also I get a Runtime error at times which says

"The database has been placed in a sate by user 'Admin' on machine 'machinename' that prevents it from being opened or locked"

On clicking debug it points to this line

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = H:\MCA.mdb"

Am I doing something wrong here? The error goes away once I restart access but reappears in a while.

Thanks.

[CBrine]

OK, couple of questions....
1. Are you trying to import the CSV file using the code from the importbutton_click? If so, I would suggest you research docmd.transfertext before you go any further.
2. Your connection string is trying to connect to itself I believe, and you cannot do that the way you are trying. Here's how I connect to the active database.

Dim db As Database, oConn As ADODB.Connection, rs As ADODB.Recordset
Dim sSql As String
Dim oldEmpID As String, oldDate As String, oldRate As String
Set db = CurrentDb
Set oConn = Application.CurrentProject.Connection
sSql = "select * from [TableName]"
Set rs = oConn.Execute(sSql)

HTH
Cal

[lazyme]

@CBrine - Yes I am trying to import the CSV file on import button click.

Will read up on docmd.transfertext. The file format i'm trying to import is like

"708-46-34","Walter,John","08/02/1985"

There is a separator ',' in the Name field. Also all fields are enclosed in double quotes. Can I still use Application.DoCmd.TransferText for this input?

Your code to connect to active database works but where do I specify the lock type? I tried adding records using this code and it said the lock type doesn't support updation.

Thanks

[CBrine]

You will need to setup a transfer specification, but once that is done, you can use the transfertext to import your data.

To adjust the record locking options, open the recordset with
rs.open(ssql,oConn,...)

instead of Set rs= ....

HTH
Cal

[lazyme]

Thanks guys, I got this working with Docmd.Transfertext. Now I want to check for duplicate rows in my import. I do not have any primary key in my tables and the record with all the field values same is considered a duplicate.

How can I check for duplicates using docmd.transfertext?

[geekgirlau]

I would suggest you do the following:

• Import your data using Docmd.TransferText into a temporary table
• Run a query to import from the temporary table to your production table only for those records that don't already exist in the production table
• Clear the temporary table

[CreganTur]

Thanks guys, I got this working with Docmd.Transfertext. Now I want to check for duplicate rows in my import.

I completely agree with geekgirl- using a temporary table and then querying the records is going to give you the most control. It adds a few steps, but they are necessary steps to ensure your data is clean.

I do not have any primary key in my tables

Why not? Primary Keys are extremely fundamental to good database design, because they block duplicates so easily. If you had a PK assigned to your table, then TransferText would only append the records that are unique- you wouldn't have to worry about the extra steps of a temp table and validation queries.

[CreganTur]

Just found out that this question is cross-posted over at VBForums. Lazy, please read this article on cross posting.

[lazyme]

@CreganTur, I only cross posted as I wanted more experts to read my problem and guide me but I will be more careful with it henceforth.

Why not? Primary Keys are extremely fundamental to good database design, because they block duplicates so easily. If you had a PK assigned to your table, then TransferText would only append the records that are unique- you wouldn't have to worry about the extra steps of a temp table and validation queries.

The database belongs to a local bookstore I work part time for and they have been maintaining it the same way since years I cant get them to change that.

I would suggest you do the following:

• Import your data using Docmd.TransferText into a temporary table
• Run a query to import from the temporary table to your production table only for those records that don't already exist in the production table
• Clear the temporary table

Do you mean I need to create a temporary table in VBA code? And then copy all the distinct rows from the temporary table to my main table?

I created a temp table and copied all the rows from the input file to it
[VBA]DoCmd.TransferText acImportDelim, , "temp", importFile, True[/VBA]

Now how do I copy data from this table to my main table "Labs"?

I also figured out the syntax for deleting the temp table
[VBA]DoCmd.DeleteObject acTable, "temp"[/VBA]

Now the only hurdle is copying distinct rows from "temp" to "Labs". Could someone please help me with this.

[geekgirlau]

I wouldn't actually delete the Temp table unless the structure is going to change. A better option is to create the table manually, and just delete all the records from it when you're done.

To copy the rows, you just need to setup an append query - the only code required is to run the query.

[lazyme]

Ok I will create and delete temp manually.

But I still can't figure out how to form the append query. Any hints please.

[geekgirlau]

What is the structure of your table?

Can you post a small sample database containing the production table, and the temporary table. Make sure there is only some dummy data included.

[CreganTur]

@CreganTur, I only cross posted as I wanted more experts to read my problem and guide me but I will be more careful with it henceforth.

You're allowed to cross post, it's just polite to let us know when you do, and to provide a link to your cross post- it allows us to see what others have done to help you, and it also keeps people from using their time to solve a problem that was already solved on another forum. That's the gist of the article I linked above.

Ok I will create and delete temp manually.

Don't delete the temp table manually. What she's saying is that you need to manually setup the table so you can know it's correctly formatted. You can use Delete queries to clear out all the records form the table in the future, so that you clear it in preparation for the next upload. Here's an example delete query:

[VBA]DELETE * FROM TableName[/VBA]
The asterisk tells SQL to select all records in the table. Replace TableName with the name of the actual table you're working with. This will clear out all records in the table.

But I still can't figure out how to form the append query. Any hints please.

This is where query design view can help you out tremendously. Add your temp table to the query design view, then click on the Query Type button and select Append Query- a dialog box will appear, asking which table you want to Append records to. After that, it's a matter of selecting the fields from the temp table you want to append, and assigning which field they correspond to in the target table.

Here's some SQL for a basic append query:
[VBA]INSERT INTO tblEmployees (LastName, FirstName)
SELECT tblImportedEmployees.LastName, tblImportedEmployees.FirstName
FROM tblImportedEmployees;[/VBA]

the first line defines the target table and the fields you want to fill with data. The second line selects the fields in theoriginal table that hold the data you want to append- notice they are in the same order as the target table's field list. The last line shows which table the data to append is coming from.

HTH

[geekgirlau]

As you can see from Randy's example, the append query itself is pretty straightforward. However in your case we need to see the structure of the table in order to exclude duplicate items. Your query needs to compare fields from your temporary table against your production table and only append records that do not exist in your production table.

[lazyme]

Ok. I will look into that.

Here's what I tried doing. I merge the all data into a single table and trying to delete all the duplicate records from the table.

Here's the code I wrote for it. I have 2 recordsets pointing to the same table and running the code like a nested for loop.

The outer for is objRecordsetDup and the inner is objRecordset. So I compare each row in objRecordsetDup to all the rows in objRecordset and then delete the duplicate rows.

The code works fine for a database with few rows but not for bigger databases. I am trying to figure out the problem with my logic. Can someone please check the logic for me.

If objRecordset.BOF And objRecordset.EOF Then
MsgBox "No records to process"
Else
objRecordsetDup.MoveFirst

Do Until objRecordsetDup.EOF
strDupName = objRecordsetDup.Fields(0) & objRecordsetDup.Fields(1) & objRecordsetDup.Fields(2)

objRecordset.MoveFirst
sameRecord = True
Do Until objRecordset.EOF
strSaveName = objRecordset.Fields(0) & objRecordset.Fields(1) & objRecordset.Fields(2)
If strDupName = strSaveName Then
If sameRecord = False Then
objRecordset.Delete
Else
sameRecord = False
End If
End If
objRecordset.MoveNext
Loop

objRecordsetDup.MoveNext

Loop

End If

[geekgirlau]

Hi Lazyme,

It would be much faster to just use a query to do this. As we've already stated, if you can post a small sample showing us the structure of your table, we can help you with the query.

[lazyme]

Hi geekgirl,

I do not understand what you mean by the structure of my table. Do you mean the Field names and their Data Types in my table?

In my case, a row is a duplicate if and only if all the field values are the same. Even if any single field value varies, the row is not a duplicate.

[geekgirlau]

Yep, field names and data types.

The simplest method is to import your table (structure, not data) into a brand new database and then add a few dummy records. Don't post real data such as customer names.

[lazyme]

Here is my table structure and a few dummy records.

[geekgirlau]

Hi Lazyme,

I was actually hoping you would attach the database file, not a picture of it!

Anyway, I've attached a file with the query - give it a run and see how you go. I'm assuming that the field names on the temporary table are identical to the production table.