Excel does not recognize the fields and displays all data in the first column. How do I set the delimiters correctly?
For more information about Office updates, see Office downloads & updates. If your issue is not resolved after you install the updates, go to method 3. Method 3: Add-ins interfering Check the Add-ins that are running, and try disabling them to see if Excel is working properly. Follow the directions in the following article to disable the add-ins. Download Microsoft Excel for macOS 10.13 or later and enjoy it on your Mac. This application requires a qualifying Microsoft 365 subscription. Microsoft 365 includes premium Word, Excel, and PowerPoint apps, 1 TB cloud storage in OneDrive, advanced security, and more, all in one convenient subscription. Open this new CSV file using Excel - your non-English characters should be displayed properly. On a Windows computer, click 'File New' in Excel. Click 'Data' tab. Then click 'From Text' option. Select the CSV you file you saved from Accompa. Excel will display 'Text Import Wizard'. In step-1 of this wizard.
Votes:
2
Your Vote:
Fourth solution
Add an extra line to your CSV file to tell Excel what the separator is.Add the following line to the top of your CSV file:
This will tell Excel to use the ';' character as separator for the next lines.This first line is considered an instruction and not shown in your Excel sheet.
When opening standard CSV (Comma Separated Values) files in Excel you may find that Excel did not recognize the fields and simply displays all data in the first column.
The Problem
The problem is:
- The standard field delimiters for CSV files are commas:
,
- On American Windows versions, the comma is set as default for the 'List Separator', which is okay for CSV files.
- But on European Windows versions this character is reserved as the Decimal Symbol and the 'List Separator' is set by default to the semicolon:
;
There are Three Solutions
Choose one of these possiblities:
- Change the CSV file extension to TXT (when you open a TXT file with Excel it will start the text file import wizard where you can choose the delimiter)
-- or -- - In your CSV file(s), use a text editor to replace all commas
,
with semicolons;
-- or -- - In your Windows Control Panel, change your Regional and Language settings
Solution #3: Changing Regional and Language settings
The last solution is our preferred one. To change regional settings,
- go to Start | Control Panel | Regional and Language Options
- Click Additional Settings
- For Decimal Symbol, enter a dot:
.
- For List Separator, enter a comma:
,
Now, when you open a CSV file in Excel it will automatically find the data fields and open it appropriately.
Sorry, I think that solution 2 is not a 'fully' valid one:
- at least in my reports, some columns (those that are not 'RAW') employ ',' for decimal ('3,45 kbit/s)'; so when you replace ',' with ';' you get a somewhat ugly result ('3;45 kbit/s').
- besides, raw columns use '.' (dot) for decimal symbol; so you would replace them with ',' (comma)
Instead, I would say:
- replace
/','/
(including quotes) with/';'/
- replace
/./
with/,/
Problem is that (at least with my settings) RAW columns and 'formatted' columns have different symbols for decimal and third digit symbols.
I'm afraid only 1 and 3 would work without further editing.
RegardsRodeca
Disclaimer: I'm an absolute newbie, so may be I'm messing everything (but I'm now fighting with these reports) ;-)
Disclaimer 2: I tried my best, but sure you'll need to translate _my_ English to _true_ English
Votes:
2
Your Vote:
Fourth solution
Add an extra line to your CSV file to tell Excel what the separator is.Add the following line to the top of your CSV file:
This will tell Excel to use the ';' character as separator for the next lines.This first line is considered an instruction and not shown in your Excel sheet.
Great. The only reason I signed up for this site is to give a upvote to the 'Fourth solution', but the site is telling me I need more points for that.
The 'Fourth solution' works! And it is so simple and it works with a little change. I downloaded a CSV spreadsheet from the US and tried to open it with my MS Excel 2010. It did not work. Solution #2 did not work for me, too.
But solution #4 works if you do the following:
1. Open the CSV file via a simple text edit / note pad editor.2. Go to the first line and add above that line a new line with sep=, (with a comma, not with a semicolon). In my case it worked this way. My CSV was comma separated.3. Save the file and open it with Excel4. Win
Thanks for your feedback and intended upvote. (upvoted your reply instead :-)
The semicolon was only intended as example and can be replaced by any character being the real separation character in your CSV file.
I'm glad this worked for you.
I would give the sep answer an upvote, but I need 15 rep. IMO this is the best solution as it will make your app UNIVERSAL
first solution better
None of these solutions work for me, and the 'best' solution is not a good solution at all as it only works with Excel, and no product that generates a csv will add that line to the front.
I have a US version of Windows in the USA, and my control panel Regions and Language settings already have the decimal point as (.) and list separator as (,). My CSV has commas to separate fields (it was generated by SQL Server Management Studio) and still imports to Excel all in the first column. In addition, some of the lines are long enough that Excel wraps them into two rows.
Need a real solution.
Mark, is this related to our PRTG Network Monitor? Are the CSV originating from it? CSV is comma-separated-values, one can only try to work with the options Excel offers:http://superuser.com/questions/407082/easiest-way-to-open-csv-with-commas-in-excel
Fourth solution really works fine
How To Download Excel On A Mac
Even though the solution proposed might work in this instance, you might corrupt your data in other ways while using this method. I've written an in depth article on why loading CSV files into Excel using the default settings is a bad idea, and what you can do about it:
Summary: Never let Excel open csv files automatically, always -always- use import from text, and manually set the encoding, delimiter and encapsulation
The fourth solution would be fine, except that it is not allowed when the file starts with a ByteOrderMark (BOM) to indicate the file contains UTF-8. Is there a way to use both features?
![Correctly Correctly](/uploads/1/3/3/2/133275897/653062328.png)
We know no solution for this. Overall, solution #3 is the easiest once set up, however this can mess with other applications (for example when a decimal point is required when before a comma was accepted, too.)
In addition to Solution #3, also ensure that 'Use system separators' is checked under Advanced setting.
Check your Excel Advanced setting:
File -> Options -> Advanced
Make sure 'Use system separators' is checked.
For me, this setting was not checked. After checking this setting I am able to view csv file.
Thank you
Please log in or register to enter your reply.
How To Download Excel On My Mac
Disclaimer: The information in the Paessler Knowledge Base comes without warranty of any kind. Use at your own risk. Before applying any instructions please exercise proper system administrator housekeeping. You must make sure that a proper backup of all your data is available.