Call for programmers

AvianStewardess · Mar 28, 2015

The_Traveler said:
not exactly, I only need to remove the quotes in the first (of 7 or 8) columns.

I believe there is a key-stroke that lets you select columns of text and do search/replace within that selection. Maybe it was Control+Shift+F8 ??? Back when I was employed, I used Word on text files to do the same sort of thing you are wanting to do.

The_Traveler · Mar 28, 2015

this is a sample download of bulk metadata from a test folder with only a single image on my web site
(in a typical folder there would be many more lines up to 100)
Note that header line, first and last columns have no double quotes and that's what the upload expects.

File Name,Title,Caption,Copyright,Alt attribute,Keywords,Zenfolio Photo ID [DO NOT EDIT]
_DSC0181-Edit.jpg,_DSC0181-Edit,,"Copyright 2015 LewLortonPhoto.com",,,1077754897

this is the file as edited in a spreadsheet, filling in name, caption, keywords, etc.

this is the file after I have filled in the missing data fields and exported as a csv.
note the double quotes on every field but last one (formatted as number)

"File Name","Title","Caption","Copyright","Alt attribute","Keywords","Zenfolio Photo ID [DO NOT EDIT]"
"_DSC0181-Edit.jpg","grazing horse","horse in bamboo thicket","Copyright 2015 LewLortonPhoto.com",,"laos",1077754897

this is the file as I need it in order to upload successfully.
note no double quotes in header line
note no double quotes in first column.

File Name,Title,Caption,Copyright,Alt attribute,Keywords,Zenfolio Photo ID [DO NOT EDIT]
_DSC0181-Edit.jpg,"grazing horse","horse in bamboo thicket","Copyright 2015 LewLortonPhoto.com",,"laos",1077754897

What I would like to have is a simple application that will take the spreadsheet.csv file and return an edited_for_upload file

I can do it the hard way with text editor but I have to do this a lot and I will make mistakes and I would rather have a simpler less tedious way to do a repetitive task.

3Dogs · Mar 28, 2015

That is where a macro would help. Takes all the 'repetative' data entry away. You only need to tell it what to do one time. Should only take a few seconds to 'run' against whole file. Trust me, it isn't hard after you do it one time...

The_Traveler · Mar 28, 2015

What text editor has macro than allows conditionals and recognizes columns?

snowbear · Mar 28, 2015

PM sent

3Dogs · Mar 28, 2015

I mentioned a few in my previous post. Notepad++ is one of the popular free open source variety. A quick search shows ways in selecting columns (ie Notepad Column Mode Editing

One thing I would check first in your spreadsheet app is to save as a different format (ie just a text file and not a csv). It may not insert all those quotes from the get-go.

dxqcanada · Mar 28, 2015

If this is a Windows 7/8 environment ... I would think using a PowerShell script ... using the -replace could work also (I am not that experienced with PS) ?

3Dogs · Mar 28, 2015

Your situation seems to be a common question/complaint about oö calc. Here are a few suggestions found in a search:

Saving to CSV file always adds quotation marks in OpenOffice - Super User

CSV unnecessary quotation help View topic Apache OpenOffice Community Forum

The_Traveler · Mar 28, 2015

Thanks, this has turning into a much larger problem than I realized and I appreciate all the help but don't want to burden you all any longer.

I think I will go along using column mode editing and a Simple replace and not attempt to over-think this.
Thanks all, especially Snowbear, for the help and the advice.

Dave442 · Mar 28, 2015

Lew, I would not give up. It looks like the problem is more related to the OO Calc. With what you do have as a result should clear up with the replacement of the " with nothing.

However as you need the " around some fields I would do the following:
When you make the entries in the spreadsheet then include a * before and after the fields that need the ", then in the text editor replace all the " with nothing and then replace all the * with the " character.
Worked for me.

On fields like your Copyright that is the same in the before and after, I would do a global replace of that field adding the * to the start and end.

You can use any character as the place holder to change out later with the ", but I find the * works and is easy to see that it is there.

I have had to do replacements in csv files that come from overseas with the decimal and comma positions reversed and I wish I had some of these other programming skills mentioned in this thread.