Import Spec - file with multiple delimiters   

I have a text file(.txt) in which the fields are seperated by #*# 
(combination of all 3). I have created an Import specification. But it 
accepts only one character to be given as a delimiter. Hence i gave just # 
(hash). I have used this in the VB code and everything is Ok. The problem is 
with such data. For eg : The title field has data....."#1 hits". In tis case, 
Access is taking this '#' as a delimiter and changing the contents of the 
column.

My question - Is there a way to tell access to look for a combination '#*#' 
as a delimiter?

Or any other work - around? BTW, i'm using Access 2003.

Any help is greatly appreciated.

Thanks,
-- 
BGud

date: Wed, 14 Nov 2007 23:28:01 -0800   author:   Kassa

Re: Import Spec - file with multiple delimiters   

As far as I'm aware, Access limits you to a single delimiter.

What you could do is import the data into a temporary table with single 
field, then use the Split function to divide it into its component parts and 
write to the final table.

Assuming you've got 5 delimited fields in the temporary field, your SQL 
would look something like:

INSERT INTO PermanentTable(Field1, Field2, Field3, Field4, Field5)
SELECT Split([OneField], "#*#")(0), Split([OneField], "#*#")(1), 
Split([OneField], "#*#")(2),
Split([OneField], "#*#")(3), Split([OneField], "#*#")(4)
FROM TemporaryTable

That will, of course, fail if there aren't 5 delimited fields in OneField. 
To ensure that you only do work with "proper" fields, you can use

INSERT INTO PermanentTable(Field1, Field2, Field3, Field4, Field5)
SELECT Split([OneField], "#*#")(0), Split([OneField], "#*#")(1), 
Split([OneField], "#*#")(2),
Split([OneField], "#*#")(3), Split([OneField], "#*#")(4)
FROM TemporaryTable
WHERE Len([OneField]) - Len(Replace([OneField], "#*#", "")) = 12



-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Kassa"  wrote in message 
news:BBBE35A4-D304-4ADF-8BCD-C486AF8DF110@microsoft.com...
>I have a text file(.txt) in which the fields are seperated by #*#
> (combination of all 3). I have created an Import specification. But it
> accepts only one character to be given as a delimiter. Hence i gave just #
> (hash). I have used this in the VB code and everything is Ok. The problem 
> is
> with such data. For eg : The title field has data....."#1 hits". In tis 
> case,
> Access is taking this '#' as a delimiter and changing the contents of the
> column.
>
> My question - Is there a way to tell access to look for a combination 
> '#*#'
> as a delimiter?
>
> Or any other work - around? BTW, i'm using Access 2003.
>
> Any help is greatly appreciated.
>
> Thanks,
> -- 
> BGud

date: Thu, 15 Nov 2007 16:53:55 -0500   author:   Douglas J. Steele