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

K

ker_01

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
 
M

macropod

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.
 
F

FatBytestard

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.
Click to expand...


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.
 
C

CellShocked

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
Click to expand...

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.
 
K

ker_01

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 said:
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 said:
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
Click to expand...

.
Click to expand...
 
A

Archimedes' Lever

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.


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 said:
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 said:
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
Click to expand...

.
Click to expand...
Click to expand...
 
M

macropod

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy Two Columns of Flat File to Final Excel File 0
Transfer flat file to a Shared folder. 3
Create a flat file in Excel 4
Trying to create a report that will list unmatched records by Ticket Primary Key 0
Create Tab delimited file with no quotes in Currency fields. 0
Piping data to other office applications 4
Getting Radio Buttons to work, that are inserted via AutoText 0
Best way to learn to programm with Excel 3

Top