ansaurus

Question

How to export 'date lookalike' text columns to csv

Answer 1

+1 A:

Put a single quote as the first character in the cell. Then it will be interpreted as text.

hughdbrown 2009-08-07 22:46:57

If I add single quote in the export code and open the csv file then the single quote is visible (e:g '5-8-13). But if I open the file without having single quote and then add single quote in each cell then your logic works. The excel file is quite large to modify individual cells. Also all cells in that column may not have data. Also each export may have different data but same columns. Is there a better way of doing this?

2009-08-09 14:12:51

If you are not modifying the data programmatically, then you are either modifying the data by hand or identifying the non-dates programmatically. It seems the latter is out, right? If you could identify which cells that look like dates aren't really dates, then you could run a VBA macro to mark the cells as text and you'd be set.

hughdbrown 2009-08-09 15:05:18

The problem is that I export my data to .csv file and send this file to the external user. This user is non tech person. I was thinking if there is a way to export data (.net code) in right format so that user doesn't have to write or run the macro. Thanks !

2009-08-09 15:49:39

Then I would do the change onsite and send the non-technical user an .XLS file, not a CSV file. Or you could figure out an alternative way to format your problematic data. Why is it 00-00-00?

hughdbrown 2009-08-10 02:24:23

It's clear from the question that the asker is exporting the data programmatically, not fiddling with it manually in Excel; therefore advice to add a single-quote isn't appropriate for the situation.

John Y 2009-08-12 04:53:20

Answer 2

+3 A:

There isn’t an easy way to control the formatting Excel applies when opening a .csv file.

In order to force Excel to maintain your data format, enter the data as follows:

="5-8-13"

It will display as 5-8-13 but the data in the cell will be stored as ="5-8-13"

Robert Mearns 2009-08-10 12:54:31

I'm upvoting this because it's probably good enough, and I really want it to overtake the single-quote answer.

John Y 2009-08-12 05:21:45

+1. see also: http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm#CSVAndExcel

Kobi 2009-09-08 06:58:21

Answer 3

A:

The answer from Robert Mearns works if there are no embedded quotation marks in the data you are exporting. This may be a fairly safe assumption if you are not experiencing any other troubles using CSV. (It gets more complicated if you need to protect literal commas, quotation marks, or newlines in the data.)

If you are dying to impress the end user, there are packages for generating native Excel documents (.xls) programmatically. I don't use .NET myself, so I don't know if there are any free ones for .NET. I use xlwt for Python (hmm... this would presumably work in IronPython), and there are some for Java, PHP, and Perl as well.

John Y 2009-08-12 05:19:42

Answer 4

A:

add "\t" to "5-8-13"

chiesa 2009-09-08 06:28:17

ansaurus

tags:

views:

answers:

How to export 'date lookalike' text columns to csv

related questions