Excel does not recognize the fields and displays all data in the first column. How do I set the delimiters correctly?

• Microsoft Excel For Mac Text Import Wizard Can 27t See All Columns Attached
• Microsoft Excel For Mac Text Import Wizard Can 27t See All Columns Excel
• Microsoft Excel For Mac Text Import Wizard Can 27t See All Columns Rows
• Microsoft Excel For Mac Text Import Wizard Can 27t See All Columns Instead

You can import information from your Excel spreadsheet by importing information from a comma-separated value (.csv) or a text (.txt) file and use the Text Import Wizard to build a new spreadsheet. For more information, see Prepare your Excel data source for mail merge in Word. In the Import Data dialog box, do one of the following. XML table in existing worksheet The contents of the file are imported into a new XML table in a new worksheet.If the XML data file doesn't refer to a schema, Excel infers the schema from the XML data file. Existing worksheet The XML data is imported in a two-dimensional table with rows and columns that shows XML tags as column headings. Can't tell whether UTF-16 would work without any sp installed, since I can't go back now. This is on windows, dunno about office for MAC. For both working cases, the import works when launching a download directly from the browser and the text import wizard doesn't intervence, it works like you would expect.

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

Microsoft Excel For Mac Text Import Wizard Can 27t See All Columns Attached

Choose one of these possiblities:

1. 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 --
2. In your CSV file(s), use a text editor to replace all commas , with semicolons ;-- or --
3. 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.

Microsoft Excel For Mac Text Import Wizard Can 27t See All Columns Excel

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.

Microsoft Excel For Mac Text Import Wizard Can 27t See All Columns Rows

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.

Microsoft Excel For Mac Text Import Wizard Can 27t See All Columns Instead

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

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?

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.