DoCmd.TransferText 1

[lbigk]

Hi,

I am importing a text file into Access 97 using TransferText. Everything works fine, but it only wants to import data into a single field. I think, the problem comes from the delimiter used: ~ .

Does anyone knows how to identify the delimiter in the code and not a manual import?

Thank you for your help.

 

[RobertT687]

The easiest way would be to create an import specification that uses the '~' as the delimiter. By default Access is looking for (, ; {tab} or {space}) as delimiters.

 

[lbigk]

Thank you for the reply. I guess, I wasn't specific enough. That's exactly what I was asking about, how to specify the delimiter as "~".

Thank you.

 

[RobertT687]

No problem.
File > Get External Data > Import
Specify file type (Txt)
Select a sample of your file. Click ‘Import’
On the Import Text Wizard Screen Click the ‘Delimited’ radio button.
Click ‘Advanced’
On the Import Specification screen, click in the ‘Field Delimiter’ box. Overtype the default ‘,’ with ‘~’.
Click ‘Save As’ and give the specification a meaningful name. Click OK.
Click OK on the Import Specification screen.
Click ‘Advanced’ again to see what Access created.
Here you can give the fields meaningful names and even mark fields for skipping. To save changes, click ‘Save As’ and overwrite your spec.
Click OK until you’re back on the Import Text Wizard Screen.
Click ‘Next’ and follow the instructions.

Now, your code would appear something like this:
DoCmd.TransferText acImportDelim, “yourspecnamehere”, destinationtablename, inputfilename

 

[lbigk]

Thank you!