91
votes
How to force Excel to open CSV files with data arranged in columns
Sadly, Microsoft decided to use different separators in different localizations for CSV (which stands for Comma Separated Values). That's especially annoying when working on an international basis.
If ...
- 1,548
81
votes
Accepted
Why isn't there a delimiter character in ASCII?
Delimiters already exist in ASCII. Decimal 28-31 (hex 1C-1F) are delimiters. This includes the file, record, group and unit separators.
I would assume we do not use them, as it is easier to type ...
- 72k
52
votes
Why isn't there a delimiter character in ASCII?
As already noted, ASCII includes delimiters. The problem is not that an extra key is needed during data entry to include the delimiters - Control is no harder to use than Shift for an UPPER-case or ...
42
votes
Accepted
How can you change Mac excel 2011 to separate using commas, not semicolons?
Excel for Mac currently has no setting to change the CSV separator from the app itself.
The chosen value separator depends on your Region
and your region's default number separators.
To change them, ...
- 47.7k
32
votes
Accepted
"Save as csv" not available
You have to follow this way to open a "Save As" dialog where you can choose other file types (csv is among them):
Data - Export Data - Export as CSV File...
- 1,045
31
votes
How do you force excel to quote all columns of a CSV file?
Powershell appears to dump correctly. so something like
search for powershell.exe on windows machine if you dont know powershell.
import-csv C:\Temp\Myfile.csv | export-csv C:\Temp\Myfile_New.csv -...
- 546
30
votes
Accepted
How can I open .csv files on Excel if Excel isn't in the list?
Right click the file, choose open with. If Excel is not showing, select more apps, then chose option to browse. Browse for Excel and choose it. You can also check to always use this type of app for ...
- 1,903
24
votes
Accepted
Excel save behaviour of CSV file with UTF8 encoding vs UTF8-Bom encoding
What happens is the following.
Excel looks at your systems Regional settings to find what list-separator is configured, in your case, a comma. Here it's important to point out that CSV is not a ...
- 1,601
19
votes
How to get Excel to interpret the comma as a default delimiter in CSV files?
You don't need the quotes around the sep=, - as long as it's the first line of the file it'll work, at least with Excel 2016.
I discovered that if the file is tab delimited, sep=\t works fine, with ...
19
votes
Can I create an Anki deck from a .CSV file?
Another way to generate .apkg files is by programmatically reusing the desktop version with Python. Extend:
PYTHONPATH=/usr/share/anki: python ...
Then you can adapt the following example to your ...
- 1,916
19
votes
What is the "sep=" metadata you can add to CSVs?
RFC 4180 is commonly recognized as the standard for the CSV format, and does not mention any such feature.
W3C Model for Tabular Data and Metadata on the Web mentions it as a feature not within their ...
- 290
17
votes
How to import external csv/text file WITHOUT table formatting
You can certainly go back to the previous method of importing.
GoTo: File-->Options-->Data:
Select the Text legacy import wizard.
Then, when you want to do the import
Data-->Get&...
- 8,248
16
votes
How to export selected columns into csv file
Here's a low-tech solution:
Save a copy of your entire sheet as .csv.
While still open in Excel, delete the columns you don't want.
Save.
- 12.6k
16
votes
Accepted
Excel often save .csv files as tab delimited format. What happened?
Just to expand on Chris76786777's answer on this possible Excel bug, I have also noticed if you open a CSV file that is encoded with UTF-8 with a BOM header on it (UTF-8-BOM), add some columns, and ...
15
votes
Accepted
Bulk fix for Excel 97-2003 error "format does not match extension"
The error "format does not match extension" means that the files are not
truly .xls. As Excel can still open them, they are probably of another
format that is supported. My guess would be ....
- 458k
12
votes
How to convert Excel file with multiple sheets to a set of CSV files?
Here is a python script getsheets.py (mirror), you should install pandas and xlrd before you use it.
Run this:
pip3 install pandas xlrd # or `pip install pandas xlrd`
How does it works?
$ python3 ...
- 121
10
votes
How to get Excel to interpret the comma as a default delimiter in CSV files?
Accepted answer is correct but I am a visual person. Here is every single step in screenshot format of how to do this in windows 10.
- 447
10
votes
Accepted
Convert tab separated values to ASCII table
How can I convert tab separated values to an ASCII table?
I use Text Tables Generator for this kind of task.
I pasted your data on that page and it created the following table:
+------+------+------...
- 154k
10
votes
Why isn't there a delimiter character in ASCII?
This is mainly historical.
In the old days of informatics, data files were mostly Fixed Width Fields files because it was the natural IO for languages like Fortran IV and COBOL: n characters for first ...
- 201
9
votes
Accepted
Open a CSV in LibreOffice Calc without converting values to scientific notation
Try these options in the Text Import dialog:
Deselect the 'Detect Special Numbers' checkbox
In the preview area, click the column header where it is labeled 'Standard' and change it to 'Text'. ...
- 2,990
9
votes
Excel often save .csv files as tab delimited format. What happened?
When you double-click CSV file and it opens in Excel what actually happens is that Excel treats it as a text file, however it's being converted to columns "on the fly". It's still text file, though.
...
- 1,680
9
votes
How can I open .csv files on Excel if Excel isn't in the list?
Emily's answer is what I'd suggest if it wasn't already posted, as it covers actually locating Excel on the PC.
But, as more of a side note that seems a little too useful to leave in a comment: if you ...
- 191
8
votes
Why does excel always say "..may contain features that are not compatible with CSV" when saving a CSV file?
Looks like there is a way to fix this, I just found this article, which worked for me using Excel 2013.
Registry subkey
HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options
Value name ...
8
votes
Accepted
How to split a CSV file into multiple files based on a text string?
The fastest method I could think of is to use PowerShell
$fullpath = "D:\myFolder\input.csv"
$path = Split-Path $fullpath -parent
$data = Import-CSV -Delimiter "," -Path $fullpath ...
- 26.9k
8
votes
Accepted
WPS Office CSV Delimiter - Linux
Open your csv file with WPS Spreadsheets
Select the A column -> Data -> Text to Columns -> Next -> Select or define your delimiter -> Finish
- 196
7
votes
How do you force excel to quote all columns of a CSV file?
Highlight the cells you want to add the quotes.
Go to Format –> Cells –> Custom
Copy/Paste the following into the Type field: \”@\”
Click “okay”
Be happy you didn’t do it all by hand.
From: http://...
- 171
7
votes
How to make excel open CSV files and automatically split the comma delimited column?
Here are some easy explanations on how to do it automatically when you open your file with Excel:
As you said, Excel has a default character as a delimiter. In your case, this is ";" visibly....
- 71
7
votes
Preview csv file in outlook
It looks like, for some reason, Microsoft doesn't think that .csv files are Excel files. I'm using Outlook 2016 and Excel from Office 365, but I think this may work with other versions.
In the ...
- 171
7
votes
How to import external csv/text file WITHOUT table formatting
I'm not sure if this fits your workflow, but instead of importing you can open the .csv file in Excel:
File ➡ Open (or press CTRL+O).
Make sure All Files or Text Files is selected in the file type ...
- 5,554
Only top scored, non community-wiki answers of a minimum length are eligible