Importing Delimited data

[Dorv]

Thanks in advance....

First off, I'm an OSX guy using Excel 2011.

My company uses a database that stores its data in delimited text files. The delimiter is ``. I've gone through the import wizard several times, and feel like I've got the right settings, but the output is off.

What I don't seem to be managing correctly is "empty" data fields.

As example

datafield1``datafield2````datafield4

Where I would expect to be, once important, a empty field between datafield2 and datafield4, the three pieces of data are compressed into three concurrent columns.

Is there something obvious that I'm missing in my import?

(Then, once I've gotten the settings correct, is there any way to automate the process via macros or Applescript?)

 

[aravindhan_31]

would you be able to send the sample to aravindhan_31@yahoo.com

 

[Dorv]

Let me go through the effort of scrubbing some of the detail, as it has personal data.

 

[Dorv]

The root cause of my trouble is the fact that we utilize DOUBLE backticks as the delimiter. I can only put one in the "other" field in the import wizard.

When I get to the option of how to handle consecutive delimiters, treating them as individual generates an extra column in between every column with data. Treating "consecutive as one" is great for handling THAT problem, but I then run into issues when there is no data in a prescribed field. "Consecutive as one" means that `` is a single delimiter, but then so is ```` or ``````.

Any obvious workaround that I'm missing?

 

[tushiroda]

possibly you can just take 1 extra step and use SUBSTITUTE to switch all of the "``" to "`" and then run the text to columns