Need importing macro to recognize #*# as delimiter

[Eric Bragas]

Hi Everybody,

I need to export a table of many columns to text from SQL Server, and
I'm checking that exported file in Excel. The problem is the
customer's required delimiter. They insist we use hash-star-hash (#*#)
as the delimiters in the final text file, and even after recording an
import in Excel and modifying the following "OtherChar" parameter as
shown below, the import still does not work correctly.

Workbooks.OpenText Filename:= "my_delimited_file.txt",
Origin:=-535, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlTextQualifierNone, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=
False, _
Space:=False, Other:=True,
OtherChar:="#*#",
FieldInfo:=Array(...

So apparently it's not as simple as replacing the OtherChar argument.

Looking for suggestions! Thanks,
-Eric B.

 

[Derick Beckwith]

I think the OtherChar has to be a single character for the delimiter to
work. I had a similar problem trying to import text output from a database
and had to use a single character delimiter for import to work. In your
case you could use a '#' as the delimiter and then delete the columns
containing the '*' chars.

 

[NickHK]

Eric,
Easiest would to export twice; once with "#*#" and once with an Excel
compliant delimiter. Would that satisfy your check ?

NickHK

 

[Eric Bragas]

This idea will work for me, since all I'm doing is validating what's in
the file. The format is important to the customer, but it's only the
data I care about. I'm going to go with your suggestion. Thank you.