Generate csv file in with cells = text format

[fsqueeen]

I'm using perl script to generate a csv file. When I opened the csv file with Microsoft Excel, right click on any column, click on Format Cells, on Number tab, the Category has 'General' selected.

I wonder if there's any way to format all cells in 'Text' format when I generate this csv from my perl script.

I know perl has a module Text::CSV_PP can format the column type (for e.g. integer column, two double columns and a string column can be set like below)

$csv->types([Text::CSV_PP::IV(),
Text::CSV_PP::NV(),
Text::CSV_PP::NV(),
Text::CSV_PP:V()]);

but, I don't want to use this module and looking for any simpler way to do it.

Anyone has idea... pls guide me... thanks in advance!

$csv = "col1,col2,col3\n";
$csv .= "aaa,bbb,ccc\n";
print "Content-Type: text/plain\nContent-Disposition: attachment; filename=\"$filename.csv\"\n\n";
print $csv;

 

[KevinADC]

I have no clue what you are asking.

 

[rharsh]

If you want to define the data types/formats for each column, you'll probably want to use the text import wizard. I don't think there's a simple way to automate that wizard.

An easier way might be to find one of the modules on CPAN for creating a spreadsheet and skip writing the csv file.

 

[zbnet]

If you rename the file from .CSV to .TXT, then when you open it in Excel you will get the Text Import Wizard by default (opening a CSV file doesn't invoke the wizard, and Excel decides for itself - based on contents - what format each cell should be: it doesn't always guess correctly!). It is then a few simple steps to configure all the columns as type Text.

 

[stevexff]

I'd go with zbnet's solution. You may still have problems with embedding newlines in the data to format the text, however.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[Rieekan]

Alternately, you could use the Spreadsheet::WriteExcel module to generate your file into an XLS document directly. The module is pretty straight forward and is in a very stable state (2.16 is the last version I believe).

CSV files do not have formatting at all as they are just text files. When Excel (or other spreadsheet software) opens these, there are some default assumptions made by the application as to the type of data the cell contains.

- George