LUWS CSV format problems

Share discussions around the Classic Local Unit Website (LUWS).
skiptaylor
Member
Posts: 190
Joined: Fri Mar 23, 2007 11:13 pm
Location: Lancaster CA

LUWS CSV format problems

#1

Post by skiptaylor »

There's a problem in the formatting and naming of the CSV generated file from the LUWS Member Directory page.

1) Perhaps this is by design, but the file extension on a generated CSV file is usually .CSV not the .TXT that the site is generating. This seriously confuses a lot of programs. For instance, Excel loses it's mind and just "loads" the file as if it were any text file and doesn't import it as one would normally want. Perhaps the extension naming as it is was by design as technically it really is a text file, it just means you have to copy it, then save it to a properly named file to get results.

2) The first fieldname given is familyname. This is fine except they are exporting a family name that might be
Lastname, Firstname and Other name. There should be double-quote delimiters so it should export as "Lastname, First name and Other name". Similarly when a family name is Lastname, Firstname, Sr that should be exported as "Lastname, Firstname, Sr." but it's not as there are no double-quote delimiters on this field at all. In other words the familyname field can have multiple commas in it and should be delimited by double-quotes.

I've tried copying this file and saving it as a .CSV named file. It doesn't display properly in either Microsoft Excel or the Open Office programs so I think the problem is at the generating end, not the user end.

It seems that when I was attempting to import Calendar events into Outlook I had a similar problem with the CSV that was generated by the Calendar page as well. It's been a while so I don't remember exactly what the problem was, but I seem to recall wrong data in columns which is generally a problem of improper delimiting.

Any idea if these could be fixed to work right?
Skip Taylor :eek:
Lancaster CA.
russellhltn
Community Administrator
Posts: 34422
Joined: Sat Jan 20, 2007 2:53 pm
Location: U.S.

#2

Post by russellhltn »

I tried it just now from my ward web page. By default, the file was saved as a CSV and the familyname is properly delimited by double quotes. I did confirm that "family name" does contain multiple names. Example: "Smith, Joesph and Emma".

Just a word of warning if you haven't already discovered it: Excel is notorious for altering the format of CSV files. Yes, by default, CSV files are associated to Excel, but if you ever change the file in Excel, a lot of program will not be able to accept it. Not even Access.

Normally in a CSV file, all text fields are delimited by double quotes. But when Excel saves a CSV file it will only double quote delimit the field if there's a comma in it. Otherwise, it strictly comma delimited. And that's decided on a record by record, field by field basis. :eek:
skiptaylor
Member
Posts: 190
Joined: Fri Mar 23, 2007 11:13 pm
Location: Lancaster CA

#3

Post by skiptaylor »

RussellHltn wrote:I tried it just now from my ward web page. By default, the file was saved as a CSV and the familyname is properly delimited by double quotes. I did confirm that "family name" does contain multiple names. Example: "Smith, Joesph and Emma".

Just a word of warning if you haven't already discovered it: Excel is notorious for altering the format of CSV files. Yes, by default, CSV files are associated to Excel, but if you ever change the file in Excel, a lot of program will not be able to accept it. Not even Access.

Normally in a CSV file, all text fields are delimited by double quotes. But when Excel saves a CSV file it will only double quote delimit the field if there's a comma in it. Otherwise, it strictly comma delimited. And that's decided on a record by record, field by field basis. :eek:
Thanks for the rapid reply!

It's amazing what Excel can mutilate. You are right! If I download it from Firefox or Opera, then I get a properly formed and named CSV. Perhaps the only way to fix this in IE would be to disassociate Excel with CSV files.

It's been a while, but in the past I wrote my share of import/export routines using CSV. I wonder if Microsoft ever has.... :D
Skip Taylor :eek:
Lancaster CA.
russellhltn
Community Administrator
Posts: 34422
Joined: Sat Jan 20, 2007 2:53 pm
Location: U.S.

#4

Post by russellhltn »

I did my download in IE. Not sure why, but it did save it rather then open it. I still couldn't quite duplicate what you were seeing - a field with commas that wasn't double quoted.
skiptaylor
Member
Posts: 190
Joined: Fri Mar 23, 2007 11:13 pm
Location: Lancaster CA

#5

Post by skiptaylor »

RussellHltn wrote:I did my download in IE. Not sure why, but it did save it rather then open it. I still couldn't quite duplicate what you were seeing - a field with commas that wasn't double quoted.
The file was being opened by Excel in the browser window. I never got the offer to save it.

I modified the file type settings for XLS and CSV so It now asks before it auto opens the file in the browser. This must be the way Office 2000 installed it. For some reason when Excel opens the file in the browser, the first set of double quotes is removed. Looks like it's an Excel problem. :eek:
Skip Taylor :eek:
Lancaster CA.
User avatar
mkmurray
Senior Member
Posts: 3266
Joined: Tue Jan 23, 2007 9:56 pm
Location: Utah
Contact:

#6

Post by mkmurray »

avskip wrote:The file was being opened by Excel in the browser window. I never got the offer to save it.

I modified the file type settings for XLS and CSV so It now asks before it auto opens the file in the browser. This must be the way Office 2000 installed it. For some reason when Excel opens the file in the browser, the first set of double quotes is removed. Looks like it's an Excel problem. :eek:
I don't believe Office 2003 nor 2007 auto-open like that anymore. They still do the file association, but ask if you want to Open or Save.
russellhltn
Community Administrator
Posts: 34422
Joined: Sat Jan 20, 2007 2:53 pm
Location: U.S.

#7

Post by russellhltn »

mkmurray wrote:I don't believe Office 2003 nor 2007 auto-open like that anymore. They still do the file association, but ask if you want to Open or Save.
That's what I'm experiencing, and as far as I know, I'm using default settings.

Uh, where do you set file associations for IE anyway? I need to point PDF to "reader", not "acrobat".
User avatar
mkmurray
Senior Member
Posts: 3266
Joined: Tue Jan 23, 2007 9:56 pm
Location: Utah
Contact:

#8

Post by mkmurray »

RussellHltn wrote:That's what I'm experiencing, and as far as I know, I'm using default settings.

Uh, where do you set file associations for IE anyway? I need to point PDF to "reader", not "acrobat".
Well, IE just uses the same file associations Windows Explorer uses (I think IE is actually built on top of Windows Explorer).

Anyways, a quick way to change a file association is to right click on a file of that extension and choose "Open With..." and then "Choose Default Program..." From there, select the program or executable you want to be associated with the file and then make sure the "Always use the selected program to open this kind of file" checkbox is checked.

That'll do it for ya.
russellhltn
Community Administrator
Posts: 34422
Joined: Sat Jan 20, 2007 2:53 pm
Location: U.S.

#9

Post by russellhltn »

mkmurray wrote:Well, IE just uses the same file associations Windows Explorer uses (I think IE is actually built on top of Windows Explorer).
That's not been my experience. I know how to change the Windows Explorer association. But IE is getting some settings somewhere else.
User avatar
mkmurray
Senior Member
Posts: 3266
Joined: Tue Jan 23, 2007 9:56 pm
Location: Utah
Contact:

#10

Post by mkmurray »

RussellHltn wrote:That's not been my experience. I know how to change the Windows Explorer association. But IE is getting some settings somewhere else.
Give me an example, if you happen to have one.

The only thing I can think of is if it is an IE plugin then; however, I just went into Internet Options on IE and found a button that says you can change the file associations for IE, but it just takes you to Windows file associations.
Locked

Return to “Classic Ward & Stake Sites (LUWS)”