Hi, so I'm trying to edit a CSV file to add multiple users in order to import them to my site. But, after editing it, it's not saving the text-delimiters that originally came with it.
For example:
changes to:
Please Login or Register to view this content.
Does anyone know what can be done to format it correctly to look like:Please Login or Register to view this content.
Please Login or Register to view this content.
You're asking this as an Excel question, which implies you are opening the file in Excel, editing it, and saving it as a csv file.
Why not just edit it in Notepad, Wordpad, or MS Word?
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Hi TMS,Originally Posted by TMS
The reason why I'm editing this in Excel is because I had a long list of emails (about 200) and needed to create accounts for all of those emails. To make it more efficient for myself I used the formula: =LEFT(G1,FIND("@",G1)-1) to grab the first part of all the emails and copy the values over to the username/display name columns, rather than cutting and pasting it manually in the CSV via Notepad. I also wanted to reuse information like "s2member_level1","15mber","11/30/-0001" and the default (password) column.
I was able to quickly manipulate all the information I wanted, but when saving the CSV file, it came out with the aforementioned incorrect formatting.
What program produced the CSV file with all the "" marks? In a normal CSV file, these "" are not delimiters. The COMMAS are the delimiters. "" in Excel are usually used to surround TEXT within a FORMULA and are not usually for display. If what you show is what you want then I agree with TMS in that Excel is not the program to use.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Typically, you only get quote delimiters around text if the text contains the delimiter. For example, if you have a name and address file and you have multiple address parts in one cell separated by commas.
The CSV was actually created by myself in Notepad, following s2member's template of how to import a user. So if Excel isn't the correct program to use, do you have any suggestions on a program I could use it to make it into the correct format?
What are you importing into? A "full" picture of what exactly you are trying to do would certainly be of help in trying to solve your problems.
I have imported and exported contacts into and out of several email programs and databases and have never run into the requirement of having both quotation marks and a comma for any field. In cases where there were commas within fields, those commas had to be replaced with a different character.
It appears that there really isn't a standard for a CSV and there are a few variations produced by some programs.
I did a search and came up with this http://support.microsoft.com/en-us/kb/2511178 for CSV files generated by Safe Pay. Is this what you are dealing with?
In addition, Open Office's Calc program is reported to insert Quotation Marks along with a Comma upon export as a CSV.
Seeing that Open Office is a free program, you might want to download it and try the Calc program that is included as it seems to export in the format that you are using. It might be safe to assume that it will also import in the same format as it exports.
Last edited by newdoverman; 04-05-2015 at 03:47 PM.
Here's a picture of what I'm importing into:
\1
The extra quotation marks and commas I have in the example are required to grant additional permissions for the users. Thanks for the suggestion, I'll give the Calc program a try.
EDIT: Just tried the Calc program, same thing occurs, it's still removing the delimiters when saving like Excel. I guess I don't really have a choice but to do this manually for all 200 emails...
Last edited by prvimoon; 04-05-2015 at 06:14 PM.
You CAN use Excel to generate your data. You can put static data in four cells and name them static1, static2, static3 and static4.
If the email address is in, say, cell A7, in B7 you put your formula:Formula:
Please Login or Register to view this content.
And, in cell C7, you put the formula:Formula:Please Login or Register to view this content.
The static values will need a single quote at the beginning to avoid Excel removing the double quotes.
Copy JUST the generated values in column C and paste them into a Notepad text file. If necessary, change, the .txt extension to a .csv extension. Import the generated text/csv file into your application.
See the examples attached.
Regards, TMS
Thank you so much TMS!You CAN use Excel to generate your data. You can put static data in four cells and name them static1, static2, static3 and static4.
If the email address is in, say, cell A7, in B7 you put your formula:Formula:Please Login or Register to view this content.
And, in cell C7, you put the formula:Formula:Please Login or Register to view this content.
The static values will need a single quote at the beginning to avoid Excel removing the double quotes.
Copy JUST the generated values in column C and paste them into a Notepad text file. If necessary, change, the .txt extension to a .csv extension. Import the generated text/csv file into your application.
See the examples attached.
Regards, TMS
This works like a charm!
You're welcome. Thanks for the rep.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks