Problems with CSV import due to comma in string

[arbazhamayun]

Hi guys,

I have a issue when importing .CSV file to Excel due to delimeter being a comma, but one of the values also has a comma included.

How the data looks in the .CSV file
"Level", "Type Name"
"Level K2, #6399175", "Type 1"

The trouble is with this value "Level K2, #6399175".

When importing this is what comes out. See picture 1.

How it should look. See picture 2.

Could someone please explain what to do? I mean the delimeter is a comma, but how do I overcome that the value "Level K2, #6399175" includes a comma (as marked)

[arbazhamayun]

Actually the comma I described before is not the problem rather it is because the value is "Level "K2", #6399175". So the problem is the marked: "Level "K2", #6399175"

I don't think there is a quick fix for this

[sandy666]

any chance for example csv file?

[arbazhamayun]

Here you go

[sandy666]

hm, could you change delimiter definition edit: is it possible to change the delimiter to a character that does not exist in the data before export to csv? eg. @ or any other character which doesn't exist in your data

testcsv.jpg

[arbazhamayun]

I suppose it is possible, but how would that help? Since the comma in the value would then also be changed to @ and then the same would happen I suppose?

[sandy666]

comma in the value will not be changed if csv will be genersted properly.
software should know what is delimiter and what isn't
as you can see on the picture example @ divided csv correctly and now will be easier to clean data from unnecessary characters or something what you want to do.

to be clear I am not saying about replace in notepad

you are very economical with your example file, so it's hard to say anything more without representative data

my favorite saying: Excel can do everything but still doesn't read user's wishful thinking

[arbazhamayun]

Yes I get what you are saying now. However I don't control the software from which the CSV is generated. It uses commas as delimiters.

[sandy666]

So use any plain text editor (hope NOT ms word) and use regex to change default delimiter (comma) to any unique character

Regular expression - regex

[arbazhamayun]

Could you give me some example of plain text editors?

[sandy666]

sure...
Notepad++ (free)
EditPadLite (free for personal use)
RegexBuddy
PowerGrep
UltraEdit
EditPadPro

I suggest Notepad++ - free and useful (features)

Always work on copy of your original file!