VBA code to copy only 4 columns data from csv file

Question

I need to copy only the first 4 column only from C:\KOMDATA\MachineList.csv file into a empty table "MachineData" (except the first row which is header).

Note: The table data type matches to the csv cell data type. (like text to text and number to number and the table does not have primary key)

Note2: File .csv data row of these column may be increased and decreased.

Purpose of this is that I will be receiving daily the csv file, which will have only 4 columns with the header which I want to copy into a blank table via code.

I will be making the table empty by code before running this code of copying, so that my data will be refereshed of daily, the quanity of data is more than 1000 records a day, therefore we are keeping 1 day data only

Please advice VBA to do this.

Regard.

Irshad

Answer 1

You can check out the code yourself by manually creating a query in Design View then checking out SQL View.

Once you have your linked table, Open Query Design mode and select the linked table (the csv). The select the four columns. Then change the query to an Append query. When you do that you can choose to append to a table in another database. You can then use the Brows button to select the mdb file and table you want to append to. 

If you then Switch to SQL View, you will see that the path to the external file is listed in an IN clause. If you copy and paste the SQL statement into your VBA module, it should work.

Answer 2

You need to first link to the file (See TransferText), then once linked, you can generate and Execute a SQL Append statement. So it would look something like this:

Dim strSQL As String

DoCmd.TransferText......

strSQL = "INSERT INTO MachineData (field1, field2, field3, field4) " & _

               "SELECT field1, field2, field3, field4 " & _

                "FROM linkedtable;"

CurrentDB.Execute strSQL, dbFailOnError

Answer 3

That's because you have the wrong value for filename. The Syntax for TransferText is:

TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)

So your TransType (acImportDelim) is correct. So is your spec name (MachineList Link Specification" at least I assume its correct). But the next parameter is the name of table not the path to a file. So it should be MachineList since that's how you refer to it in your SELECT statement. So you code should be:

Private Sub Command4_Click()

Dim strTablename As String

Dim strSQL As String

strTablename = "MachineList"  'Its table name where the csv data will come and linked

DoCmd.TransferText acImportDelim, "MachineList Link Specification", strTablename, "C:\KOMTRAXDATA\MachineList.csv", True

strSQL = "INSERT INTO Table1 IN 'C:\KOM\TestA.mdb' " & _

                "SELECT * " & _

                "FROM MachineList IN 'C:\KOM\KomDataLink.mdb';"

CurrentDb.Execute strSQL, dbFailOnError

End Sub

Answer 4

Sir,

By the help of your advise, I was able to transfer data from 1stmdb to 2ndmdb by staying in the 3rdmdb. I run the query from the 3rdmdb and was sucessful :

INSERT INTO Table1 IN 'C:\KOM\TestA.mdb'

SELECT *

FROM MachineList IN 'C:\KOM\KomDataLink.mdb';

But I am unable to populate the 1stmdb as the below code does not work :

Dim filename As String

filename = "C:\KOMDATA\KomDataLink.mdb.MyFile2" 

DoCmd.TransferText acImportDelim, "MachineList Link Specification", filename, "C:\KOMTRAXDATA\MachineList.csv", True

Please advice.

Regards.

Irshad

Answer 5

Sir,

Thanks for your reply, I tried the code and it worked perfectly if the code is being used staying inside in the mdb.

But the actual requirement is changed now, that the code will run from another mdb to carry out this operation. I mean the user will run the code from his mdb "MainUser", so that the data will be copied from the csv file and and create the table in a mdb "TestA", which is there but not opened.

I tried the below code but it fails with the error -"Run time errror 2006" - Object name naming rules related error

Private Sub Command4_Click()

Dim filename As String

Dim filename2 As String

filename = "C:\KOM\TestA.mdb.MyFile2"  'Its table name where the csv data will come and linked

filename2 = "C:\KOM\TestA.mdb.Table1" 'Its table name where later it will be inserted

DoCmd.TransferText acImportDelim, "MachineList Link Specification", filename, "C:\KOMTRAXDATA\MachineList.csv", True

Dim strSQL As String

strSQL = "INSERT INTO filename2 (Model, SerialNo, Classification1, SMR,NightLock ) " & _

               "SELECT Field1, Field2, Field3, Field4, Field5 " & _

                "FROM filename;"

CurrentDb.Execute strSQL, dbFailOnError

End Sub

Please advice, how to correct my code to solve this.

Regards

Irshad