Can Outlook be forced to respect csv format for Palm PDA use?

  • Thread starter Thread starter Anthony Susa
  • Start date Start date
A

Anthony Susa

How do we force Outlook to respect csv syntax upon export?

I get a wholly different if I simply import a csv file to Outlook and then
immediately export from Outlook to a csv file!

The exported csv file looks absolutely nothing like the input csv file.

Since I use the original csv file as my master contact list, it's important
to maintain its integrity, despite Outlook shenanigans to the contrary.

Am I doing something wrong (perhaps a missed setting in Outlook that says
please respect the csv format)?

How do you get Microsoft Outlook 2003 on WinXP to respect the csv syntax
upon export from Outlook contacts?
 
How do you get Microsoft Outlook 2003 on WinXP to respect the csv syntax
upon export from Outlook contacts?
Click to expand...

By way of example, try this 3-line test:

Test: Import this 3-line csv file into MS Outlook Contacts:
LAST,FIRST,HOME,WORK,CELL,EMAIL
Susa,Anthony,650-354-0974,408-365-4500,650-438-7744,[email protected]
Susa,Josephine,650-354-0974,650-450-3300,650-438-7743,[email protected]

Immediately export back out to a csv file. Contrary to what you'd think,
you get absolutely nothing anything like the original file. The output file
is over 20 lines long and it contains a tremendous amount of repeated
garbage. In fact, despite the command to output to a csv file, the output
isn't even a csv file. It's some strange ASCII Microsoft format.

Is there any way to force Outlook to output contacts to a normal csv file?
 
Anthony said:
By way of example, try this 3-line test:

Test: Import this 3-line csv file into MS Outlook Contacts:
LAST,FIRST,HOME,WORK,CELL,EMAIL
Susa,Anthony,650-354-0974,408-365-4500,650-438-7744,[email protected]
Susa,Josephine,650-354-0974,650-450-3300,650-438-7743,[email protected]

Immediately export back out to a csv file. Contrary to what you'd think,
you get absolutely nothing anything like the original file. The output
file is over 20 lines long and it contains a tremendous amount of repeated
garbage. In fact, despite the command to output to a csv file, the output
isn't even a csv file. It's some strange ASCII Microsoft format.

Is there any way to force Outlook to output contacts to a normal csv file?
Click to expand...

What version of Outlook? Just tried this with 2K3 and it works as expected,
with text fields surrounded with quotation marks. Note that it exports the
_entire_ contact list and not just the new entries.

Also I hope that those weren't your _real_ phone numbers etc.
 
Anthony said:
By way of example, try this 3-line test:
Click to expand...

Anthony, first, I hope those weren't actual phone numbers!

Second, contrary to one of your earlier posts--where you indicated the
process would take 5 seconds--it's not as simple as you think.

For one thing, Outlook, by default, will export *all* Contact fields to
the CSV file. If you don't want each field, you have to create the
proper map, placing the correct fields in the correct order--each time
you perform the export (time consuming--and the process must be reversed
for each import). Further, Outlook, by default, places the field names
in the first row of the CSV file. I don't know whether this can be
turned off, but I suspect not.

Palm Desktop works similar to Outlook (you must map the fields each
time), but it does not add field names to the first row of the CSV file.

But I am puzzled because in a prior post you seemed to indicate you were
already doing this:
"In my working environment, I hotsync by cable every morning and
afternoon as I arrive and leave from work. During the work day, I modify
the master.csv file (if necessary). The computer is actually a Linux
server tied to the desktop PC via Samba. At the end of the work day, I
download those changes (if any) to the Palm PDA. When I'm away from the
office, I modify the Palm pda contact list. Back at work, I upload those
changes and the cycle resumes."

How exactly were you accomplishing this?
 
Just as an aside, there unfortunately is no singular universal "standard"
for how CSV files are or should be created in generic terms and there is no
single right way to create a CSV file. Different programs create different
variations - some include a "qualifier" around every field such as a double
or single quote, others only use a qualifier if the delimiter character is
included within a field (and the delimitor is not necessarily a comma, it in
fact is the system's list separator character - ergo for Europe depending on
the system's regional setting - the system's separator character in many
cases is the semi-colon). The variations go on but the MS Office products
are the most forgiving and complete of all the products we've seen that deal
with CSV files (especially when it comes to multi-line fields within a row
that contain embedded "end-of-line" characters). The safest approach is to
surround each field by a qualifier. For North American regional settings,
that generally is the double quote character which is what Outlook does when
you export data to CSV format.

Outlook does not provide the same kinds of CSV import/export options (in
terms of structure) you'd find in something like MS Access. If you have MS
Access, export a table from it to text/csv format and you will get a fairly
good what I'm referring to above.

In the case of the example in your message - the file would be "broken" if
the qualifier did not surround the field - something that whatever program
you're using to manage the file would have to support. Import programs can't
assume that the data being imported won't break rules somewhere along the
line.

Lastly, in terms of "In fact, despite the command to output to a csv file,
the output isn't even a csv file. It's some strange ASCII Microsoft
format." - not exactly sure what you mean by that sentence (you didn't
mention your regional settings or language used on the particular system,
some or all of which could have a bearing on the issue). Did you mean that
you cannot read the characters in the file created?

Karl

__________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0
"Power contact importers for MS Outlook '2000/2003"
http://www.contactgenie.com
 
What version of Outlook? Just tried this with 2K3 and it works as expected,
with text fields surrounded with quotation marks. Note that it exports the
_entire_ contact list and not just the new entries.
Also I hope that those weren't your _real_ phone numbers etc.
Click to expand...

My version of Microsoft Outlook is Outlook 2003 (is that a version?).
When I export a CSV file, I get many lines per record, with the field
identifiers and an equal sign ... none of which is in the original csv
file.

Maybe I'm outputting the CSV file wrongly?

Is there a difference between a DOS csv file and a Windows CSV file?

Tony Susa
 
But I am puzzled because in a prior post you seemed to indicate you were
already doing this:
"In my working environment, I hotsync by cable every morning and
afternoon as I arrive and leave from work. During the work day, I modify
the master.csv file (if necessary). The computer is actually a Linux
server tied to the desktop PC via Samba. At the end of the work day, I
download those changes (if any) to the Palm PDA. When I'm away from the
office, I modify the Palm pda contact list. Back at work, I upload those
changes and the cycle resumes."

How exactly were you accomplishing this?
Click to expand...

Hi Tinman,

I do thank you for all your help and advice as do many others who lurk
here.

In one of my posts I said it was working except for a "minor glitch" (see
http://groups.google.com/group/comp...="tony+susa"+"glitch"&rnum=2#0619b85dc4587bd8)
which I found with a google groups search for "Tony Susa minor glitch".

This is the minor glitch.

I have to hand edit the exported CSV file from Outlook to get it into the
csv format of the input file. Luckily it's not hard as I'm a whiz at text
editing so it only takes a short while ... but you can see why I am asking
for how to export the same csv file out of Outlook as I imported.

From the helpful responses here, it seems that there MUST be a way to
export a csv file out of Microsoft Outlook 2003 where the format is the
same as the input format of:

Are you SURE you can import a 3-line CSV file into Outlook and then export
the same format file? I can't. (Maybe I'm just dumb or more than likely
ignorant, but I just can't sem to figure out how to do that, hence this
question).

Tony Susa
 
Just tried this with 2K3 and it works as expected,
with text fields surrounded with quotation marks.
Click to expand...

Hi J. Clarke,

I forgot to ask. Does it export just the VALUE of the fields or both the
value and the FIELD IDENTIFIER?

In my case, I get the Field Identifier (all with quotes that I'll omit),
e.g.,
LAST NAME = Susa
FIRST NAME = Anthony
etc.

Instead of what I gave it:
Susa,Anthony,etc.

The question, which seems to be answered by a "No" is whether or not
Outlook can input a one-line csv file of the format:
last,first,cell,home,work,email
and output the same one line csv file.

If Outlook can output csv, I can't find how; hence, this quest.

Tony Susa
 
Just as an aside, there unfortunately is no singular universal "standard"
for how CSV files are or should be created in generic terms and there is no
single right way to create a CSV file.
Click to expand...

Indeed. That's why I ask if Outlook can input a csv file of the Excel
one-line-per-record format and then output that same format.

If it can, I can't get Outlook to do it.

For exmaple, this one-line input file is written out by Excel:
"last","first","cell","home","work","email"

That one-line Excel csv file is imported by Outlook 2003 fine.
And, it is transferred to the Palm PDA (Treo650) just fine.

Yet, Outlook is the only program that doesn't output back that same file.
The output from Outlook looks absolutely nothing like the one-line input.

Hence my question to the experts (I am decidedly, NOT, an expert),
Tony Susa
 
Lastly, in terms of "In fact, despite the command to output to a csv file,
the output isn't even a csv file. It's some strange ASCII Microsoft
format." - not exactly sure what you mean by that sentence (you didn't
mention your regional settings or language used on the particular system,
some or all of which could have a bearing on the issue). Did you mean that
you cannot read the characters in the file created?
Click to expand...

All I meant by that was that I create a one-line csv file of the format:
"last","first","cell","home","work","email"

When I read it into and out of Excel, the file structure remains exactly.
By that, I'm assuming that is the "standard csv format".
I repeat that I assume that is the standard csv format because Excel is the
standard program that is set to read/write csv files (at least that's what
Microsoft Office seems to set as the default application for csv files on
my American standard system).

So, assuming Microsoft Excel is the de-facto csv standard reader; and
noting that Excel has no problem reading in my one-line csv file and
outputting the exact same format file, I made the assmption that this is
the de-facto csv syntax.

Now, trying the same input output (actually import export) test with
Microsoft Outlook gets us a multiline file that bears no resemblence
structurally to that of the input file. Many fields are added. Field names
are added. The one-line file turns into a multi-line file. etc.

My question was whether there was a way to make Microsoft Outlook export
the same file it imported much like Microsoft Excel seems to write the same
format file it read in.

I hope it's clearer now as to the question.
The answer isn't so clear since some people said they could do it and
others said they couldn't (I'm in the couldn't do it crowd myself).

Tony Susa
 
Anthony, first, I hope those weren't actual phone numbers!
Click to expand...

Please ignore the phone numbers listed. I'll just use names from here on
in. Without the phone numbers. If you delete the numbers, that would be
great. Thanks,

Tony Susa
 
Tony said:
Hi J. Clarke,

I forgot to ask. Does it export just the VALUE of the fields or both the
value and the FIELD IDENTIFIER?

In my case, I get the Field Identifier (all with quotes that I'll omit),
e.g.,
LAST NAME = Susa
FIRST NAME = Anthony
etc.

Instead of what I gave it:
Susa,Anthony,etc.

The question, which seems to be answered by a "No" is whether or not
Outlook can input a one-line csv file of the format:
last,first,cell,home,work,email
and output the same one line csv file.

If Outlook can output csv, I can't find how; hence, this quest.
Click to expand...

What procedure are you following to get your csv files from Outlook?

If I do "File/Import and Export/Export to a file/comma separated
values/contacts" I get a row of headers, with each header enclosed in
quotation marks, then a row for each entry in the contact list.

If I just need a few fields, then further along there's a "custom mapping"
option that lets me specify which Outlook fields are to be matched to which
headers in the csv file.

There is no way to export a single contact list entry to a csv file directly
that I know of.

The difference between CSV(DOS) and CSV(Windows) is that different mappings
are used for characters above 128.
 
Tony said:
All I meant by that was that I create a one-line csv file of the
format: "last","first","cell","home","work","email"

When I read it into and out of Excel, the file structure remains
exactly. By that, I'm assuming that is the "standard csv format".
Click to expand...

Not really. But Excel will preserve the file's structure, as best it
can, since you are literally "opening" the file, not "importing" it. Two
very different things (the latter is what you are doing in Outlook).
Now, trying the same input output (actually import export) test with
Microsoft Outlook gets us a multiline file that bears no resemblence
Click to expand...

It's not really a multi-line file. If you export CSV (DOS or Windows
format) there will be one record per line. It's possible that your
viewer is not able to display the (numerous) amount of fields per line
(when exporting using the default settings within Outlook).

structurally to that of the input file. Many fields are added. Field
names are added. The one-line file turns into a multi-line file. etc.
Click to expand...

I went over this in my prior reply. When you import a CSV file into
Outlook's Contacts database, you are populating (hopefully by properly
mapping) only a small portion of the total number of fields in the
actual Contacts database.

So when you export, Outlook does not remember--nor care--how that data
got in there. By default it will export ALL fields, even if 98% of them
are empty. This is why you are seeing so many more fields. So before you
export you *must* click "Map Custom Fields." Then you need to "Clear
Map" on the right-hand side (the CSV file side). Then you drag fields
from the left pane (the Contacts database) to the right pane--in the
exact order you want them to be in the CSV file. The next time you
export (and choose "Map Custom Fields") Outlook should remember the last
mapping (usually).

What Outlook also does, and one could call this a "standard" if they
wished, is add the field names to the first row of the CSV database.
Some applications, such as MS Access, can read the first column's values
and use that information to create field names. But I don't know if this
behavior in Outlook can be turned off (I haven't really looked).

Further, Palm Desktop will also export all fields if you don't specify
an export map. But Palm Desktop *doesn't* add the field names to the
first row of the CSV file.

So you can either map the fields properly in Outlook, and manually
delete the first row in the CSV file, or use Palm Desktop to do the
export.

My question was whether there was a way to make Microsoft Outlook
export the same file it imported much like Microsoft Excel seems to
write the same format file it read in.
Click to expand...

See above.
 
Tony said:
Hi Tinman,

I do thank you for all your help and advice as do many others who lurk
here.
Click to expand...

I don't know what you mean by " lurk here," since I don't generally
lurk. But this message is cross-posted all over Usenet. You are probably
just seeing replies from people who do not normally post in your usual
NG.
 
Would have to echo John's question - what exact steps are you using to
export your file? If you are getting one field per line as per your example,
then that is certainly not a CSV format of any kind but have never seen that
layout from Outlook's import/export process from any version of Outlook.
Don't even see that format using any of the save functions.

You are attemping to export the Outlook data using Outlook's OWN
import/export function correct - not from any other addin/program?
(sorry if that sounds like an obvious question but it does have to be
asked in this case).

Out of curiousity, what results do you get if you export your data directly
to Excel from Outlook?

Karl

__________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0
"Power contact importers for MS Outlook '2000/2003"
http://www.contactgenie.com
 
Back
Top