471,353 Members | 1,711 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,353 software developers and data experts.

CSV Files

I've got a project that I'm sending output to a CSV file.

Some of the fields have commas in them and some of the fields have spaces.

When I attempt to open the file with a double click or Process.Start, the
default application opens it: MS Excel. This is fine.

However, every time Excel opens one of these files, it defaults to a "Fixed
Width" data type.

I have tried encapsulating each of my cells in quotes, and I have replaced
all of the commas with dots, but neither has solved this problem.

I understand how to go into the Text Import Wizard of Excel to set the data
type as a delimited file using commas, but this app is going out to
supervisors and managers in our plant that aren't so good with computers.

Is there some setting that I can use to ensure that my files default as a
delimited file without physically creating a proprietary MS Excel file?

I can not replace the spaces in the cells, because those are required to
have a proper model numbers in our company.

I appreciate any help.
Jul 17 '08 #1
8 1678
On Thu, 17 Jul 2008 14:50:01 -0700, jp2msft
<jp*****@discussions.microsoft.comwrote:
[...]
Is there some setting that I can use to ensure that my files default as a
delimited file without physically creating a proprietary MS Excel file?
I have to say, I don't see anything in your question that technically is
related to C#, .NET, or programming at all for that matter. You seem to
be asking about how _Excel_ treats specific text file formats, and that's
a question that you'd get a _much_ better answer for in a newsgroup
specifically about Excel.

Now, that said...based on what I know about Excel, I believe that there's
nothing you can do about the default way it will open a CSV file. You'll
have to go through the Text Import Wizard, or at the very least code up a
macro that your clients can use to open the file (I think it's possible
you could write an add-in that would watch for text files being opened,
and then run the Text Import on the newly opened file to do what you want,
but don't hold me to that :)...in any case, if you did that, your clients
would have to install the add-in for any Excel installation they wanted to
work that way).

Does the file have to be CSV? If you wrote the data out as an XML file,
it's a bit easier for Excel to figure out what you want. It has a default
XML import mode that I've found works very nicely for database-like data.

Pete
Jul 17 '08 #2
Off topic, btw.
Commas nor spaces shouldn't be a problem if you have quoted the field;
do you have a short (say, 2 line, 6 cell) example of something that
loads incorrectly? It could be a setting on your machine?
Alternatively note that some (not all) European versions of Excel
default to using period for the delimiter (gotta love i18n....).

Marc
Jul 17 '08 #3
fwiw, CSV is a standardized format... RFCs and all...

http://www.fileformat.info/format/csv/internal.htm

Before I found that site, though... I opened Excel, manually entered a few
rows/cols of data, using just about every combination of comma's and quotes
I could think of and exported that from Excel to a CSV file.... finally,
inspecting the file and duplicating its format in code, using the same data
I originally entered into Excel... didn't stop until they matched <g>

But... iirc, ADO has built in CSV file I/O, so ADO.Net should (VB6 dev here,
just poking around the groups temporarily). I wrote my own from scratch as a
"drop-in" class, so I didn't need to package the ADO components, or anything
else... same with the limited XML stuff I do.... a single "drop-in" class
replaces all XML components I'd need to package.
"jp2msft" <jp*****@discussions.microsoft.comwrote in message
news:BE**********************************@microsof t.com...
I've got a project that I'm sending output to a CSV file.

Some of the fields have commas in them and some of the fields have spaces.

When I attempt to open the file with a double click or Process.Start, the
default application opens it: MS Excel. This is fine.

However, every time Excel opens one of these files, it defaults to a
"Fixed
Width" data type.

I have tried encapsulating each of my cells in quotes, and I have replaced
all of the commas with dots, but neither has solved this problem.

I understand how to go into the Text Import Wizard of Excel to set the
data
type as a delimited file using commas, but this app is going out to
supervisors and managers in our plant that aren't so good with computers.

Is there some setting that I can use to ensure that my files default as a
delimited file without physically creating a proprietary MS Excel file?

I can not replace the spaces in the cells, because those are required to
have a proper model numbers in our company.

I appreciate any help.

Jul 17 '08 #4
Do you have a "go to" link for the XML creation?

I've seen how to create XML files online and in books, but what I'd like to
see is how to setup the nodes and such. (I've never actually had an
opportunity to generate an XML file)

Would the XML file still be saved as a CSV file or would I give it an XML
extension?

"Peter Duniho" wrote:
Does the file have to be CSV? If you wrote the data out as an XML file,
it's a bit easier for Excel to figure out what you want. It has a default
XML import mode that I've found works very nicely for database-like data.

Pete
Jul 18 '08 #5
Yeah, Pete indicated to me it was off topic too. Sorry, but I didn't feel the
Excel guys would want to talk to a software developer.

Here are a couple of files:
http://www.joeswelding.biz/test/DataGrid_Viewer.csv (larger)
http://www.joeswelding.biz/test/Pack...o_07172008.csv (one line only)

"Marc Gravell" wrote:
Off topic, btw.
Commas nor spaces shouldn't be a problem if you have quoted the field;
do you have a short (say, 2 line, 6 cell) example of something that
loads incorrectly? It could be a setting on your machine?
Alternatively note that some (not all) European versions of Excel
default to using period for the delimiter (gotta love i18n....).

Marc
Jul 18 '08 #6
On Jul 17, 5:50*pm, jp2msft <jp2m...@discussions.microsoft.comwrote:
I've got a project that I'm sending output to a CSV file.

Some of the fields have commas in them and some of the fields have spaces..
Go to opennetcf.org and download theirs provider. You can create (and
consume) .csv files like a charm. No point in creating a new parser
Jul 18 '08 #7
Now, that said...based on what I know about Excel, I believe that there's*
nothing you can do about the default way it will open a CSV file. *
My bet is that the OP got wrong the csv creation
Jul 18 '08 #8
On Fri, 18 Jul 2008 06:38:04 -0700, jp2msft
<jp*****@discussions.microsoft.comwrote:
Do you have a "go to" link for the XML creation?

I've seen how to create XML files online and in books, but what I'd like
to
see is how to setup the nodes and such. (I've never actually had an
opportunity to generate an XML file)
I think that the documentation for the XmlWriter class would be a good
place to start:
http://msdn.microsoft.com/en-us/libr...xmlwriter.aspx
Would the XML file still be saved as a CSV file or would I give it an XML
extension?
It can have whatever extension you want (my recollection is that Excel
detects the XML from the header), but ".xml" is probably the best choice.
The ".csv" extension would definitely be inappropriate, even if it still
worked.

That said, others have suggested that Excel will in fact default to
comma-delimited fields if you format your CSV file correctly. If you can
get Excel to generate a CSV file that it then reads in correctly, then I
would think that would be an indication that those suggestions are
correct. In that case, you should just look at the difference between
what Excel is writing and what you're writing and fix the differences so
that you're following what Excel expects.

Pete
Jul 18 '08 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

18 posts views Thread by JKop | last post: by
3 posts views Thread by pooja | last post: by
7 posts views Thread by Corepaul | last post: by
18 posts views Thread by UJ | last post: by
reply views Thread by wal | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.