Import Specification Help

[Shaun Harwood]

Hello all

Here is a puzzle I hope someone has a solution for. I am trying to import
a delimited text file using a Visual Basic 'TransferText' command. So that
the table is created properly I have an Import Specification set up and it
seem to work ok. I have a new text file every day to load - when I have
finished using the information in it - I have a macro which deletes the
table. Here is my problem. I have set the specification so that the first
row of my Text File is used as field names. Five of the field names change
each day to reflect the next five days date. When I import the first time
it works - but on subsequent days my field names remain the same as the
first time. Is there a way to tell my code to reload the field names from
each new file?

I hope thats clear - gets a bit confusing. Any assistance would be
appreciated.

Thanks

Shaun H

[Benny Andersen]

Re: Import Specification Help

On Thu, 07 Apr 2005 00:52:58 GMT, Shaun Harwood
<Shaun_Harwood@ sympatico.ca> wrote:
[color=blue]
>Hello all
>
>Here is a puzzle I hope someone has a solution for. I am trying to import
>a delimited text file using a Visual Basic 'TransferText' command. So that
>the table is created properly I have an Import Specification set up and it
>seem to work ok. I have a new text file every day to load - when I have
>finished using the information in it - I have a macro which deletes the
>table. Here is my problem. I have set the specification so that the first
>row of my Text File is used as field names. Five of the field names change
>each day to reflect the next five days date. When I import the first time
>it works - but on subsequent days my field names remain the same as the
>first time. Is there a way to tell my code to reload the field names from
>each new file?
>
>I hope thats clear - gets a bit confusing. Any assistance would be
>appreciated.
>
>Thanks
>
>Shaun H[/color]


You can get the field names from the file's first line, create a table
using these and import without fieldnames

Sub import()
Dim line$, filename$
filename = ' something
Open filename For Input As #1
Line Input #1, line
line = Replace(line, """", "")
' removes surrounding textkvalifiers
line = "create table Mytable( " & _
Replace(line, ";", " text,") & " text)"
CurrentDb.Execu te line
Close #1
DoCmd.TransferT ext acImportDelim, "specname", _
"Mytable", filename, False
End Sub

--
Benny Andersen