Import CSV file as new table

[gg80]

I have a user that sends me his data as a csv file (downloaded from his registration site). Unfortunately, there are more column heads than are needed for our Access program and the number and header names of those "extra" columns can vary. I thought that this would be no problem as I could just import his CSV file as is, creating a table with the same name as the CSV file. Then use an append query to select the field names that are needed for the Access program. (Those field names don't change)

I created an import spec that works fine when selected manually, but am having trouble when trying to automate with VBA. I am using :
DoCmd.TransferText acImportDelim, "ImportSpec", ,strPath & strFile



According to help screen, table name is optional, so left that space blank between commas. Yet, I get a message that I need a table name.

I am using A2003 and A2007. Any help much much appreciated.

[ssanfu]

According to A2000 help, the table name and the file name are the two arguments that are required.

This is the command I use to import a CSV file:

DoCmd.TransferText acImportDelim, "AIQImport", "tblAddsEdits", strFileName, True

The CSV format is fixed though. I don't allow the client to change the CSV format.

You could try linking to the CSV file, then running the append query. Un-link the CSV file when done.

[gg80]

According to help in A2007, table name is is optional. Since I can do the operation the way I want with one click after seleting the spec name, it seems that there should be a way to do same thing with VBA. But, this isn't the first time the help was wrong. I will experiment with linking. Since the csv file is replaced with each download, I will have to try to do a link/unlink with vba. Possible?

Thanks for quick reply.

According to A2000 help, the table name and the file name are the two arguments that are required.

This is the command I use to import a CSV file:

DoCmd.TransferText acImportDelim, "AIQImport", "tblAddsEdits", strFileName, True

The CSV format is fixed though. I don't allow the client to change the CSV format.

You could try linking to the CSV file, then running the append query. Un-link the CSV file when done.

[ssanfu]

Yes. This works for me

CurrentDb.TableDefs.Delete "YourfileName"

[gg80]

I did the link method and works fine. Actually, I think that I will do it this way for all my other applications. Keeps the clutter out of the file

I also found that I could import the csv file and create a new table. If you put the new table name per below, it imports fine. (The importSpec imports csv data to a new table.) Seems obvious now.


DoCmd.TransferText acImportDelim, "ImportSpec", "NewTableName", strPath & FileName

According to help in A2007, table name is is optional. Since I can do the operation the way I want with one click after seleting the spec name, it seems that there should be a way to do same thing with VBA. But, this isn't the first time the help was wrong. I will experiment with linking. Since the csv file is replaced with each download, I will have to try to do a link/unlink with vba. Possible?

Thanks for quick reply.