import text file 1

[BrockLanders]

Hi, can anyone help me with some quick and dirty code to import a text file into an Access db, which allows me to name each field as it's imported? I've looked at the TransferText method of the DoCmd, but it doesn't look like you can name the fields, other than the first row of the file.

Thanks in advance

 

[jebry]

Hi!

Just create the table first and import the file into the existing table.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[BrockLanders]

i want to change the name of the fields each week when i import new data. one of the field names will be the current week.

i'm not looking for ready made code how to perform this, just something to get me started - specifically what coding just to import data and name fields. i can figure out the weekly naming.

any help would be much appreciated.

thanks

 

[jebry]

Hi!

What you want to do then is to go through the import wizard and make the name changes in the wizard. When you have the names completed instead of continuing click on the Advanced button and click on Save as. This will bring up a dialog box which will allow you to input a specification file name. Then in the code you use:

DoCmd.TransferText acImportDelim, "YourSpecificationName", "YourTableName", "YourFileName"

Alternatively you can add a row of field names to your text file and do this:

DoCmd.TransferText acImportDelim, , "YourTableName", "YourFileName", True

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[BrockLanders]

the problem is once a week, i'll be importing 52 rolling weeks of data, with each column/field named one of those weeks.

for example, this week field 1 would be 6/18/05, field 2 would be 6/25/05, field 3 would be 7/2/05 and so on. however, next week field 1 will be 6/25/05, field 2 7/2/05, field 3 7/9/05 and so on. with an import spec, i'd have to change each field name (all 52 columns) every week.

is there a way to code field names other than using a spec?

thanks again

 

[jebry]

Hi!

Not to my knowledge. But, you can change the field names of a table using code. I'm a bit rusty on it, but you should be able to find it in the help files.

Also, like the second part of my post above indicates, you can include the field names in the first row of the text file. Then the final True in the following TransferText command will make Access read the first row as field names. But that could be just as tedius depending on where you are getting the text file.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[BrockLanders]

ok, i'll look into renaming the fields after it's already imported.

thanks for your time.

 

[CptCrusty1]

Jeff, the "specs" post is an older one, but since its searchable, it still helped me. Thanks a bunch...

Rich.