SOLVED

Importing Text Files: Tab-deliminated text files vs CSV's

%3CLINGO-SUB%20id%3D%22lingo-sub-2667065%22%20slang%3D%22en-US%22%3EImporting%20Text%20Files%3A%20Tab-deliminated%20text%20files%20vs%20CSV's%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2667065%22%20slang%3D%22en-US%22%3E%3CP%3EI%20routinely%20create%20text%20files%20in%20non-excel%20programs%20that%20are%20often%20manipulated%20in%20excel%20by%20other%20users.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20under%20the%20impression%20that%20csv%20was%20the%20preferred%20format%20for%20data%20that%20may%20be%20used%20by%20excel%20(I'm%20not%20sure%20where%20I%20got%20this%20impression%3B%20it%20may%20have%20been%20an%20old%20teacher).%20I%20may%20be%20misremembering%2C%20but%20I%20thought%20you%20could%20open%20a%20csv%20directly%20in%20excel%20and%20have%20the%20values%20separated%20into%20the%20right%20cells%20without%20going%20through%20the%20file%20import%20process.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20a%20coworker%20told%20me%20that%20I%20should%20use%20Tab-delimited%20instead%2C%20claiming%20that%20csv's%20don't%20automatically%20open%20as%20excel%20spreadsheets%20which%20is%20much%20more%20convenient.%20I%20tested%20it%20and%2C%20lo%20and%20behold%2C%20she%20was%20right%20and%20the%20tab-delimited%20version%20opens%20automatically%20formatted%20into%20columns%20while%20the%20csv%20needs%20to%20undergo%20the%20file%20import%20process.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20two%20questions%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Is%20there%20a%20setting%20on%20excel%20that%20can%20make%20it%20so%20that%20.csv's%20open%20automatically%20without%20needing%20to%20use%20file%20import%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20Are%20there%20any%20nuances%20or%20drawbacks%20to%20switching%20to%20Tab-delimited%20for%20my%20outputs%20in%20general%3F%20Is%20tab-delimited%20generally%20preferred%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2667065%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2667114%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20Text%20Files%3A%20Tab-deliminated%20text%20files%20vs%20CSV's%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2667114%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1132700%22%20target%3D%22_blank%22%3E%40Eric_at_work%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20only%20difference%20is%20that%20tab%20is%20always%20tab%2C%20for%20%22comma%20separated%22%20separator%20could%20be%20not%20only%20comma%20but%20semicolon%20and%20in%20general%2C%20any%20other%20symbol.%20Default%20one%20depends%20on%20regional%20setting%20of%20your%20Windows%2C%20but%20in%20general%20could%20be%20changed%20on%20any%20other%20one.%20However%2C%20with%20that%20it%20could%20be%20side%20effect%20for%20other%20apps%20%2F%20files%20within%20your%20system.%3C%2FP%3E%0A%3CP%3EAnother%20way%20it%20to%20add%20as%20first%20line%20of%20csv%20file%3C%2FP%3E%0A%3CP%3Esep%3D%2C%3C%2FP%3E%0A%3CP%3Eif%20comma%20is%20used%20by%20separator%20or%3C%2FP%3E%0A%3CP%3Esep%3D%3B%3C%2FP%3E%0A%3CP%3Eif%20semicolon%2C%20etc.%3C%2FP%3E%0A%3CP%3EFor%20example%2C%20if%20click%20on%20such%20csv%20file%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20204px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F304538iF5D73073515AE8E6%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20such%20one%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20209px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F304539iCF630C51F86014E7%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EExcel%20opens%20it%20exactly%20the%20same%20way%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20254px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F304540i11957D7E3E7CDF24%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20point%20is%20that%20Excel%20could%20do%20some%20default%20transformation%20if%20you%20open%20csv%20file%20by%20double%20click.%20For%20example%2C%2011-05%20could%20be%20converted%20to%20November%2005%20of%20current%20year.%20Again%2C%20depends%20on%20regional%20settings.%20In%20such%20cases%20Import%20is%20more%20preferable%20since%20you%20may%20define%20columns%20type%20during%20the%20import.%20But%20will%20it%20be%20tab-delimited%20or%20comma-delimited%20-%20doesn't%20matter%2C%20they%20are%20imported%20by%20exactly%20same%20way.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2667119%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20Text%20Files%3A%20Tab-deliminated%20text%20files%20vs%20CSV's%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2667119%22%20slang%3D%22en-US%22%3EThanks%20for%20this%20explanation%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20hope%20our%20company's%20computer%20regional%20settings%20are%20all%20the%20same%2C%20but%20I'll%20keep%20that%20in%20mind%20when%20the%20outputs%20might%20be%20dates%20or%20other%20ambiguous%20formats.%3C%2FLINGO-BODY%3E
New Contributor

I routinely create text files in non-excel programs that are often manipulated in excel by other users.

 

I was under the impression that csv was the preferred format for data that may be used by excel (I'm not sure where I got this impression; it may have been an old teacher). I may be misremembering, but I thought you could open a csv directly in excel and have the values separated into the right cells without going through the file import process.

 

However, a coworker told me that I should use Tab-delimited instead, claiming that csv's don't automatically open as excel spreadsheets which is much more convenient. I tested it and, lo and behold, she was right and the tab-delimited version opens automatically formatted into columns while the csv needs to undergo the file import process.

 

I have two questions:

 

1. Is there a setting on excel that can make it so that .csv's open automatically without needing to use file import? 

 

2. Are there any nuances or drawbacks to switching to Tab-delimited for my outputs in general? Is tab-delimited generally preferred?

 

2 Replies
best response confirmed by Eric_at_work (New Contributor)
Solution

@Eric_at_work 

The only difference is that tab is always tab, for "comma separated" separator could be not only comma but semicolon and in general, any other symbol. Default one depends on regional setting of your Windows, but in general could be changed on any other one. However, with that it could be side effect for other apps / files within your system.

Another way it to add as first line of csv file

sep=,

if comma is used by separator or

sep=;

if semicolon, etc.

For example, if click on such csv file

image.png

and such one

image.png

Excel opens it exactly the same way

image.png

 

Another point is that Excel could do some default transformation if you open csv file by double click. For example, 11-05 could be converted to November 05 of current year. Again, depends on regional settings. In such cases Import is more preferable since you may define columns type during the import. But will it be tab-delimited or comma-delimited - doesn't matter, they are imported by exactly same way.

Thanks for this explanation,

I hope our company's computer regional settings are all the same, but I'll keep that in mind when the outputs might be dates or other ambiguous formats.