Excel - Creating Tabbed Delimited Text File - Need help wi..

dza7 · External Since: Nov 04, 2009 Posts: 1

Hello,

I'm attempting to creating a tabbed delimited file from excel that is
going to be used for entering sale orders in an Oracle based
platform. The problem I'm running into right now is that I have
several blank cells in my excel spreadsheet that need to be shown as
"" in the text file when I "save as" to a txt file.

I've tried different methods and none of them have worked:
* I've tried changing all the blank cells to "". When "saved as" a
txt file those cells become """".
* I've tried changing the blank cells to a period, an apostrophe, a
space and none of those methods have worked either.

In the end I can edit this txt file in wordpad and replace the
incorrect characters but I'm hoping there is something I can change in
my excel template to help wit this process.

Thanks,

Dza

Franz Verga · External Since: Jun 21, 2006 Posts: 327

I think you can use this macro from Chip Pearson:

http://www.cpearson.com/excel/imptext.htm#Export

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy (now in Berlin)
dza7 wrote:
> Hello,
>
> I'm attempting to creating a tabbed delimited file from excel that is
> going to be used for entering sale orders in an Oracle based
> platform. The problem I'm running into right now is that I have
> several blank cells in my excel spreadsheet that need to be shown as
> "" in the text file when I "save as" to a txt file.
>
> I've tried different methods and none of them have worked:
> * I've tried changing all the blank cells to "". When "saved as" a
> txt file those cells become """".
> * I've tried changing the blank cells to a period, an apostrophe, a
> space and none of those methods have worked either.
>
> In the end I can edit this txt file in wordpad and replace the
> incorrect characters but I'm hoping there is something I can change in
> my excel template to help wit this process.
>
> Thanks,
>
> Dza

Dave Peterson · External Since: Jul 08, 2005 Posts: 16049

If you're willing to edit the tab delimited text file, then you could have used
the asterisk (or any unused character) and then replace that with "" in the text
file.

Open the .txt file in Notepad and then do the replacing.

dza7 wrote:
>
> Hello,
>
> I'm attempting to creating a tabbed delimited file from excel that is
> going to be used for entering sale orders in an Oracle based
> platform. The problem I'm running into right now is that I have
> several blank cells in my excel spreadsheet that need to be shown as
> "" in the text file when I "save as" to a txt file.
>
> I've tried different methods and none of them have worked:
> * I've tried changing all the blank cells to "". When "saved as" a
> txt file those cells become """".
> * I've tried changing the blank cells to a period, an apostrophe, a
> space and none of those methods have worked either.
>
> In the end I can edit this txt file in wordpad and replace the
> incorrect characters but I'm hoping there is something I can change in
> my excel template to help wit this process.
>
> Thanks,
>
> Dza

--

Dave Peterson

Daryl S · External Since: Nov 03, 2009 Posts: 4

Depending on how many columns you have and the size, you can use a column to
the right of your current data (or on another spreadsheet in the workbook) to
do the concatenation yourself. Then you only need to export the one new
column (or copy/paste if you like).

The formulas for the cells in your new column would look something like this:
=A4 & CHAR(9) & B4 & CHAR(9) & C4 & CHAR(9) & D4

Where CHAR(9) is the tab delimeter you want. If you need quotes around
text fields, you would add those in your formula also.

--
Daryl S

"dza7" wrote:

> Hello,
>
> I'm attempting to creating a tabbed delimited file from excel that is
> going to be used for entering sale orders in an Oracle based
> platform. The problem I'm running into right now is that I have
> several blank cells in my excel spreadsheet that need to be shown as
> "" in the text file when I "save as" to a txt file.
>
> I've tried different methods and none of them have worked:
> * I've tried changing all the blank cells to "". When "saved as" a
> txt file those cells become """".
> * I've tried changing the blank cells to a period, an apostrophe, a
> space and none of those methods have worked either.
>
> In the end I can edit this txt file in wordpad and replace the
> incorrect characters but I'm hoping there is something I can change in
> my excel template to help wit this process.
>
> Thanks,
>
> Dza
> .
>