Importing data then appending to a table

[thalam]

Hi there,

I'm trying to import data (CSV File), which will contain only two fields (ID Number, Comment). I want to create a macro or perhaps VB code which will simply import the two fields into a table and simply enter today's date in the date field and write the comment that I'm importing into the table associated by the ID.

I've been looking at the DoCmd.Transfertext command, but I can't quite wrap my head around the appending portion of the task. If anyone has any suggestion it would be greatly appreciated.

Thanks

 

[Tony1258]

One option that you can do is import the file into a table and the next step will to be run a append query from the import table to the table you want. In the append query you can have the date field assign the current date. finally in the macro I would run a delete table to get rid of the imported table. Try it.

Tony

 

[Fonz]

An alternative to the Docmd.Transfertext

This is VBA code that gives you total control of the import.
Click "New" on the Forms and select Design View

Let us assume your table (for data import) is called MyComments and it has 3 fields:
ID Number (Property - Number/ Long Integer),
Comment (Property - Text / 255 characters),
LastUpdate -(Property- Date/Time , Format: General date)

On the Form - put
ONE Unbound Text Field (Name: txtImport, Caption of label: Import File Path/name) and
One Button (Name:cmdImport)

I assume that the File contains data similar to the following:

1, "Bla, bla, bla..."
2, "text, text, ...."
3, "Comment, Comment...."


On the Click Event of the Button insert the following code

Sub cmdImport_Click()
Dim db as Database, rt as Recordset, fsource as String, mrec as String
Dim newVar as String


If isnull(txtImport) Then
MsgBox "You MUST enter the Import File Path/Name!"
txtImport.SetFocus
Exit Sub
Else
If Dir(txtImport) = "" Then
MsgBox "You MUST enter a valid pre-existing Filename/path"
txtImport.SetFocus
txtImport = ""
Exit Sub
End If
End If

Docmd.Hourglass True
fsource = txtImport

Set db = CurrentDB
Set rt = db.Openrecordset("MyComments")

Open fsource For Input As #1
Do While Not EOF(1)
Line Input #1, mrec
rt.AddNew
rt![ID Number] = Mid(mrec, 1, Instr(1,mrec, ",")-1)
newVar = Trim(Mid(mrec, Instr(1,mrec, ",")+1, Len(mrec)))
rt!Comment = newVar
'Insert date & time - to insert Date ONLY, replace with Date()
rt!LastUpdate = Now()
rt.Update
Loop
Close(1)
rt.close
Set rt = nothing
Set db = nothing

Docmd.Hourglass False
MsgBox "Import Complete!"

End Sub

Now COMPILE and check no errors, then run Form, enter a valid file and path name like : C:\MyImport.CSV

Click the Button and Voila!

Regards
Mark

 

[dsigner]

I find it much easier to go from CSV file via Excel which is much less picky about details than Access. Also you can see very clearly what is going on.
Insert a first line into the spreadsheet with field names which match your Access field names exactly. You can perform any transforms if the data is not in the exact form which you want by creating calculation columns. If you do this just make sure that it is the final form column which has the actual Access field name attached.

When you append the spread sheet with a query Access will automatically match the fields for you.

 

[dsigner]

I find it much easier to go from CSV file via Excel which is much less picky about details than Access. Also you can see very clearly what is going on.
Insert a first line into the spreadsheet with field names which match your Access field names exactly. You can perform any transforms if the data is not in the exact form which you want by creating calculation columns. If you do this just make sure that it is the final form column which has the actual Access field name attached.

When you append the spread sheet with a query Access will automatically match the fields for you.

 

[thalam]

Thanks a lot for the code!! It really helped me in the right direction. Now I'm just trying to create a file browser which will allow the user to go and point at the directory instead of manually typing it.

Thanks again!!

 

[KeithG]

To create your file browser you will need to use the FileSystemObject.

 

[thalam]

Thanks a lot! That bit of information was helpful for putting me into the right direction to come up with some answers.