Excel Text Import Wiz-No Default and CSV save is always messed with. :(

[Tanquen]

Did some digging online and it looks like Excel after many years:

Still will not open CSV files as CSV files by default. Everything is in one cell ignoring the CSV extension. You then have to trick it into using the Text Import Wizard. Why?

Still has no way to set the Text Import Wizard defaults? I work with many apps that export to CSV and nothing else. Never seen a TSV file. Why is the default delimiter “Tab” and why can’t you change it?

Why oh why dose Excel mess with everything in your CSV and why is there no way to stop it? Many apps will not work with the CSV export file after simply opening in Excel and then saving it. Excel changes formatting of date and time and add or removes quotes and commas making the import CSV file no longer valid.

Is there a simple spreadsheet program that can?

 

[Joe4]

Still will not open CSV files as CSV files by default. Everything is in one cell ignoring the CSV extension.

I have never had that happen to me. If it is a properly formatted Excel file, where the data is separated by commas (and the whole line isn't enclosed in double-quotes), it should split them over multiple fields (unless you have different regional settings, maybe using a semi-colon instead of a comma or something like that). Now, it does have issue with sometimes guessing the wrong data types (dropping leading zeroes, etc). I would be interested to see exactly what your data looks like.

Anyway, if you open the CSV file by going into a blank Excel file, going to the Data menu, go to the Get External Data ribbon, and select "From Text", and then browse to your file, it will invoke the Import Wizard, where you can set all the settings and fields to your liking.

 

[Tanquen]

I’m no Excel expert and I’m sure different visions have handled it defiantly over the years but I’m now on Excel 2016 and this is the way it’s worked for (Me) the last 25+ years.

If you just open the CSV it will open in Excel but it ignores the commas in the Comma Separated Variable file. You get a few rows where there are carriage returns in the original CSV but that is it, everything else is in one cell in each row. If you open Excel first you will get the Text Import Wizard and yes, you can use the Data From Text option too but that still leaves you with bad data.

When done it would be nice if left everything alone but as you said it turns “,00.00” into “,0” and changes date and time formats or anything it thinks it a data or time. It also adds or removes quotes and commas. I just want it to output all the same strings it had going in and I know I’m not the only one. Just find the commas and put exactly what is in between them on the screen in each cell. Don’t change anything. I do some search and replaces and the output should be the same, leading zeros and or spaces and or things that look like dates or whatever.

 

[Tanquen]

Why did it add all those “”? Don’t see a way to edit a post. Maybe because I have Excel on my PC.

 

[Joe4]

I have been using a ton of Excel for about 25 years myself, a lot of it doing data management and manipulation of all sorts of files (Excel, CSV, tab-delimited, fixed-width, etc). As matter as fact, one of my big jobs for a few years was creating Excel data converters to handle all sorts of various data formats. And I have used pretty much every version of Excel there is since Excel 97. It has been my experience that Excel has been very consistent in how it handles CSV files over the years.

I think the big mistake that Microsoft makes is making Excel the default program to open CSV files. It shouldn't - it should be a Text Editor. The big issue with using Excel to open CSV files is that it tries to "guess" that data types of each field, and it often guesses wrong (like if you have an ID field of all numbers that may have leading zeroes, it thinks it is numeric, so all leading zeroes get dropped), or uses a date format that you don't like.

However, I have never had a properly created CSV file open in Excel and dump everything into one cell or one column. If you are experiencing that sort of odd behavior, then I you most likely have one of two things going on:

1. Your file is not a proper CSV file (does not follow the CSV file standards).
For example, Excel can handle text qualifiers just fine if placed around each field, like this:

"Doe, John","Male","Retired"

but this is not correct:

"Doe, John,Male,Retired"

If it is set to use double-quotes as text qualifiers, everything in that row will be imported into a single column.

2. Your local settings have been changed. For example, you could elect to use a semi-colon instead of a comma as your delimiter.
See this for an example: https://superuser.com/questions/238...-open-csv-files-with-data-arranged-in-columns

If you just open the CSV it will open in Excel but it ignores the commas in the Comma Separated Variable file. You get a few rows where there are carriage returns in the original CSV but that is it, everything else is in one cell in each row. If you open Excel first you will get the Text Import Wizard and yes, you can use the Data From Text option too but that still leaves you with bad data.

When using that option, you should never be left with bad data, because you are the one telling Excel exactly what the delimiter is and what the data type of each field is! Many people do not realize when you get up to Step 3, you can select each and every field and set the Data Type on each separately. If you choose "Text" for every single one, the data will be imported into Excel exactly how it appears in the CSV file (it will not convert any of the data).

Why did it add all those “”? Don’t see a way to edit a post. Maybe because I have Excel on my PC.

Maybe the PC is your issue!
Where are you posting this question from, a PC or a phone or some other device?
Are you using any sort of special keyboard or font?

By the way, should also ask, where are you located and what version of Excel are you using?
I ask because I have seen differences in people using European and Asian versions of Excel (mostly, which what is set as the default delimiter).

 

[Tanquen]

I’m in the US and using the regular English version.

That doesn’t seem right. I use many different apps and I guess all must fail the CSV standard somehow but how? I’ve never had Excel open from clicking on a CSV and had it open correctly. If something is wrong with the CSV file then why does the Text Import Wizard not open and why does it work fine when after getting it to run? All I have to do is tell it to use a comma for the delimiter.

Yes I use the set them all to text as this helps with some of the formatting issues. Another setting I’d like to make default when the Text Import Wizard is run.

I think the big mistake that Microsoft makes is letting Excel mess with simple CSV files. If it’s a CSV just look for the comma and put that string in a cell as is and don’t try to format it or do anything else.

“If you choose "Text" for every single one, the data will be imported into Excel exactly how it appears in the CSV file (it will not convert any of the data).” That is how it should be but it still does add stuff. I remember with old FIX apps it would add or remove commas in rows that had empty cells. The FIX app was looking for data at a certain number of commas and will fail to import the file.

I don’t know but it would be great if there was a setting to just open a CSV (open any file really) a certain way (Text Mode?) and leave everything alone.

 

[Joe4]

I’ve never had Excel open from clicking on a CSV and had it open correctly.

Like I said, doing that way often results in issues with incorrect Data Types and/or date conversions, but it as long as the Text Qualifiers and Commas were used correctly in the file, it should not put everything in a single cell or column. It should still put things in the correct columns (even if the formatting is not correct). If it is putting everything in a single cell or column when it shouldn't be, then I believe the issue is either with the data file structure or your Excel settings.

If something is wrong with the CSV file then why does the Text Import Wizard not open and why does it work fine when after getting it to run?

If you try to open a CSV by clicking on it from Windows Explorer, it will open in Excel automatically without invoking the Import Wizard. That is just their default behavior. I don't agree with it, but it is what it is. One of the first things I usually do when I get a new computer is to change my settings so that a Text Editor like NotePad is the default program to open CSV files when clicking on them from Windows Explorer instead of Excel. It is a Text file, not an Excel file, so a Text Editor should be the default program to view it.

“If you choose "Text" for every single one, the data will be imported into Excel exactly how it appears in the CSV file (it will not convert any of the data).” That is how it should be but it still does add stuff.

If we are talking about the same thing, I have never experienced anything like that. I challenge you to come up with an example that I can recreate on my system here. Just give me a single line of CSV data, and I will try to open it in Excel and see what happens.

I remember with old FIX apps it would add or remove commas in rows that had empty cells. The FIX app was looking for data at a certain number of commas and will fail to import the file.

I have seen it add commas for blank rows in creating CSV files from Excel, but not in importing CSV files into Excel. Are you sure you aren't confusing the two things?

I don’t know but it would be great if there was a setting to just open a CSV (open any file really) a certain way (Text Mode?) and leave everything alone.

Some years ago I wrote a VBA program to do precisely that. I had some non-technical users who needed to update data in CSV files. So they needed some way to open it Excel, make their edits, and save the file. That was the only way of doing it and ensuring that Excel didn't change date formats and drop leading zeroes.

 

[Joe4]

One other thing I should add. I have had arguments with some smaller "software companies" (I use that term losely) with what they consider CSV files.
They INSISTED that their program produced valid CSV files, but they did not, as they neglected to use Text Qualifiers where necessary.
If your data may have valid commas in it, you need to use Text Qualifiers to distinguish "commas that are delimiters" from "commas that are part of the data".

For example, let's say that the data had three fields:
Company Name: Dewey, Cheatham, & Howe
Contact: John Doe, Jr.
City: New York

So that line of data was coming over like this:
Dewey, Cheatham, & Howe,John Doe, Jr.,New York

For any program reading in the data, how can they tell which commas are delimiters and which are data? They cannot from that! That is why text qualifiers are required. Anything between text qualifiers is treated as text. So it should look like this:
"Dewey, Cheatham, & Howe","John Doe, Jr.",New York
That is a properly constructed line of CSV data (the New York can be enclosed in Text Qualifiers, but doesn't need to be, since there are no commas in that field).

To make a long story short, the point that I am trying to make is just be wary of "CSV" files that come from outside source and other programs. Just because a program says it can create them properly doesn't necessarily mean that they do!