All Questions
Tagged with microsoft-excel csv
495
questions
247
votes
13
answers
685k
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 ...
- 14.5k
158
votes
5
answers
117k
views
Is it possible to paste CSV formatted data into Excel 2007?
I want to copy and paste CSV formatted text into Excel 2007.
Is there a way of doing this without saving it to a temporary file first?
I'm sure in previous versions of Excel there was a way to ...
- 8,522
135
votes
6
answers
327k
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.
...
- 4,174
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 ...
- 2,616
132
votes
9
answers
489k
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. ...
- 2,987
125
votes
20
answers
555k
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?
- 1,527
125
votes
5
answers
569k
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 ...
- 1,383
120
votes
7
answers
216k
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 ...
- 1,494
67
votes
9
answers
391k
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 ...
- 1,341
48
votes
5
answers
54k
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 ...
- 765
44
votes
3
answers
262k
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" ...
- 825
38
votes
9
answers
207k
views
How to export selected columns into csv file
I want to export specific number of columns from excel into .csv file. I have around 10 columns like lname, fname, phone, address, email and so on. What should I do to export only certain columns like ...
- 381
31
votes
6
answers
112k
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 ...
- 18.8k
29
votes
8
answers
181k
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 ...
- 411
28
votes
3
answers
87k
views
How can you change Mac excel 2011 to separate using commas, not semicolons?
I have Excel 2011 on my Mac, and when I try to export a .xls file into .csv I end up having a semicolon separated file instead of .csv.
p.s. The System preferences for my keyboard input is Italian
...
- 397
27
votes
2
answers
171k
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?
- 881
24
votes
2
answers
61k
views
Unix command to get number of lines in a CSV file
I have to get the count of lines from incoming CSV files.
I have used the following command to get the count.
wc -l filename.csv
Consider a file coming with 1 record, I am getting some files with ...
- 1,449
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 ...
- 701
23
votes
2
answers
56k
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 ...
- 833
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?
- 12.8k
22
votes
5
answers
38k
views
How do I export only visible cells from Excel to CSV?
I am running Excel 2010. At the top of my spreadsheet I have about 10 hidden rows.
When I export to CSV, how do I export only visible cells?
- 221
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 ...
- 3,487
20
votes
2
answers
100k
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.
- 1,949
19
votes
5
answers
85k
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?. ...
- 679
19
votes
8
answers
38k
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 ...
- 528
18
votes
1
answer
42k
views
Excel save behaviour of CSV file with UTF8 encoding vs UTF8-Bom encoding
My original csv files are encoded with UTF8-BOM, this is so Excel can recognise the Unicode charcters (If its just encoded with UTF8 then Excel does not recognise the unicode characters).
My issue ...
- 283
18
votes
5
answers
35k
views
Importing CSV into Excel with commas in quoted fields
I have a CSV file that contains a text field that can have commas in it. When importing it into Excel, it's picking up these commas as field separators. I have the field in quotes, and I've tried ...
Jeff
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?
- 407
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 ...
- 281
17
votes
9
answers
104k
views
How do I get Excel to import a CSV file with commas in some of the content fields?
How do I get Excel to import my CSV file?
File
I have a file claiming to be CSV. It contains 10 fields, all of which are surrounded by double quotes (yes, even the dates and numbers.) 2 or 3 of these ...
- 475
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 ...
- 287
16
votes
5
answers
32k
views
How to import external csv/text file WITHOUT table formatting
I am using Excel that comes with Office 365. And this version of Excel does something annoying that's breaking all my macros.
I want to import a CSV file into a sheet WITHOUT any table formatting and ...
- 1,201
16
votes
3
answers
32k
views
Saving filtered results from an Excel workbook to a CSV file
I have an Excel workbook with a lot of rows in it. I filter it using the values of one column and then I want to save the filtered results to a CSV file. When I do "Save as..." I get all the rows. I ...
- 281
15
votes
4
answers
28k
views
Excel often save .csv files as tab delimited format. What happened?
I never know how that may happen.
I really really hate it.
That's because my program counts on csv files being csv.
And I never know, until now, how can my csv file becomes tab delimited format
...
- 4,801
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 ...
- 251
13
votes
1
answer
15k
views
Disable prompts in Microsoft Excel when saving a CSV file [duplicate]
I have to download multiple csv reports from the Internet throughout the day, make a tiny change to them, and save the change. Excel adds 3 completely unnecessary dialogs to what should really be a 5-...
- 589
12
votes
7
answers
65k
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 ...
- 2,274
11
votes
2
answers
102k
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 ...
- 691
11
votes
4
answers
133k
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 ...
- 111
9
votes
2
answers
86k
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 ...
- 93
9
votes
3
answers
37k
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 ...
- 321
8
votes
13
answers
110k
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 ...
- 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?
- 1,659
7
votes
6
answers
173k
views
How to prevent the number increase in the excel for Autofill?
I want my column to look like
number
30/31/32/33/34
30/31/32/33/34
30/31/32/33/34
30/31/32/33/34
......
eg: The content in each cell is 30/31/32/33/34. I want to fill all of the cells in the column ...
stack2013110
7
votes
2
answers
2k
views
Bulk fix for Excel 97-2003 error "format does not match extension"
Context
I have received around 1700 97-2003 excel files with a ton of data including some text information. I am trying to read them all into Stata before I clean and run some statistical analysis. ...
- 73
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 ...
- 4,521
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?
- 890
6
votes
3
answers
94k
views
Excel adds double quotes on csv export [duplicate]
I am experiencing a weird unknown behavior in excel. The sheet I want to export to a csv file consists of 4 columns with data like this:
site.aspx|de|lblChanges.Text|some text that will be used ...
- 191
6
votes
3
answers
69k
views
How to make excel open CSV files and automatically split the comma delimited column?
I get these reports everyday in CSV format. In the previous version of excel (2007) when I opened these files they were already split into columns. Now with the latest version it isn't. Where is this ...
- 171
6
votes
3
answers
9k
views
How to split a CSV file into multiple files based on a text string?
I have multiple text files (CSV) with a structure similar to this:
funkiana,23.55,-99.866
funkiana,24.634,-98.701
funkiana,24.717,-98.901
geminiflora,22.25,-104.6166667
geminiflora,21.21666667,-104....
- 63