Best way to format a flat file that Excel will load properly?
 

I'm generating a flat file in Outlook VBA. Right now I'm able to save it as a
..txt file, although I hope to save it in a delimited flat file format with an
..xls extension so it will automatically be opened by excel without having to
go through the whole 'import' sequence.

There are string fields in the file, some of which contain commas- so I
can't make it a comma delimited file.

What is the best delimiter to use so that Excel will automatically recognize
and parse the flat file contents across columns?

Currently in 2003, but strongly prefer solutions that will also work in 2007

Many thanks,
Keith

Best way to format a flat file that Excel will load properly?
 

Hi ker_01,

You can use a csv file by enclosing each field that might contain commas in double quotes.

Applying an xls extension to a text file does not a valid Excel file make and is liable to generate an error message when Excel
tries to open it. Plus, if you then proceed to open the file, all the data will probably be in one column.

--
Cheers
macropod
[Microsoft MVP - Word]


"ker_01" wrote in message ...
I'm generating a flat file in Outlook VBA. Right now I'm able to save it as a
.txt file, although I hope to save it in a delimited flat file format with an
.xls extension so it will automatically be opened by excel without having to
go through the whole 'import' sequence.

There are string fields in the file, some of which contain commas- so I
can't make it a comma delimited file.

What is the best delimiter to use so that Excel will automatically recognize
and parse the flat file contents across columns?

Currently in 2003, but strongly prefer solutions that will also work in 2007

Many thanks,
Keith

Best way to format a flat file that Excel will load properly?
 

On Sat, 9 Jan 2010 14:51:17 +1100, "macropod"
wrote:

Hi ker_01,

You can use a csv file by enclosing each field that might contain commas in double quotes.

Applying an xls extension to a text file does not a valid Excel file make and is liable to generate an error message when Excel
tries to open it. Plus, if you then proceed to open the file, all the data will probably be in one column.


Using a sig AND being a top poster is a RETARDED combination as your
sig causes the message you responded to to get snipped.

You're an idiot. Either STOP using a sig as they are NOT even needed,
or STOP top posting, friggin idiot.

Best way to format a flat file that Excel will load properly?
 

On Fri, 8 Jan 2010 16:55:01 -0800, ker_01
wrote:

I'm generating a flat file in Outlook VBA. Right now I'm able to save it as a
.txt file, although I hope to save it in a delimited flat file format with an
.xls extension so it will automatically be opened by excel without having to
go through the whole 'import' sequence.

There are string fields in the file, some of which contain commas- so I
can't make it a comma delimited file.

What is the best delimiter to use so that Excel will automatically recognize
and parse the flat file contents across columns?

Currently in 2003, but strongly prefer solutions that will also work in 2007

Many thanks,
Keith

If you are creating the text file, and you get to insert the
delimiters, then you can choose a character other than a comma, no
problem.

When you go to import it into an excel workbook (yes, this step is
required), you choose the delimiter that you constructed the text file
with, and it will import it perfectly every time as long as the character
you chose does not "pop up" in the data strings you are trying to import.

So, choose carefully.

Best way to format a flat file that Excel will load properly?
 

Hi FatBytestard,

Have a nice day!

--
Cheers
macropod
[Microsoft MVP - Word]


"FatBytestard" wrote in message ...
On Sat, 9 Jan 2010 14:51:17 +1100, "macropod"
wrote:

Hi ker_01,

You can use a csv file by enclosing each field that might contain commas in double quotes.

Applying an xls extension to a text file does not a valid Excel file make and is liable to generate an error message when Excel
tries to open it. Plus, if you then proceed to open the file, all the data will probably be in one column.


Using a sig AND being a top poster is a RETARDED combination as your
sig causes the message you responded to to get snipped.

You're an idiot. Either STOP using a sig as they are NOT even needed,
or STOP top posting, friggin idiot.

Best way to format a flat file that Excel will load properly?
 

On Mon, 11 Jan 2010 12:14:50 +1100, "macropod"
wrote:

macropod

What an idiot.

Best way to format a flat file that Excel will load properly?
 

Thank you to macropod and Cellshocked for your responses.

I am now saving the file as a txt file, and have used commas to delimit the
file, and surrounded string fields with quotes so that 'accidental' commas
won't throw off the format.

When I right-click the file and say "open with Excel", it still throws each
entire row into column A.

Are there any tutorials on how to manually format a file output to be more
Excel-friendly, even (gack) if it means throwing it into XML format?

My alternative is to have Outlook create an instance of Excel, and dump the
contents directly into Excel instead of a flat file... but I'd be treading in
unfamiliar waters, given that this needs to work in a mixed 2003/2007
environment.

Thank you!!
Keith

"macropod" wrote:

Hi ker_01,

You can use a csv file by enclosing each field that might contain commas in double quotes.

Applying an xls extension to a text file does not a valid Excel file make and is liable to generate an error message when Excel
tries to open it. Plus, if you then proceed to open the file, all the data will probably be in one column.

--
Cheers
macropod
[Microsoft MVP - Word]


"ker_01" wrote in message ...
I'm generating a flat file in Outlook VBA. Right now I'm able to save it as a
.txt file, although I hope to save it in a delimited flat file format with an
.xls extension so it will automatically be opened by excel without having to
go through the whole 'import' sequence.

There are string fields in the file, some of which contain commas- so I
can't make it a comma delimited file.

What is the best delimiter to use so that Excel will automatically recognize
and parse the flat file contents across columns?

Currently in 2003, but strongly prefer solutions that will also work in 2007

Many thanks,
Keith

.

Best way to format a flat file that Excel will load properly?
 

It is not "right click and select open with excel".


You OPEN Excel, and select the "Data" tab and select "from text"

(you could record these moves in a macro too)

Also, your file should be a .csv file, NOT a .txt file, though either
will work.

csv is "comma separated values". If you already have a pre-parsed
file, give it the pre-parsed file extension name.

Anyway, opening Excel first and performing an import is the right way,
especially if you have several fields to define.


On Mon, 11 Jan 2010 13:03:02 -0800, ker_01
wrote:

Thank you to macropod and Cellshocked for your responses.

I am now saving the file as a txt file, and have used commas to delimit the
file, and surrounded string fields with quotes so that 'accidental' commas
won't throw off the format.

When I right-click the file and say "open with Excel", it still throws each
entire row into column A.

Are there any tutorials on how to manually format a file output to be more
Excel-friendly, even (gack) if it means throwing it into XML format?

My alternative is to have Outlook create an instance of Excel, and dump the
contents directly into Excel instead of a flat file... but I'd be treading in
unfamiliar waters, given that this needs to work in a mixed 2003/2007
environment.

Thank you!!
Keith

"macropod" wrote:

Hi ker_01,

You can use a csv file by enclosing each field that might contain commas in double quotes.

Applying an xls extension to a text file does not a valid Excel file make and is liable to generate an error message when Excel
tries to open it. Plus, if you then proceed to open the file, all the data will probably be in one column.

--
Cheers
macropod
[Microsoft MVP - Word]


"ker_01" wrote in message ...
I'm generating a flat file in Outlook VBA. Right now I'm able to save it as a
.txt file, although I hope to save it in a delimited flat file format with an
.xls extension so it will automatically be opened by excel without having to
go through the whole 'import' sequence.

There are string fields in the file, some of which contain commas- so I
can't make it a comma delimited file.

What is the best delimiter to use so that Excel will automatically recognize
and parse the flat file contents across columns?

Currently in 2003, but strongly prefer solutions that will also work in 2007

Many thanks,
Keith

.

Best way to format a flat file that Excel will load properly?
 

I guess that's really a compliment from someone who wants to go down in history as FatBytestard!

--
Cheers
macropod
[Microsoft MVP - Word]


"FatBytestard" wrote in message ...
On Mon, 11 Jan 2010 12:14:50 +1100, "macropod"
wrote:

macropod

What an idiot.