Import CSV file w/o field names into table w/field names

Well- are you doing it only once, or a number of times?  To do it once, it is certainly easiest just to use the import method, to do it a number of times, transfertext works... docmd.transfertext in VBA... You may also need to create an import specification, which is done using the import wizzard, and then use that as a part of transfer text...

Mike

If the TransferText function works, why don't you just delete the content of the table before you do the import?

currentdb.Execute "DELETE * FROM tblYourTableHere"

HTH,

Michael

TransferText is designed to handle CSV files

what do you mena about append?

it adds records to a table if thats what u mean
do you want to clear the contents beforehand or what

One way would be to open the file csv file in excel
then use the File|Get external data|import function ( youcan import into an existing table or a new one.

Good Luck,

/Joe

It will be done weekly, which is why I'm trying to get it automated.  I created an import spec, but the import spec automatically assigns field names (which can be changed), but I have over 120 fields that I don't really want to assign.  Any ideas???

THANKS!

well I suppose broesi thinks the same as me, and broesi has given u an example

First you need to create an import specification.
Do the tthe first transfer of data using

File>Get External Data>Import
Select  from Files of type      Text type
Locate where your csv files and select
Click import
Just follow the instructions on the Import text wizard.
On the last window, when Next is grayed select Advanced ,
this will take you to the Import Spec window, save the spec.


now you can use the import spec using

    DoCmd.TransferText acImportDelim, "ImportSpec", "TableName", "C:\FileName.CSV"

While doing the import using the wizard, do not select First row contains Field Names

What would like to try is to use an import spec and transfer text to get the data imported with the auto-assigned generic field names (Field1, Field2, etc.).  Then could I append this to a blank table with assigned field names such as Name, Address, etc. using vba.  Remember that there are 120 fields, so I'm looking for some sql or something that will force Field1 into Name, Field2 into Address, etc.  

another thing

    DoCmd.TransferText acImportDelim, "ImportSpec", "TableName", "C:\FileName.CSV", False,""
^^^  set this to false to indicate no field names

I have tried this code (slightly altered for my purposes):
DoCmd.TransferText acImportDelim, "ImportSpec", "TableName", "C:\FileName.CSV", False,""

But my import spec is has my fields named Field1, Field2, Field3, etc. and my table has fields named Name, Address, Phone, etc.  Even with the FALSE I still get a "Field (Field1) doesn't exist in table" error message.

Also, I really appreciate all the quick help!  THANKS.

Is it possible to have an sql statement force the first field of one table into the first field of another and so on without having to name the fields???

This will generate the same error as when I try the TransferText...
DoCmd.RunSQL ("INSERT INTO CustomNameFs SELECT GenericNameFs.* FROM GenericNameFs")

I believe when you link to a csv file without a fieldname header, Access assigns the names Field1, Field2, etc. You would have to create the query and save it, then do the temporary link with the name TempTable and then the append query takes care of the rest.

I agree with GRayl. Just import the file into a temp table, using default options which should generate field names like Field1, field2... field100 ... fieldx, then use a query to fetch data from temp to the table you want to import into - should look something like:
insert into DestinationTable (employeeNo, address, tel, email ... ) select (field1, field2, ... fieldx) from tempTable
it's true that the first time around it's gonna be really painful, but then the subsequent operations are much easier.

Have fun!

Y.C.

What I have gathered from this is that it is not possible to force data (originally 150k csv records w/ 120 fields) from the first field of one table into the first field of a different table without defining the field names (be it in a query, an import spec, or sql).  I went ahead and just defined all fields in the import spec.  Points to GRayL for the assistance.

Thanks.  By linking to the text file, Access assigned default Field Names consistently. Thus the same type of table next month will have the same name defaults. You need the append query to move the data over to your permanent table. You can reuse the append query as long as the field types and number do  not change.  Good luck.

Ray :)