Questions tagged [csv]

Comma-separated values: a rudimentary plain-text spreadsheet. Also includes tab, space, semicolon or other-separated values.

Filter by
Sorted by
Tagged with
9 votes
2 answers
85k views

How do I split one row into multiple rows with Excel?

I have a product database in Excel with several hundred entries, each of which has from 1 to 3 "tiers" of pricing: Standard, Deluxe, and Premium. Each tier has its own SKU (A, B, or C added on to the ...
GreysonD's user avatar
243 votes
13 answers
674k views

Easiest way to open CSV with commas in Excel

CSV files are automatically associated with Excel but when I open them, all the rows are basically in the first column, like this: It's probably because when Excel thinks "comma-separated values", it ...
Borek Bernard's user avatar
131 votes
9 answers
485k views

How to get Excel to interpret the comma as a default delimiter in CSV files?

I have a number of .csv files. Some of them are comma delimited, some are tab delimited (maybe they should be called .tsv ...) The csv extension gets associated with Excel when Excel is installed. ...
Szabolcs's user avatar
  • 2,907
124 votes
5 answers
553k views

How to set character encoding when opening a CSV file in Excel?

Is it possible to set the default encoding for Excel (any version, e.g. 2010) when opening files like csv files (like you can in Open Office Calc)? I When I try to open a csv file encoded in Japanese ...
LozzerJP's user avatar
  • 1,373
135 votes
7 answers
119k views

How can I stop Excel from eating my delicious CSV files and excreting useless data?

I have a database which tracks sales of widgets by serial number. Users enter purchaser data and quantity, and scan each widget into a custom client program. They then finalize the order. This all ...
atroon's user avatar
  • 2,616
120 votes
7 answers
213k views

How to open semicolon delimited CSV-files in US-version of Excel

When I double-click on a .csv file, it is opened in Excel. The csv-files have columns delimited with semicolons (not commas, but also a valid format). Using a German Windows/Excel setup, the opened ...
Holgerwa's user avatar
  • 1,494
65 votes
9 answers
380k views

How to force Excel to open CSV files with data arranged in columns

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 ...
Timofey's user avatar
  • 1,321
22 votes
2 answers
54k views

How to control CSV import into Excel 2010

When I open CSV files in Excel 2010 I get entire rows in cells, not distributed according comma separation. How to control this?
Dims's user avatar
  • 12.7k
18 votes
3 answers
6k views

Excel: Is there a registry setting to suppress the warning messages when saving spreadsheets in CSV format?

I work with many CSV files, and am constantly annoyed by the Excel dialog that pops up when I try to save a file: "[your spreadsheet] may contain features that are not compatible with CSV (Comma ...
gw.'s user avatar
  • 281
11 votes
4 answers
130k views

How to avoid double quotes when saving Excel file as Unicode?

I have an Excel file with Unicode content of which some cells contain text inside double quotes, for example "text". When I save the Excel file to a text file in Unicode format, the text which ...
karthik's user avatar
  • 111
132 votes
6 answers
317k views

Formatting a comma-delimited CSV to force Excel to interpret value as a string

I've been searching around for a while trying to figure out how to output a CSV file in such a way to force Excel to interpret the values as a string and not try to convert them to numbers or dates. ...
Simon E.'s user avatar
  • 4,047
29 votes
8 answers
178k views

How do I stop Excel from converting value 0503E000 to 5.03E+02 automatically?

Excel converting value 0503E000 to 5.03E+02 in any cell automatically in CSV I tried to convert it to text number and general but after i save it and reopen it again it goes back to 5.03E+02 How do I ...
CrashOverride's user avatar
23 votes
2 answers
54k views

Open a .csv file in Excel and have it update whenever .csv file changes

Hi I'm working with some web scrapping and the data on my csv files will be constantly changing. I want to open these files on Excel to view and also have it refresh whenever the csv source changes ...
meiryo's user avatar
  • 823
22 votes
5 answers
32k views

How can you make Excel 2007 stop formatting large numbers as scientific notation?

Kind of the inverse of this question. I frequently download CSVs from outside sources as part of my programming work. I like to examine their data in Excel because, let's face it, Excel is an ideal ...
John Rudy's user avatar
  • 3,497
18 votes
2 answers
17k views

True difference between Excel CSV and Standard CSV

What is the true difference between an Excel CSV and a standard CSV? For example, when handling columns with line breaks inside one cell, how do they encode it differently?
user157195's user avatar
16 votes
6 answers
46k views

Excel CSV import treating quoted strings of numbers as numeric values, not strings

I've got a web application that is exporting its data to a CSV file. Here's one example row of the CSV file in question: 28,"65154",02/21/2013 00:00,"false","0316295","8316012,8315844","MALE" Since ...
Michael Oryl's user avatar
12 votes
4 answers
32k views

How do I get excel to not mess around with the formatting of a .csv file

I have two issues with a csv file that occur when I try and edit it in Excel. I have an ISBN column that gets reformatted to scientific format every time I save the file The encoding for the file is ...
Martin Duys's user avatar
  • 1,201
124 votes
20 answers
545k views

How do you force excel to quote all columns of a CSV file?

Excel only places quotes around certain fields, how do I force excel to save a CSV file with quotes around every column?
Michael Shnitzer's user avatar
47 votes
5 answers
51k views

What is the "sep=" metadata you can add to CSVs?

While trying to find out how to get CSVs to open correctly in Excel on a Spanish PC (belonging to a customer) I found many answers recommending using sep=, at the top of the file: Easiest way to open ...
Burhan Ali's user avatar
44 votes
3 answers
261k views

How do you change default delimiter in the Text Import in Excel?

I want to import a CSV file to Excel. The CSV file has comma separated data (go figure), but the delimiter in the Text Import Wizard is set to "Tab" by default. How can I change the default to "Comma" ...
Lars's user avatar
  • 825
34 votes
9 answers
43k views

How to join two CSV files?

Suppose you have one CSV file with 2 fields: ID and email. You have another file with 2 fields: email and name. How can you produce a file with all three fields joined on email?
crst53's user avatar
  • 617
27 votes
2 answers
169k views

Excel save-as CSV options -- possible to change comma to pipe or tab instead?

Is there a way to tell Excel which delimiter to use when producing a CSV, e.g. tab or pipe?
Tim's user avatar
  • 817
22 votes
4 answers
20k views

Light text editor for CSV file? [closed]

Could anybody suggest a light (small, fast) text editor that can handle columnar view of csv files? save quote character to all fields, even if not 'necessary' OpenOffice Calc is bit big for my old ...
Radek's user avatar
  • 3,084
19 votes
5 answers
83k views

Why does excel always say "..may contain features that are not compatible with CSV" when saving a CSV file?

Every CSV I've ever tried to save from Excel (2010, anyway) says filename.csv may contain features that are not compatible with CSV (Comma delimited). Do you want to keep the workbook in this format?. ...
gregmac's user avatar
  • 678
12 votes
6 answers
63k views

Why is Excel removing leading zeros when displaying CSV data?

I have a CSV text file with the following content: "Col1","Col2" "01",A "2",B "10", C When I open it up with Excel, it displays as shown here: Note that Cell A2 attempts to display "01" as a number ...
Ivan's user avatar
  • 2,214
9 votes
4 answers
35k views

Export Office Excel table to csv using a macro

I'm using Excel Tables in Excel 2010. I would like to quickly export the contents of these tables to *.csv. My current workflow: 1. Select table manually 2. Copy the contents into a new workbook 3....
vg425's user avatar
  • 103
8 votes
13 answers
108k views

How to stop Excel Treating US dates as UK dates?

I'm in the UK, I've got a problem where I've got a list of dates supplied in US format. Excel seems to treat the ones that are valid in both formats as UK dates, (e.g. 03/01/2012 becomes 3rd of ...
deworde's user avatar
  • 260
8 votes
1 answer
5k views

Break up a large CSV file

I have a large CSV file (about 6,000 rows) that I want to break up into smaller CSV files (about 100 rows each). Is there an easy way to do that?
nute's user avatar
  • 1,649
7 votes
5 answers
7k views

Excel 2007 is converting CSV fields into formulas

I have a problem with a CSV file which has telephone numbers stored in their internationalized format (+1-800-555-1234). When opening this CSV in Excel 2007, it converts that data to a formula and ...
dlux's user avatar
  • 4,511
5 votes
0 answers
255 views

Excel 2007 doesn't preserve asian characters when converting to csv [duplicate]

I am trying to save a spreadsheet as a CSV file. The file consists of korean characters, and the resulting csv has those characters literally converted to question marks (0x3F). I am running on an ...
MxLDevs's user avatar
  • 1,189
3 votes
3 answers
6k views

Importing from a CSV modifies the number format into scientific notation (1,23E+18)

Suppose I have a following string in a .csv file which I am trying to open in Excel 2010: 1073741824;1073741824;1073751824;1073741832;1152921513196781569 However, when I actually open it, the value ...
wh1t3cat1k's user avatar
3 votes
4 answers
12k views

Can I use a batch file to remove blank columns in a .csv

I have nine columns in my current .csv file, however, the 2nd and 3rd columns are blank and I want to get rid of them. Is there a way to use batch commands to remove these empty columns from my .csv ...
batchnoober's user avatar
3 votes
1 answer
450 views

How to return in a cell all the matching values present in a column?

Suppose I have the following data: A B 1 idx1 VAL1 2 idx2 VAL2 3 idx3 VAL3 4 idx4 VAL1 5 idx5 VAL4 6 idx6 VAL1 7 idx7 VAL1 8 idx8 VAL2 9 idx9 VAL5 10 idx10 VAL3 For each "VAL[n]" ...
Backo's user avatar
  • 177
2 votes
1 answer
17k views

Excel How to make Text Qualifier editable or add a new entry in the existing list?

I have a CSV file which is not properly formatted because one of the columns contains HTML code which use the same character as the Text Qualifier: the double quote. Example: "col1","col2","col3" "...
рüффп's user avatar
1 vote
2 answers
2k views

How to move files to specific folders based on CSV entries?

I have a CSV file, comma delineated. I need to move the file, listed in column 3 into the folder listed in column 12. I output both columns to different text files using: awk -F ',' '{print $3}' /...
Alex L. Mermelstein's user avatar
1 vote
1 answer
12k views

How to import (and view correctly) Chinese UTF-8 encoded CSV file with Excel 2011 on Mac OS X

I have a CSV file which I can open and view the column with Chinese letters normally with TextMate. However, when I try to import this file into Excel 2011, the field comes with unreadable fonts. ...
cenk's user avatar
  • 113
0 votes
1 answer
30k views

Editing CSV file in Excel always converts number column to scientific notation [duplicate]

I have a CSV file that I'm editing in Excel 2013. In the file there is a column that contains numbers e.g 688087123456789. Everytime I open the CSV file in Excel, it converts the number to scientific ...
jmc's user avatar
  • 101
48 votes
3 answers
77k views

Can I create an Anki deck from a .CSV file?

Can I convert my CSV file into an Anki deck? I can't find any options in the program.
Thalecress's user avatar
  • 1,317
33 votes
21 answers
48k views

Querying a CSV file

Does anyone know of a simple tool that will open up a CSV file and let you do basic, SQLesque queries on it? Like a graphical tool of sorts, one that is easy to use. I know I could write a small ...
J. Polfer's user avatar
  • 2,342
31 votes
6 answers
108k views

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

I have an Excel file with 20 sheets on it. Is there a way to quickly convert the file into 20 CSV files - one for each sheet? I can do it manually of course, but I'll have to be processing these ...
AngryHacker's user avatar
  • 18.6k
23 votes
4 answers
32k views

Excel does not honor the delimiter setting for reading or writing CSV files

When saving/opening CSV files in Excel, the default separator is a semicolon ;, as I need it to be a comma , I tried to change it by changing the Language and Regional settings following several other ...
Sandra's user avatar
  • 701
20 votes
2 answers
97k views

What's the difference between - CSV (MS-Dos), CSV (Macintosh), CSV (comma delimited)

What's the difference between - CSV (MS-Dos), CSV (Macintosh), CSV (comma delimited) file types in excel 2010? They are all listed as a save file type, but ultimately are Comma Separated Value files.
chobo2's user avatar
  • 1,949
19 votes
8 answers
37k views

Increase size limit of data import from csv into Excel

I have a load of data in a csv file that I'm importing into Excel, most of it is fine, but one of my columns contains up to 1000 characters of data. Unfortunately, Excel has taken it upon itself to ...
SwiftD's user avatar
  • 528
15 votes
1 answer
38k views

Saving a file in a CSV type in Excel always removes the BOM

I've been trying to find a reasonable solution/explanation (unsuccessfully) to find out why Excel defaults to removing the BOM when saving a file to the CSV type. Please forgive me if you find this a ...
rickp's user avatar
  • 251
11 votes
2 answers
100k views

Compare two spreadsheets and get missing records

I have two CSV documents. The first has 10.000 rows with 50 columns, with the first column being a unique SKU (stock keeping unit). The second document with 9.500 rows using the same columns and ...
Moak's user avatar
  • 691
9 votes
3 answers
36k views

Excel 2019 not opening CSV correctly - not parsing columns

When I open a CSV files in Excel 2019 the file is not being parsed correctly. When I opened the same files in Excel 2016 they would be parsed correctly, with the commas used as delimiters. But I have ...
Herman Toothrot's user avatar
7 votes
1 answer
15k views

Space or tabs as separator in CSV

I usually put "sep=," or "sep=;" as first line on my csv files so i can easily open them with excel. But is there a way to use this with space and tab separated files?
Madmenyo's user avatar
  • 890
7 votes
2 answers
6k views

Saving to CSV file always adds quotation marks in OpenOffice

I'm trying to use OpenOffice Calc for editing CSV files with TAB as delimiter. No matter what I do, Calc is insisting on adding quotation marks (") to the strings. If a cell content is number only, it'...
lang2's user avatar
  • 2,222
4 votes
3 answers
12k views

How to save double quotes and semicolon in a csv file while opening the file in excel 2010?

I receive a CSV file from sever. The data inside it is delimited by semicolon and it is in UTF-8 format. For example, the data what we receive is: "NL 我喜"" mutilple""我喜" When we open it for the ...
NJMR's user avatar
  • 147
4 votes
1 answer
2k views

ODBC Query cannot be executed because it is too complex?

I have a CSV file which I am using as a database with LibreOffice DB. What I'm doing is explained in detail in this answer. I can do some operations, but when I try to enter the following into the ...
patrick's user avatar
  • 1,243