Excel format for CSV data 1

[razalas]

Not sure if I should post this on Microsoft Office forum, but I think someone here might be better able to understand what my issue is.

Am creating a CSV text file from an RM/Cobol program running on Unix (SCO V.5.0.6). CSV file is then attached to an e-mail text file and sent out to various clients. That way when client receives the e-mail, they can click on the attachment and open it by default in Excel.

This is working great, except for certain values that are not formatted properly by Excel. For example, we have a SKU named "MAR06" which Excel interprets as a date (Mar-06) when it displays it.

Does anyone know if there is some kind of formatting code that I can add to my CSV text file to tell Excel to treat this piece of data in a particular manner?

For instance, something like: text("MAR90") to indicate that MAR90 is text and not a date?

Code what you mean,
and mean what you code!
But by all means post your code!

Razalas

 

[mrregan]

Hi,
Put a space in front of the sku.

 

[razalas]

mrregan,

thanks for the suggestion. That seems to work when I try it, but it means that I'd really have to do that for every SKU field being written out, which I'd rather not do. But it may come to that.

It has also been suggested that we name the attachment as a ".txt" file. Except this requires the user to explicitly open the attachment with Excel (or program of choice) and go through a wizard to determine how to handle the data. That's ok too, but we were just trying to make this as simple as possible.

But of course, nothing is ever simple, is it?

Code what you mean,
and mean what you code!
But by all means post your code!

Razalas

 

[fredericofonseca]

When you open the file with Excel you can select the type of fields each columns is.
Select the column containing that data and choose "Text".

It will work fine then.

If this is a common file, always with the same format, then I recomend creating a small Excel macro to open the file and do the formating required in one go. This macro can then be associated with a button on your toolbars.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[k5tm]

Razalas! Still doing CSV!

I am not an Excel expert, but if your clients are using reasonably recent versions of Excel, you could use the Liant XML Extensions to export your data as an XML document (which Microsoft calls SpreadsheetML). This would allow you to control virtually everything about the Workbook, including the format of cells.

This is a reasonably straightforward process, and Liant Technical Support can help you achieve your results quickly.

So, this actually can be simple!

Tom Morrison

http://www.liant.com

 

[razalas]

Federico,

yes, if the attachment has the ".txt" extension, then you will go through the data wizard with the opportunity to determine the format for each data column.

but if the attachement has the ".csv" extension, you don't go through the data wizard when you open the file. I suppose there is some setting somewhere that controls this, but I am trying to leverage the default action of opening a csv file without going through the wizard in order to make this as simple as possible for my intended recipients (all of whom are external clients).


Code what you mean,
and mean what you code!
But by all means post your code!

Razalas

 

[razalas]

Tom,

yes, this (and bunch of other stuff) would be a lot easier if done with XML.

Unfortunately, we are not at a point (yet) where that's a viable option.

Hopefully soon!

Code what you mean,
and mean what you code!
But by all means post your code!

Razalas

 

[razalas]

Did a Google search and read thru a bunch of forums. Found one thread that suggested formatting the field in the csv file as

="MAR60",...

Tried this with much success and elation!

Thanks for all the suggestions.

Code what you mean,
and mean what you code!
But by all means post your code!

Razalas

P.S. Tom... still hoping to convince mgmt to buy XML Extensions for RM/Cobol...

 

[Crox]

I prefer the SYLK format. You can make a complete excel sheet with many functionalities. The extension is SLK. It is told before...

 

[razalas]

Crox,

thanks for the info on SYLK format. Seems like it's a bigger hammer than necessary for the nail we just pounded down. (Hope that makes sense).

But it may very well come in handy in the future. Hopefully, I can try it out soon (when I'm not busy working on XML).

Code what you mean,
and mean what you code!
But by all means post your code!

Razalas