Read from text file

[drewduncan]

Can anyone help:

I want to read data from a csv file which will be used to create records within a table - called Jobs.

The files can have various rows BUT each row always has 13 columns. Each column or data separated by a comma will be entered into a different field within the Job Table.

One row from the text file will contain information such as RefNo,CustomerName,HouseNumber,StreetName,Town,PostCode etc

RefNo will be copied into the RefNo field of the Jobs table, CustomerName will be copied into the CustomerName field of the Jobs table and so on.
Any help on how to do this would be greatly appreciated.

Cheers

 

[bboffin]

See DoCmd.TransferText which will do just what you want.

 

[drewduncan]

OK think i understand what DoCmd.TransferText will do.

Is it possible however to state what field I want the data seperated by the commas to be transfered into?

e.g. The table that I am transfering the text file to has 32 fields. Only 13 of these fields will be given values from the text file?

 

[bboffin]

You can use the specificationname argument to do this but it's not the easiest thing to set up and it's not very well explained. Try producing a specification and see it it solves the problem.

It may be simpler to allow DoCmd.TransferText to create a table containing all the fields and then write an update query to transfer the columns you are interested in to your table.

 

[sha76]

If you first manually import your file then you will be able to set up an import specification (press the advanced button on the Import Text Wizard), as part of this you may choose to skip fields you don't require (NB Make sure you click Save As when you're done, I've made the mistake of just clicking OK & losing an hours work!). You can then reference this with Docmd.TransferText.