Hot answers tagged

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 ...
Stephan's user avatar
  • 1,548
81 votes

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 ...
Keltari's user avatar
  • 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 ...
manassehkatz-Moving 2 Codidact's user avatar
42 votes

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, ...
Tetsujin's user avatar
  • 47.7k
32 votes

"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...
user1251007's user avatar
  • 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 -...
ATek's user avatar
  • 546
30 votes

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 ...
MyDaftQuestions's user avatar
24 votes

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 ...
Wouter's user avatar
  • 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 ...
Stephen Hemingway's user avatar
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 ...
gavenkoa's user avatar
  • 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 ...
Stefan Kögl's user avatar
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&...
Ron Rosenfeld's user avatar
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.
Excellll's user avatar
  • 12.6k
16 votes

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 ...
Russ Frizzell-Carlton's user avatar
15 votes

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 ....
harrymc's user avatar
  • 458k
12 votes

How to convert Excel file with multiple sheets to a set of CSV files?

Here is a python script (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 ...
Scott Ming's user avatar
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.
Sam B's user avatar
  • 447
10 votes

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: +------+------+------...
DavidPostill's user avatar
  • 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 ...
Serge Ballesta's user avatar
9 votes

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'. ...
Liam's user avatar
  • 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. ...
AcePL's user avatar
  • 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 ...
Keiji's user avatar
  • 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 ...
Pranav Kulkarni's user avatar
8 votes

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 ...
nixda's user avatar
  • 26.9k
8 votes

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
Cloud's user avatar
  • 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://...
Steve Coleman's user avatar
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....
BibiM's user avatar
  • 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 ...
Bill Turner's user avatar
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 ...
Saaru Lindestøkke's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible