Microsoft Access DoCmd.TransferText additional column

Question

I'm using DoCmd.TransferText to import data from .cvs file. I need add another column with current date to imported information. Is there any simple way to do this?

Answer 1

There's a few approaches you can use:

-Parse the file and create INSERT statements via VBA instead of using TransferText.

-If the table already exists, you're appending data to an existing table, and you have an auto-number ID field or some other way to distinguish new data from old data then you can add the current date after importing via an UPDATE statement:

Dim latestID As Long

latestID = DMax("yourIDField", "yourTable")
DoCmd.TransferText acImportDelim, , "yourTable", "c:\import.csv", True

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE yourTable SET importedDate = #" & Date & "# WHERE yourIDField > " & latestID
DoCmd.SetWarnings True

-If a new table is being created each time you import, you can use an ALTER statement to add the column then an UPDATE to enter the current date:

DoCmd.TransferText acImportDelim, , "yourTable", "c:\import.csv"

DoCmd.SetWarnings False

DoCmd.RunSQL "ALTER TABLE yourTable ADD COLUMN importDate DATE"
DoCmd.RunSQL "UPDATE yourTable SET importDate = #" & Date & "#"

DoCmd.SetWarnings True

Answer 2

You can use schema.ini instead transfer text method.

Check the following blog it describes more about schema.ini https://officeaccelerators.wordpress.com/2015/02/08/exporting-text-file-from-access/

Set cn = CreateObject("ADODB.Connection")
Set cn = CurrentProject.Connection
sSQL = "insert into TanleName(Field1,Field2,Date) select tab1.Field1,tab1.Field2,date() from "
sSQL = sSQL & "[text;database=FilePath\;HDR=Yes].[Test.csv] as Tab1"
cn.Execute sSQL