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

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

In the Import dialog box, click the option for the type of file that you want to import, and then click Import. In the Choose a File dialog box, locate and click the CSV, HTML, or text file that you want to use as an external data range, and then click Get Data. The short of it is that I can see the data in Excel, but all of the data doesn’t import. I have an excel workbook containing one spreadsheet. I can see the data in the columns and everything looks good. When I use the import wizard to import the data, I can see the columns and the preview data, but the data doesn’t all get transferred over. From Excel’s “Data” tab, click the “Text to Columns” button found in the “Data Tools” section. This will bring up the “Convert Text to Columns Wizard” window and allows you to begin separating your data. From the options, select the “Delimited” radio button and click “Next” to continue. By default, Excel will choose to.

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

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.

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.

Now in Excel365, we have the Get & Transform area available, which has been described in an older post of mine. The Get & Transform area of the Data tab on the ribbon is superior in terms of DataConnectors and Transformational capabilities. However though, sometimes we just want to use one of the old wizards to import our data such as the LegacyWizards.

The Legacy Wizards do not appear in the Get & Transform area now, they are just hidden waiting for us to turn them on. Below you can find out How To Turn On The Legacy Wizards In Microsoft Excel 365.

We must select the File tab in order to move to Backstage View. Once in BackstageView, from the left of the drop down menu we select the category Options as shown in the image below.

Once we select the Option command, the ExcelOptions dialog box appears, where from the left once more, we select the category Data. Under this category we can ChangeOptionsRelatedToDataImportAndDataAnalysis, as shown below.

Microsoft excel for mac text import wizard can 27t see all columns excel

At the bottom part of the Data area we notice the area mentioning ShowLegacyDataImportWizards. All we need to do is to activate or deactivate the check boxes at the left of the commands that we need, and which will be displayed in Excel. The commands that are available are the following:

From Web
From SQL
From XML Data Import

Once we do all the adjustments that we need, we press the Ok button at the bottom right corner of the Excel Options dialog box, in order for us to return to Excel and for our adjustments to take place.

In the image below, I have activated all the LegacyDataImportWizards, from the ExcelOptions dialog box, and then from the Data tab and from the left area of the ribbon named Get & Transform Data, we select the GetData command as shown below and from Now, from the drop-down menu we notice the LegacyWizards command, with all the available commands that we activated.