Hi.
When using a tab delimited text file in a query, I've always had good luck
with a schema.ini file placed in the same directory as the text file, which
contains all of the formatting information needed. With a schema.ini file,
you could use the following syntax in your query to break that single column
into the multiple columns you were expecting:
SELECT *
FROM [TEXT;DATABASE=C:\Temp].RAWDATA.txt;
For the syntax in your schema.ini file, you'll find the specifications and
information for using text files with Access on these Web pages:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp
http://msdn.microsoft.com/library/d.../en-us/office97/html/workingwithtextfiles.asp
If you already have a table or query in your database that contains the
table structure (field names, data types, et cetera) you would like to use
for queries that use text files, then see this Web page for creating a
procedure that writes the schema.ini file for you:
http://support.microsoft.com/default.aspx?scid=155512
Or, if you already have a table or query in your database that contains the
table structure you would like to use for an import, and you'd rather avoid
VBA to create the procedure, then you could create a query and let Jet do
most of the work for you by using the following syntax:
SELECT tblMyStuff.* INTO [Text;DATABASE=C:\Temp].Output.txt
FROM tblMyStuff
WHERE (1 = 0);
Access will automatically create the Output.txt file with column names, but
no records from the table, as well as create the schema.ini file for you.
You can delete the Output.txt file, but you'll need to edit the schema.ini
file, so open it in a text editor and make the following changes (watch out
for word wrap):
FROM: TO:
[Output.txt] [RAWDATA.txt]
Format=CSVDelimited Format=TabDelimited
.... and any other customizations that you may need, then save the schema.ini
file. Now, the next time you need to use your RAWDATA.txt file in a query,
it will be properly formatted.
(NOTE: You could use "FMT=TabDelimited" in your export query, but Jet will
ignore it and give you the default anyway, so you'll just have to edit the
schema.ini file for any items you want to customize.)
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Any human can read my reply E-mail address and should alter it so that a
message will be forwarded to me. Spammers are free to use my UNALTERED
reply E-mail address. I will *never* get those messages!)
Bastian said:
Hi,
I have TAB delimited text file.
How could I read it directly from a ms access query ?
I have tried to used this sql statement:
SELECT * FROM
[TEXT;FMT=DELIMITED;HDR=YES;CHARACTERSET=437;DATABASE=C:\Te
mp\RAWDATA;].[Data#txt];
But what I got in the query, all the data is just in one
column.
Could someone help me ?
Thanks.
