How to convert .csv file into excel with the separator in the csv file is (;)

[Jagdev Singh]

Hi Experts

I want to convert the CSV file into excel. THe seperator used in the CSV file is (";" instead of (,). I find a solution online which says open the CSV and add (sep=Semicolon(";") in the beginning and then open it in excel. I opened the CSV file in notepad and added the code and opened the CSV in excel. It worked for the first line and rest of the line didn't picked it. Is there any simple way to deal with all the records at the one go.

Regards,
JD

 

[Asheesh]

Copy paste the data from notepad to excel...and the use "Text To Columns" in "Data Tools" group..then click on Delimited on the pop up and click Next and then click on Semicolon..and click next

Note: you must select the entire column first

 

[Jagdev Singh]

Hi Guys

It worked! the issue was there were "'" was used in the files before every entry. I just replaced it in notepad and run the above trick.

Regards,
JD

 

[Jagdev Singh]

Thanks Asheesh! I tried that trick as well before posting the query. The issue was with the special character "'" used in the each entries. Hope the above trick help others with the same issue.

Regards,
JD

 

[Hui]

JD

I think you can totally avoid the Notepad step

Simply Open Excel
Goto File, Open
Select Text Files
In the File Name dialog, locate your file and Open
This will automatically run the Text to Column Wizard
Import as appropriate using a Custom Field Delimiter of ;

Once in Excel tidy up columns as required.

 

[Jagdev Singh]

Hi Hui

Even this way is also worked fine. Thanks for staring this trick with us.

Regards,
JD

 

[Hui]

When using the Open File dialog, DONT worry about the file extension
Select the file type if you know it
In the filename box simply type *.XYZ or whatever your file type is
(Excel will only list the *.XYZ file types)
Find the file
Open

 

[Jagdev Singh]

Thanks Hui! for all your support and help.

Regards,
JD