Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I generate a CSV file with an extension .csv in which every piece of data in one line is separated with a comma:

1,2,3,4
1,2,3,4

The file is sent via email and I want that when a customer opens it she sees data arranged into columns.

Is such thing possible?

PS: I may freely change the delimeter.

share|improve this question
1  
As far as I am concerned, you can't do anything except for importing the data into Excel, since Excel can't make any assumptions about the structure of the data itself (e.g. the separator used, the format of decimals or the general format of data). –  slhck Jan 28 '11 at 14:32
    
@slhck please look at the answer –  Tim Jan 28 '11 at 14:39
    
Oh, I didn't know that Excel was capable of doing this. Nice one. –  slhck Jan 28 '11 at 14:41
    
By just double click the CSV file, Excel will be opened and the file will be displayed correctly. Am I correct? –  wilson Jan 31 '11 at 6:17
    
@wilson: Excel has to be associated to open .csv files then by double clicking a .csv file excel will be launched –  Tim Jan 31 '11 at 16:33
add comment

2 Answers

up vote 1 down vote accepted

Just use tab instead of comma. And if that doesn't work, give your tab delimited file an xls extension.

share|improve this answer
    
thanks for the solution, I left the .csv as file extension –  Tim Jan 28 '11 at 14:38
add comment

The behavior of Excel when opening CSV files heavily depends on your local settings and the used list separator under Region and language » Formats » Advanced. By default Excel will assume every CSV was saved with that separator. Which is true as long as the CSV doesn't come from another country!

If your customers are in other countries, they may see other results then you think.

For example, here you see that a German Excel will use semicolon instead of comma like in the U.S enter image description here

To confuse you even more, that setting interferes with the decimal symbol which can be separately set up under Excel Options » Advanced » Use system separators or via regional settings as shown above. Excel can't use the same symbol as decimal tab and list separator. It will automatically use comma or semicolon as a backup separator. Read more

I will take your example to create 3 files. Each with a different separator and open it with Excel.

    COMMA               SEMICOLON                     TAB 

enter image description hereenter image description hereenter image description here

Not the same as your Excel does? I thought so.

So lets manually change the extension from the same CSV files to XLS and look what happens now. First, Excel will throw up a warning that the file extension doesn't match the content and Excel tries to guess whats inside.

    COMMA        SEMICOLON                   TAB 

enter image description hereenter image description hereenter image description here

Conclusion: TAB + renaming + ignore warning = Win on every system?
Maybe, but I wouldn't be so sure for customers outside your own country.

The best method is to instruct your customers to first open a blank Excel and then go to Data » Get data from text and manually select comma as separator

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.