How to set a delimiter when the data source is a text file (1 Viewer)

felipe

New member
Local time
Today, 02:45
Joined
Oct 6, 2010
Messages
1
Hi all,
I am making a query from a text file using the following statement:
SELECT * FROM
[Text;FMT=Delimited;HDR=Yes;DATABASE=C:\DirectoryPa th\;].[FileName#txt];

I want to set the delimiter of the file, for instance a semicolon, and I do not know how to do it. I have tried with the following statements, but without good results.

1. SELECT * FROM
[Text;FMT=Delimited(;);HDR=Yes;DATABASE=C:\DirectoryPa th\;].[FileName#txt];

2. SELECT * FROM[Text;FMT=Delimited";";HDR=Yes;DATABASE=C:\DirectoryPa th\;].[FileName#txt];

Any advice will be very helpful.

Thanks in advance,

Felipe.
 
Last edited by a moderator:

jdraw

Super Moderator
Staff member
Local time
Today, 05:45
Joined
Jan 23, 2006
Messages
15,364
Why are you trying to query the text file directly?
Why not use the import wizard and import your data to a temporary table, then use a standard Access query to get the data you need?

Search Access help for Import Wizard

There is some info here http://www.databasedev.co.uk/import-data-with-vba.html that may help.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:45
Joined
Jan 20, 2009
Messages
12,849
The delimiter cannot be set in the connection properties.
Use a schema.ini file in the same folder as the text file.

Put this in the schema.ini file

Code:
[filename.txt]
ColNameHeader=True
Format=Delimited(;)

The FROM statement can be cut back to:
[Text;DATABASE=C:\path].[filename.txt]
 

Users who are viewing this thread

Top Bottom