Exporting, changing data and importing via Microsoft Excel

#1

It seems I must be a complete Bozo but I am not able to do this.

I need to export the data list, update a S2member custom field with a “1” for probably 100 users and reimport to update their status.

My problem is, no matter what program I use, I am not able to save a csv that properly maintains quotes around each field.
With Excel, if I simply open the csv, it strips the quotes. If I Import from text file to an empty sheet, the result can have quotes if the 'Text Qualifier" option is set to ‘None’ during import but no matter how I save the resulting file, it ends up with double quotes.

It seemed like the best option I might have had was to use Calc from Open Office but no matter what I did with the import dialog, the app would stop responding. I also tried Google Sheets and WPS Office. They both stripped the quotes.

I will add that I also tried exporting with the Pro ‘Advanced Import/Export Tools’ and found the resulting file to be rather jumbled with data in the wrong columns.

I presume I am doing something dumb?

#2

Hi Daine.

I haven’t used Excel in probably a decade, but I seem to remember there was a way to save the CSV with the correct format…

I use Calc and don’t have a problem with it. Just make sure you’re saving it as CSV and not another format.

Haven’t tried Sheets… Okay, just tried it and it works too.

You mention quotes, but they aren’t used for every single value, so maybe you think it’s wrong because some values don’t use the double quotes, but it’s probably just fine. The quotes are used to enclose values that include the separator (i.e. a comma), if I remember correctly.

With Sheets I exported as comma separated values, and then opened the file in a text editor, and all looks fine.

I hope that helps. :slight_smile:

#3

Indeed, that helps significantly, Cristián!

I don’t know why the Calc importer kept crashing on my system but I have a new laptop on the way so might revisit that. That said, Excel is totally capable of working with, saving and changing file types to work with csv files - it was just the ‘required’ surrounding quotes issue that I was trying to address.

All of the KB and other documentation always shows each field (even empty ones) being surrounded with quotes like:
"","johndoe22","8834ks9903","John","Doe","John Doe","johndoe22@example.com"
And (when viewed in a text editor) each field in a freshly exported S2member user file is surrounded by quotes so yes, I thought it was a requirement for all fields. It was driving me mad that no matter what spreadsheet program I used to edit and save the data, once saved, when that file was viewed in a text editor, all the quotes were gone.

Yes, it makes total sense that surrounding quotes are only a requirement when a field has the separator, in s2s’ case, a ‘,’ and that should definitely be noted more clearly in the documentation. Actually, it seems like a TAB might be a better separator, less likely to show up in data fields, but that’s a different discussion.

In this particular case there is no need for commas to be in any field so I think it will be easy enough to download the file, change or remove any commas in the data and then work from there with a normal CSV file that has no quotes.

BTW, I think it’s time to change your photo. That’s the same one you had back in 2011 or so :grin:

Many thanks…

#4

lol, yeah, I look different now… :sweat_smile:

I see what you mean about all fields having the double quotes around them… And re-reading the documentation for it, it always mentions them.

Well, maybe that’s required and I got confused. I never put attention to it, just opened it with the spreadsheet editor and saved back again as CSV. Please test it importing a single row and see if it works.

#5

Took a few days to get it all together but yes indeed, it works fine and I’m now confident that the double quotes are only needed in situations where the content to be imported contains commas as they would totally confuse things.

I did a search through all my source data and removed the few commas that were accidently used by the subscribers and the standard csv formatted data imported just fine.

I did tell s2member export to save the original data with the UTF-8 Byte Order Marker and maintained that throughout the process though I doubt it would make a difference, especially because of how I did the import.

I’m sure it would have worked but I did not import direct from the final file. Instead I used copy & paste from a text program (in my case Notepad++). That made it easy to test a line or two first and then do the whole thing.

So I believe all is good though I won’t really know until users start yelling about issues - or not :slight_smile:

Thanks!

#6

Well done! Good job. That’s good news.

Thanks for the update. :slight_smile: