Archived Forums. Excel IT Pro Discussions. This forum is for general questions and feedback related to Microsoft Excel all versions as they pertain to the IT Pro community. Sign in to vote. I have changed the settings in Control Panel such that the List Separator is ' '. When I open this csv file with notepad I see the file is comma separated instead of the expected pipe separated. Why is this happening and how can I fix it? Thursday, February 16, PM.
KritiSundar, Thanks for reading and commenting on this post. Like you have suggested there are other ways to do this; And a very simple solution too. The intention of the post was to demonstrate how to do with it Excel and no other tool while also implying a question on why is it so tedious for this example.
Once again thanks for your suggested solution. I am exporting an excel file to csv. I changed the List Separator from a comma , to a pipe. The problem is there are commas used in text in the cells and it has changed them to as well. How the heck do you get it to leave the text in the fields alone and only separate the columns???
Hi Denny. Is it just a capital i? It looks bigger. The Region and Language settings are correct, so the list separator is configured with a , not a ;. Best regards, Erik. The intention of the post was to demonstrate how to do with it Excel with office and windows 7 and no other tool while also implying a question on why is it so tedious for this example. Unfortunately not knowing your environment I am unable to help. Hi Barry, I have the same setup as you do, Office and Windows 7.
Yes, very frustrating to perform a simple task. I hate MS office applications in general. They give me high blood pressure. The easiest way I can think of to save as a pipe delimited file, is to save the CSV export, then import to MS Access, then MS Access has an option to export the table as a tab delimited file. Take care not to corrupt any data with datatypes, easiest to set everything as text when you import to MS Access. Hi Keith, Thanks for taking the time to read my post and suggest an alternative to this frustrating simple task Cheers Barry.
Hi Barry, This post really very helpful and I got the result as stated above. But while importing data from excel to my csv file non latin characters are unable to get copied i. Can you please help? Hi Raman, Thanks for taking the time to read my post and I am Glad you have found helpful. In general, Microsoft favours character sets that only include Latin characters, and I have faced the same problems working with Russian characters that you face here with Japanese.
Unfortunately successive Office versions have hidden the solution further and further from view! On Windows: — When you have your Excel file ready, save it in Excel format somewhere safe, so you can return to it if needed. You might need to experiment a little! Good luck, Shaun. Nobody likes an answer that involves installing new software. This didnt work, but not sure if I did it right.
I took a notepad file and saved in Exel, removed the pipes to clean up and do some math. Now I want to add the pipes back in and get back into note pad. Even if I have to copy paste back into notepad.
Cells lig, col a. WriteLine enregistrement Next lig a. Cells r, c a. WriteLine List Next r a. Close End Sub. Hi Franck Thanks for taking the time to read and reply to my post. I am sure others will find your macro useful, thanks for posting. This does not work.
Basic Sample Just to show here the fundamentals. Another method is "Write". Try Google it. Rosetta Rosetta 2, 1 1 gold badge 11 11 silver badges 28 28 bronze badges. Add a comment. CountA ActiveSheet. Rows 1 While activesheet.
Fraser T Fraser T 11 1 1 bronze badge. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Podcast Making Agile work for data science. Then a prompt box pops up, please click the OK button. Now current worksheet is converted to a pipe delimited file.
If you want to save all sheets as pipe delimited files, please repeat the above step 6 to 8 to save them individually using a different file name for each.
Then you can see values are separated by pipe delimiter as below screenshot shown. Note: The other languages of the website are Google-translated. Back to English. Log in. Remember Me Forgot your password? Password Reset. Please enter the email address for your account.
0コメント