"No named ranges" error importing from xls file...

  • Thread starter Thread starter Alphonse
  • Start date Start date
A

Alphonse

Hi,
I have received and Excel file with Contact information; all the headings,
Title, First Name, etc. are correctly placed at the top of each column, but
when I try to import the xls file I receive a message stating "The MS Excel
file [path\filename] has no named ranges. Use Excel to name the range of
data you want to import."
I don't understand what it means by "named ranges"... I have looked in XL
but have not found anything that helps. Can someone please tell me how to do
this?
 
Alphonse said:
I have received and Excel file with Contact information; all the
headings, Title, First Name, etc. are correctly placed at the top of
each column, but when I try to import the xls file I receive a
message stating "The MS Excel file [path\filename] has no named
ranges. Use Excel to name the range of data you want to import."
I don't understand what it means by "named ranges"... I have looked
in XL but have not found anything that helps. Can someone please tell
me how to do this?
Click to expand...

Did you look in Excel's Help? Here is a description from that Help:

Name cells in a workbook

You can use the labels of columns and rows on a worksheet to refer to the
cells within those columns and rows. Or you can create descriptive names to
represent cells, ranges of cells, formulas, or constant values. Labels can
be used in formulas that refer to data on the same worksheet; if you want to
represent a range on another worksheet, use a name.

You can also create 3-D names that represent the same cell or range of cells
across multiple worksheets.

Name a cell or a range of cells

Select the cell, range of cells, or nonadjacent selections that you want to
name.
Click the Name box at the left end of the formula bar .

Type the name for the cells. Press ENTER.
 
To avoid the "Named Range" requirement for the Excel file using the Outlook
import wizard, just save the worksheet with the contact info as a CSV file
and import the CSV file in Outlook instead.

Karl

___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com
 
The problem with converting the xls file into a csv file is that any
carriage returns in the Notes field creates a new record or entry, not only
creating bogus contacts but also rendering the Notes incomplete. Also, for
those records which have names that contain special characters, meaning any
letter with an accent mark, thus, Spanish, French and German to name but
three, such characters get often replaced by a ? or some other ANSI
character. As if this were not enough, converting into CSV causes anything
that has a comma, like addresses and notes, will shift all data beyond it to
another field, pushing some past the last column. A better solution to the
commas problem is to convert to a Tab Separated Values, which actually saves
it as a txt file, but even this does not save you from the first two
problems I stated.

Here is a better way:

To use a Microsoft Excel workbook to import information, cancel everything
in Microsoft Outlook, and then open up the file in Excel. To define a named
range in Microsoft Excel, follow these steps:

1. Select the range of cells (including column headers) that you want to
import.

2. On the Insert menu, point to Name, and then click Define.

3. In the Names In Workbook box, type a name (the name may not contain
spaces nor should it be the same as one of the column header names) for the
range that you specified, and then click Add.

4. Click OK, and on the File menu, click Save.

5. Save and quit the workbook.

(edited excerpt from http://support.microsoft.com/kb/196743/en-us)

-or-

1. Select the range of cells (including column headers) that you want to
import.

2. While that block is selected, type a name for the range in the box above
the grid, to the left of the formula bar (it usually has a cell number in it
and if you hover over the box it should say Name Box).

3. Save and close the spreadsheet and try to import into Outlook again.

(formatted answer given by Jocelyn Fiorello, MVP - Outlook


--
Cheers,
Alphonse


To avoid the "Named Range" requirement for the Excel file using the Outlook
import wizard, just save the worksheet with the contact info as a CSV file
and import the CSV file in Outlook instead.

Karl

___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com



Alphonse said:
Hi,
I have received and Excel file with Contact information; all the headings,
Title, First Name, etc. are correctly placed at the top of each column,
but
when I try to import the xls file I receive a message stating "The MS
Excel
file [path\filename] has no named ranges. Use Excel to name the range of
data you want to import."
I don't understand what it means by "named ranges"... I have looked in XL
but have not found anything that helps. Can someone please tell me how to
do
this?
Click to expand...
 
Brian,
Thank you for your response, but that explanation from the help file, which
does not apply in this case, is the reason why I resorted to this newsgroup.
I did, however, find the answer by searching the newsgroup via the web
interface which allows me to search as far back as 2004 (my OE does not
download headers that far back and I didn't find how to make it do it). The
applicable explanation is found in the Alternative Method at the bottom of
this article: http://support.microsoft.com/kb/196743/en-us
The reasons why the Alternative method works better are given in my post to
Karl Timmermans on this same thread.
--
Cheers,
Alphonse


Alphonse said:
I have received and Excel file with Contact information; all the
headings, Title, First Name, etc. are correctly placed at the top of
each column, but when I try to import the xls file I receive a
message stating "The MS Excel file [path\filename] has no named
ranges. Use Excel to name the range of data you want to import."
I don't understand what it means by "named ranges"... I have looked
in XL but have not found anything that helps. Can someone please tell
me how to do this?
Click to expand...

Did you look in Excel's Help? Here is a description from that Help:

Name cells in a workbook

You can use the labels of columns and rows on a worksheet to refer to the
cells within those columns and rows. Or you can create descriptive names to
represent cells, ranges of cells, formulas, or constant values. Labels can
be used in formulas that refer to data on the same worksheet; if you want to
represent a range on another worksheet, use a name.

You can also create 3-D names that represent the same cell or range of cells
across multiple worksheets.

Name a cell or a range of cells

Select the cell, range of cells, or nonadjacent selections that you want to
name.
Click the Name box at the left end of the formula bar .

Type the name for the cells. Press ENTER.
 
Interesting results you are encountering

When Excel saves a worksheet to a CSV file - it does so using double quotes
as the field qualifiers which handles any fields that have embedded comma's
(pretty much a universal method of dealing with that issue). It's never been
an issue for us in the all the years we've been dealing with Outlook
importing/exporting nor has anyone else ever told us that they couldn't
import a file after saving an XLS file to CSV. Ergo, this is the first time
have seen a problem report about how Excel saves a CSV file.

In terms of multi-line notes - Outlook does import those for the same reason
mentioned above - the notes field is surrounded by double-quote qualifiers
(in fact just re-tested Outlook's import/export wizard with a file that
contains both the conditions above and all worked just as advertised).

In terms of characters being replaced, may have something to do with your
system's configuration but don't currently have a test file with those
conditions to test the Outlook import/export wizard so can't comment.

Having said all of that, you have also not provided any version information
regarding the versions of Outlook or Excel you're using - or operating
system in use etc (my response above applies to all versions of
Excel/Outlook '2000 and above). Would also be interesting to know what your
regional settings are.

Karl
___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com



Alphonse said:
The problem with converting the xls file into a csv file is that any
carriage returns in the Notes field creates a new record or entry, not
only
creating bogus contacts but also rendering the Notes incomplete. Also, for
those records which have names that contain special characters, meaning
any
letter with an accent mark, thus, Spanish, French and German to name but
three, such characters get often replaced by a ? or some other ANSI
character. As if this were not enough, converting into CSV causes anything
that has a comma, like addresses and notes, will shift all data beyond it
to
another field, pushing some past the last column. A better solution to the
commas problem is to convert to a Tab Separated Values, which actually
saves
it as a txt file, but even this does not save you from the first two
problems I stated.

Here is a better way:

To use a Microsoft Excel workbook to import information, cancel everything
in Microsoft Outlook, and then open up the file in Excel. To define a
named
range in Microsoft Excel, follow these steps:

1. Select the range of cells (including column headers) that you want to
import.

2. On the Insert menu, point to Name, and then click Define.

3. In the Names In Workbook box, type a name (the name may not contain
spaces nor should it be the same as one of the column header names) for
the
range that you specified, and then click Add.

4. Click OK, and on the File menu, click Save.

5. Save and quit the workbook.

(edited excerpt from http://support.microsoft.com/kb/196743/en-us)

-or-

1. Select the range of cells (including column headers) that you want to
import.

2. While that block is selected, type a name for the range in the box
above
the grid, to the left of the formula bar (it usually has a cell number in
it
and if you hover over the box it should say Name Box).

3. Save and close the spreadsheet and try to import into Outlook again.

(formatted answer given by Jocelyn Fiorello, MVP - Outlook


--
Cheers,
Alphonse


To avoid the "Named Range" requirement for the Excel file using the
Outlook
import wizard, just save the worksheet with the contact info as a CSV file
and import the CSV file in Outlook instead.

Karl

___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com



Alphonse said:
Hi,
I have received and Excel file with Contact information; all the
headings,
Title, First Name, etc. are correctly placed at the top of each column,
but
when I try to import the xls file I receive a message stating "The MS
Excel
file [path\filename] has no named ranges. Use Excel to name the range of
data you want to import."
I don't understand what it means by "named ranges"... I have looked in XL
but have not found anything that helps. Can someone please tell me how to
do
this?
Click to expand...
Click to expand...
 
Sorry about the lack of version info... oversight.
Win XP Pro SP2
Office 2002 SP3
Regional Settings:
Standards and formats - English US
Location - US
Languages - English, French and Spanish

About the double-quotes, I have noticed them within the CSV files, I have
always understood the concept, and for ever I have avoided using dbl-quotes
in the Notes field to avoid OL a possible confusion, but I have not
understood why, nonetheless, all multi-line notes get imported incomplete.
If I bother to go and fix the file by replacing all line returns to
something like semi-colons, then all will turn out fine... I just took it as
a glitch and adapted.

Humor me a bit and try this: export a couple of contacts with multi-line
notes as a CSV file, then open Excel, then open the CSV file. If you ask,
why not open it directly from Explorer, the answer is that any numbers that
begin with 0, like some foreign countries' phone #s, zip codes, or overseas
access codes, will all lose that initial 0.

In terms of special characters changing, you can test by using
Ctrl+SingleQuote then type a vowel to obtain an accented vowel, or
Ctrl+Comma, then type C for c with a little tail (cedilla) or Ctrl+Colon
(Shift+Semicolon), then a vowel for the two little dots over the vowel.
--
Cheers,
Alphonse


Interesting results you are encountering

When Excel saves a worksheet to a CSV file - it does so using double quotes
as the field qualifiers which handles any fields that have embedded comma's
(pretty much a universal method of dealing with that issue). It's never been
an issue for us in the all the years we've been dealing with Outlook
importing/exporting nor has anyone else ever told us that they couldn't
import a file after saving an XLS file to CSV. Ergo, this is the first time
have seen a problem report about how Excel saves a CSV file.

In terms of multi-line notes - Outlook does import those for the same reason
mentioned above - the notes field is surrounded by double-quote qualifiers
(in fact just re-tested Outlook's import/export wizard with a file that
contains both the conditions above and all worked just as advertised).

In terms of characters being replaced, may have something to do with your
system's configuration but don't currently have a test file with those
conditions to test the Outlook import/export wizard so can't comment.

Having said all of that, you have also not provided any version information
regarding the versions of Outlook or Excel you're using - or operating
system in use etc (my response above applies to all versions of
Excel/Outlook '2000 and above). Would also be interesting to know what your
regional settings are.

Karl
___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com


The problem with converting the xls file into a csv file is that any
carriage returns in the Notes field creates a new record or entry, not only
creating bogus contacts but also rendering the Notes incomplete. Also, for
those records which have names that contain special characters, meaning any
letter with an accent mark, thus, Spanish, French and German to name but
three, such characters get often replaced by a ? or some other ANSI
character. As if this were not enough, converting into CSV causes anything
that has a comma, like addresses and notes, will shift all data beyond it to
another field, pushing some past the last column. A better solution to the
commas problem is to convert to a Tab Separated Values, which actually saves
it as a txt file, but even this does not save you from the first two
problems I stated.

Here is a better way:
To use a Microsoft Excel workbook to import information, cancel everything
in Microsoft Outlook, and then open up the file in Excel. To define a named
range in Microsoft Excel, follow these steps:

1. Select the range of cells (including column headers) that you want to
import.
2. On the Insert menu, point to Name, and then click Define.
3. In the Names In Workbook box, type a name (the name may not contain
spaces nor should it be the same as one of the column header names) for the
range that you specified, and then click Add.
4. Click OK, and on the File menu, click Save.
5. Save and quit the workbook.
(edited excerpt from http://support.microsoft.com/kb/196743/en-us)
-or-
1. Select the range of cells (including column headers) that you want to
import.
2. While that block is selected, type a name for the range in the box above
the grid, to the left of the formula bar (it usually has a cell number in it
and if you hover over the box it should say Name Box).
3. Save and close the spreadsheet and try to import into Outlook again.
(formatted answer given by Jocelyn Fiorello, MVP - Outlook
--
Cheers,
Alphonse


To avoid the "Named Range" requirement for the Excel file using the
Outlook
import wizard, just save the worksheet with the contact info as a CSV file
and import the CSV file in Outlook instead.

Karl

___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com



Alphonse said:
Hi,
I have received and Excel file with Contact information; all the
headings,
Title, First Name, etc. are correctly placed at the top of each column,
but
when I try to import the xls file I receive a message stating "The MS
Excel
file [path\filename] has no named ranges. Use Excel to name the range of
data you want to import."
I don't understand what it means by "named ranges"... I have looked in XL
but have not found anything that helps. Can someone please tell me how to
do
this?
Click to expand...
Click to expand...
 
In terms of exporting/importing contacts with multi-line notes - not
something that is new to us, been dealing with data movement in and out of
Outlook for quite a while now and have had hundreds of data samples
submitted to us since the release of our first product more then 4 yrs ago.
Moving multiline "notes" (body) data from any data source including CSV is
something that's done all the time and as much as I'd like to say that this
would be a good reason to use a ContactGenie product, reality is that for
importing multi-line notes that are "properly" formatted, the Outlook import
wizard works perfectly (it's never failed on us yet). That includes a
multi-line CSV file with one or more embedded double-quote characters. For
what it's worth, also added all the characters you mentioned copying the
various characters from two different fonts (MS Serif & Arial) and
everything was imported with no problems.

Since you are using Outlook '2002 for which the PST standard is ANSI versus
Unicode (if memory serves me correctly), is it possible you're trying to
import Unicode data into a PST file that doesn't support Unciode?

Also FYI - a simplistic explanation of how Excel converts text fields to CSV
that contain comma's and double quotes is that an additional double quote is
inserted in front of every double-quote found - after that's completed the
entire field is bracketed by one double-quote at the beginning and end of
the particular field. Ergo, when a CSV file is then processed - the
double-quote pairs are treated as field content with each resulting pair
translated as a single double quote (providing the CSV translator deals with
everything in a conventional manner - which the MS CSV translator does in
all MS products I've worked with so far). The only caveat with the term of
CSV in terms of MS translators is that it uses the list separator character
which is not a comma in many other regions so "CSV" is not completely
accurate especially when sending those files from one country to another
using different regional settings (these files in reality then become
custom-delimited files versus comma-separated).

As for the leading zeros issue in Excel - that falls in the realm of Excel
worksheet formatting. If leading zeros are displayed within a given field in
an Excel worksheet then those zeros will be saved in a CSV file (which also
means that the column has likely been formatted as "text"). Anything else,
I'll leave to the Excel experts.

To get back to point - this started with a simple comment of saving an Excel
worksheet to CSV and importing the CSV file to Outlook IF you didn't want to
deal with "named ranges". On that note, still haven't heard anything other
then the potential issue of Ansi vs Unicode that should cause problems
importing a CSV file (saved by Excel) into Outlook. This is also the first
time I've heard of anyone in the newgroup having similar problem(s) after
following that suggestion.

Karl
___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com



Alphonse said:
Sorry about the lack of version info... oversight.
Win XP Pro SP2
Office 2002 SP3
Regional Settings:
Standards and formats - English US
Location - US
Languages - English, French and Spanish

About the double-quotes, I have noticed them within the CSV files, I have
always understood the concept, and for ever I have avoided using
dbl-quotes
in the Notes field to avoid OL a possible confusion, but I have not
understood why, nonetheless, all multi-line notes get imported incomplete.
If I bother to go and fix the file by replacing all line returns to
something like semi-colons, then all will turn out fine... I just took it
as
a glitch and adapted.

Humor me a bit and try this: export a couple of contacts with multi-line
notes as a CSV file, then open Excel, then open the CSV file. If you ask,
why not open it directly from Explorer, the answer is that any numbers
that
begin with 0, like some foreign countries' phone #s, zip codes, or
overseas
access codes, will all lose that initial 0.

In terms of special characters changing, you can test by using
Ctrl+SingleQuote then type a vowel to obtain an accented vowel, or
Ctrl+Comma, then type C for c with a little tail (cedilla) or Ctrl+Colon
(Shift+Semicolon), then a vowel for the two little dots over the vowel.
--
Cheers,
Alphonse


Interesting results you are encountering

When Excel saves a worksheet to a CSV file - it does so using double
quotes
as the field qualifiers which handles any fields that have embedded
comma's
(pretty much a universal method of dealing with that issue). It's never
been
an issue for us in the all the years we've been dealing with Outlook
importing/exporting nor has anyone else ever told us that they couldn't
import a file after saving an XLS file to CSV. Ergo, this is the first
time
have seen a problem report about how Excel saves a CSV file.

In terms of multi-line notes - Outlook does import those for the same
reason
mentioned above - the notes field is surrounded by double-quote qualifiers
(in fact just re-tested Outlook's import/export wizard with a file that
contains both the conditions above and all worked just as advertised).

In terms of characters being replaced, may have something to do with your
system's configuration but don't currently have a test file with those
conditions to test the Outlook import/export wizard so can't comment.

Having said all of that, you have also not provided any version
information
regarding the versions of Outlook or Excel you're using - or operating
system in use etc (my response above applies to all versions of
Excel/Outlook '2000 and above). Would also be interesting to know what
your
regional settings are.

Karl
___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com


The problem with converting the xls file into a csv file is that any
carriage returns in the Notes field creates a new record or entry, not
only
creating bogus contacts but also rendering the Notes incomplete. Also, for
those records which have names that contain special characters, meaning
any
letter with an accent mark, thus, Spanish, French and German to name but
three, such characters get often replaced by a ? or some other ANSI
character. As if this were not enough, converting into CSV causes anything
that has a comma, like addresses and notes, will shift all data beyond it
to
another field, pushing some past the last column. A better solution to the
commas problem is to convert to a Tab Separated Values, which actually
saves
it as a txt file, but even this does not save you from the first two
problems I stated.

Here is a better way:
To use a Microsoft Excel workbook to import information, cancel everything
in Microsoft Outlook, and then open up the file in Excel. To define a
named
range in Microsoft Excel, follow these steps:

1. Select the range of cells (including column headers) that you want to
import.
2. On the Insert menu, point to Name, and then click Define.
3. In the Names In Workbook box, type a name (the name may not contain
spaces nor should it be the same as one of the column header names) for
the
range that you specified, and then click Add.
4. Click OK, and on the File menu, click Save.
5. Save and quit the workbook.
(edited excerpt from http://support.microsoft.com/kb/196743/en-us)
-or-
1. Select the range of cells (including column headers) that you want to
import.
2. While that block is selected, type a name for the range in the box
above
the grid, to the left of the formula bar (it usually has a cell number in
it
and if you hover over the box it should say Name Box).
3. Save and close the spreadsheet and try to import into Outlook again.
(formatted answer given by Jocelyn Fiorello, MVP - Outlook
--
Cheers,
Alphonse


To avoid the "Named Range" requirement for the Excel file using the
Outlook
import wizard, just save the worksheet with the contact info as a CSV
file
and import the CSV file in Outlook instead.

Karl

___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com



Alphonse said:
Hi,
I have received and Excel file with Contact information; all the
headings,
Title, First Name, etc. are correctly placed at the top of each column,
but
when I try to import the xls file I receive a message stating "The MS
Excel
file [path\filename] has no named ranges. Use Excel to name the range of
data you want to import."
I don't understand what it means by "named ranges"... I have looked in
XL
but have not found anything that helps. Can someone please tell me how
to
do
this?
Click to expand...
Click to expand...
Click to expand...
 
Thanks for a very clear explanation. It all makes perfect sense and I do
believe everything that you are telling me, so I imagine that there is
something on my side that has caused this in my computer. I will retry all
this in my computer, and if part or all fails, I will try in another
computer with a clean install of Windows XP and a full, clean install of
Office 2002. Maybe some of this has to do with the import filters selections
of the Office2k2 installation?

Well, I do appreciate your taking the time to tell me all this because I
will, one way or another--clean re-install my PC-- make it work for me and
avoid me all the work-arounds I have been taking... so this might take me a
few days to sort out. Well, the tunnel has become a ditch, and now I just
have to find the path to climb out.

On a yet more positive side, if I am the first to have experienced these
things, let us hope I keep being the only one.
--
Cheers,
Alphonse


In terms of exporting/importing contacts with multi-line notes - not
something that is new to us, been dealing with data movement in and out of
Outlook for quite a while now and have had hundreds of data samples
submitted to us since the release of our first product more then 4 yrs ago.
Moving multiline "notes" (body) data from any data source including CSV is
something that's done all the time and as much as I'd like to say that this
would be a good reason to use a ContactGenie product, reality is that for
importing multi-line notes that are "properly" formatted, the Outlook import
wizard works perfectly (it's never failed on us yet). That includes a
multi-line CSV file with one or more embedded double-quote characters. For
what it's worth, also added all the characters you mentioned copying the
various characters from two different fonts (MS Serif & Arial) and
everything was imported with no problems.

Since you are using Outlook '2002 for which the PST standard is ANSI versus
Unicode (if memory serves me correctly), is it possible you're trying to
import Unicode data into a PST file that doesn't support Unciode?

Also FYI - a simplistic explanation of how Excel converts text fields to CSV
that contain comma's and double quotes is that an additional double quote is
inserted in front of every double-quote found - after that's completed the
entire field is bracketed by one double-quote at the beginning and end of
the particular field. Ergo, when a CSV file is then processed - the
double-quote pairs are treated as field content with each resulting pair
translated as a single double quote (providing the CSV translator deals with
everything in a conventional manner - which the MS CSV translator does in
all MS products I've worked with so far). The only caveat with the term of
CSV in terms of MS translators is that it uses the list separator character
which is not a comma in many other regions so "CSV" is not completely
accurate especially when sending those files from one country to another
using different regional settings (these files in reality then become
custom-delimited files versus comma-separated).

As for the leading zeros issue in Excel - that falls in the realm of Excel
worksheet formatting. If leading zeros are displayed within a given field in
an Excel worksheet then those zeros will be saved in a CSV file (which also
means that the column has likely been formatted as "text"). Anything else,
I'll leave to the Excel experts.

To get back to point - this started with a simple comment of saving an Excel
worksheet to CSV and importing the CSV file to Outlook IF you didn't want to
deal with "named ranges". On that note, still haven't heard anything other
then the potential issue of Ansi vs Unicode that should cause problems
importing a CSV file (saved by Excel) into Outlook. This is also the first
time I've heard of anyone in the newgroup having similar problem(s) after
following that suggestion.

Karl
___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com



Alphonse said:
Sorry about the lack of version info... oversight.
Win XP Pro SP2
Office 2002 SP3
Regional Settings:
Standards and formats - English US
Location - US
Languages - English, French and Spanish

About the double-quotes, I have noticed them within the CSV files, I have
always understood the concept, and for ever I have avoided using
dbl-quotes
in the Notes field to avoid OL a possible confusion, but I have not
understood why, nonetheless, all multi-line notes get imported incomplete.
If I bother to go and fix the file by replacing all line returns to
something like semi-colons, then all will turn out fine... I just took it
as
a glitch and adapted.

Humor me a bit and try this: export a couple of contacts with multi-line
notes as a CSV file, then open Excel, then open the CSV file. If you ask,
why not open it directly from Explorer, the answer is that any numbers
that
begin with 0, like some foreign countries' phone #s, zip codes, or
overseas
access codes, will all lose that initial 0.

In terms of special characters changing, you can test by using
Ctrl+SingleQuote then type a vowel to obtain an accented vowel, or
Ctrl+Comma, then type C for c with a little tail (cedilla) or Ctrl+Colon
(Shift+Semicolon), then a vowel for the two little dots over the vowel.
--
Cheers,
Alphonse


Interesting results you are encountering

When Excel saves a worksheet to a CSV file - it does so using double
quotes
as the field qualifiers which handles any fields that have embedded
comma's
(pretty much a universal method of dealing with that issue). It's never
been
an issue for us in the all the years we've been dealing with Outlook
importing/exporting nor has anyone else ever told us that they couldn't
import a file after saving an XLS file to CSV. Ergo, this is the first
time
have seen a problem report about how Excel saves a CSV file.

In terms of multi-line notes - Outlook does import those for the same
reason
mentioned above - the notes field is surrounded by double-quote qualifiers
(in fact just re-tested Outlook's import/export wizard with a file that
contains both the conditions above and all worked just as advertised).

In terms of characters being replaced, may have something to do with your
system's configuration but don't currently have a test file with those
conditions to test the Outlook import/export wizard so can't comment.

Having said all of that, you have also not provided any version
information
regarding the versions of Outlook or Excel you're using - or operating
system in use etc (my response above applies to all versions of
Excel/Outlook '2000 and above). Would also be interesting to know what
your
regional settings are.

Karl
___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com


The problem with converting the xls file into a csv file is that any
carriage returns in the Notes field creates a new record or entry, not
only
creating bogus contacts but also rendering the Notes incomplete. Also, for
those records which have names that contain special characters, meaning
any
letter with an accent mark, thus, Spanish, French and German to name but
three, such characters get often replaced by a ? or some other ANSI
character. As if this were not enough, converting into CSV causes anything
that has a comma, like addresses and notes, will shift all data beyond it
to
another field, pushing some past the last column. A better solution to the
commas problem is to convert to a Tab Separated Values, which actually
saves
it as a txt file, but even this does not save you from the first two
problems I stated.

Here is a better way:
To use a Microsoft Excel workbook to import information, cancel everything
in Microsoft Outlook, and then open up the file in Excel. To define a
named
range in Microsoft Excel, follow these steps:

1. Select the range of cells (including column headers) that you want to
import.
2. On the Insert menu, point to Name, and then click Define.
3. In the Names In Workbook box, type a name (the name may not contain
spaces nor should it be the same as one of the column header names) for
the
range that you specified, and then click Add.
4. Click OK, and on the File menu, click Save.
5. Save and quit the workbook.
(edited excerpt from http://support.microsoft.com/kb/196743/en-us)
-or-
1. Select the range of cells (including column headers) that you want to
import.
2. While that block is selected, type a name for the range in the box
above
the grid, to the left of the formula bar (it usually has a cell number in
it
and if you hover over the box it should say Name Box).
3. Save and close the spreadsheet and try to import into Outlook again.
(formatted answer given by Jocelyn Fiorello, MVP - Outlook
--
Cheers,
Alphonse


To avoid the "Named Range" requirement for the Excel file using the
Outlook
import wizard, just save the worksheet with the contact info as a CSV
file
and import the CSV file in Outlook instead.

Karl

___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com



Alphonse said:
Hi,
I have received and Excel file with Contact information; all the
headings,
Title, First Name, etc. are correctly placed at the top of each column,
but
when I try to import the xls file I receive a message stating "The MS
Excel
file [path\filename] has no named ranges. Use Excel to name the range of
data you want to import."
I don't understand what it means by "named ranges"... I have looked in
XL
but have not found anything that helps. Can someone please tell me how
to
do
this?
Click to expand...
Click to expand...
Click to expand...
 
Actually meant to add this to the previous response.................

Feel free to send us (to support AT contactgenie.com) a sample of the data
that you're having a problem with (please also reference that it is being
sent by request from this newsgroup should you decide to do so). Would be
interesting to see if we can replicate the issue(s) both with the Outlook
import wizard and our products and if we do - our programs get updated to
handle anything new found (if possible) since we have the capability to
monitor everything at a pretty discrete level. At a minimum, all we need is
an XLS file that contains a header row and one contact row that doesn't
import correctly for you in case you're concerned about confidential info
(please make sure that you try to import your data from the new XLS file or
corresponding saved CSV file created from that XLS file first just so that
any potential XLS file errors are excluded and we're working from the exact
same page).

Karl

___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com



Alphonse said:
Thanks for a very clear explanation. It all makes perfect sense and I do
believe everything that you are telling me, so I imagine that there is
something on my side that has caused this in my computer. I will retry all
this in my computer, and if part or all fails, I will try in another
computer with a clean install of Windows XP and a full, clean install of
Office 2002. Maybe some of this has to do with the import filters
selections
of the Office2k2 installation?

Well, I do appreciate your taking the time to tell me all this because I
will, one way or another--clean re-install my PC-- make it work for me and
avoid me all the work-arounds I have been taking... so this might take me
a
few days to sort out. Well, the tunnel has become a ditch, and now I just
have to find the path to climb out.

On a yet more positive side, if I am the first to have experienced these
things, let us hope I keep being the only one.
--
Cheers,
Alphonse


In terms of exporting/importing contacts with multi-line notes - not
something that is new to us, been dealing with data movement in and out of
Outlook for quite a while now and have had hundreds of data samples
submitted to us since the release of our first product more then 4 yrs
ago.
Moving multiline "notes" (body) data from any data source including CSV is
something that's done all the time and as much as I'd like to say that
this
would be a good reason to use a ContactGenie product, reality is that for
importing multi-line notes that are "properly" formatted, the Outlook
import
wizard works perfectly (it's never failed on us yet). That includes a
multi-line CSV file with one or more embedded double-quote characters. For
what it's worth, also added all the characters you mentioned copying the
various characters from two different fonts (MS Serif & Arial) and
everything was imported with no problems.

Since you are using Outlook '2002 for which the PST standard is ANSI
versus
Unicode (if memory serves me correctly), is it possible you're trying to
import Unicode data into a PST file that doesn't support Unciode?

Also FYI - a simplistic explanation of how Excel converts text fields to
CSV
that contain comma's and double quotes is that an additional double quote
is
inserted in front of every double-quote found - after that's completed the
entire field is bracketed by one double-quote at the beginning and end of
the particular field. Ergo, when a CSV file is then processed - the
double-quote pairs are treated as field content with each resulting pair
translated as a single double quote (providing the CSV translator deals
with
everything in a conventional manner - which the MS CSV translator does in
all MS products I've worked with so far). The only caveat with the term of
CSV in terms of MS translators is that it uses the list separator
character
which is not a comma in many other regions so "CSV" is not completely
accurate especially when sending those files from one country to another
using different regional settings (these files in reality then become
custom-delimited files versus comma-separated).

As for the leading zeros issue in Excel - that falls in the realm of Excel
worksheet formatting. If leading zeros are displayed within a given field
in
an Excel worksheet then those zeros will be saved in a CSV file (which
also
means that the column has likely been formatted as "text"). Anything
else,
I'll leave to the Excel experts.

To get back to point - this started with a simple comment of saving an
Excel
worksheet to CSV and importing the CSV file to Outlook IF you didn't want
to
deal with "named ranges". On that note, still haven't heard anything other
then the potential issue of Ansi vs Unicode that should cause problems
importing a CSV file (saved by Excel) into Outlook. This is also the first
time I've heard of anyone in the newgroup having similar problem(s) after
following that suggestion.

Karl
___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com



Alphonse said:
Sorry about the lack of version info... oversight.
Win XP Pro SP2
Office 2002 SP3
Regional Settings:
Standards and formats - English US
Location - US
Languages - English, French and Spanish

About the double-quotes, I have noticed them within the CSV files, I have
always understood the concept, and for ever I have avoided using
dbl-quotes
in the Notes field to avoid OL a possible confusion, but I have not
understood why, nonetheless, all multi-line notes get imported
incomplete.
If I bother to go and fix the file by replacing all line returns to
something like semi-colons, then all will turn out fine... I just took it
as
a glitch and adapted.

Humor me a bit and try this: export a couple of contacts with multi-line
notes as a CSV file, then open Excel, then open the CSV file. If you ask,
why not open it directly from Explorer, the answer is that any numbers
that
begin with 0, like some foreign countries' phone #s, zip codes, or
overseas
access codes, will all lose that initial 0.

In terms of special characters changing, you can test by using
Ctrl+SingleQuote then type a vowel to obtain an accented vowel, or
Ctrl+Comma, then type C for c with a little tail (cedilla) or Ctrl+Colon
(Shift+Semicolon), then a vowel for the two little dots over the vowel.
--
Cheers,
Alphonse


Interesting results you are encountering

When Excel saves a worksheet to a CSV file - it does so using double
quotes
as the field qualifiers which handles any fields that have embedded
comma's
(pretty much a universal method of dealing with that issue). It's never
been
an issue for us in the all the years we've been dealing with Outlook
importing/exporting nor has anyone else ever told us that they couldn't
import a file after saving an XLS file to CSV. Ergo, this is the first
time
have seen a problem report about how Excel saves a CSV file.

In terms of multi-line notes - Outlook does import those for the same
reason
mentioned above - the notes field is surrounded by double-quote
qualifiers
(in fact just re-tested Outlook's import/export wizard with a file that
contains both the conditions above and all worked just as advertised).

In terms of characters being replaced, may have something to do with your
system's configuration but don't currently have a test file with those
conditions to test the Outlook import/export wizard so can't comment.

Having said all of that, you have also not provided any version
information
regarding the versions of Outlook or Excel you're using - or operating
system in use etc (my response above applies to all versions of
Excel/Outlook '2000 and above). Would also be interesting to know what
your
regional settings are.

Karl
___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com


The problem with converting the xls file into a csv file is that any
carriage returns in the Notes field creates a new record or entry, not
only
creating bogus contacts but also rendering the Notes incomplete. Also,
for
those records which have names that contain special characters, meaning
any
letter with an accent mark, thus, Spanish, French and German to name but
three, such characters get often replaced by a ? or some other ANSI
character. As if this were not enough, converting into CSV causes
anything
that has a comma, like addresses and notes, will shift all data beyond it
to
another field, pushing some past the last column. A better solution to
the
commas problem is to convert to a Tab Separated Values, which actually
saves
it as a txt file, but even this does not save you from the first two
problems I stated.

Here is a better way:
To use a Microsoft Excel workbook to import information, cancel
everything
in Microsoft Outlook, and then open up the file in Excel. To define a
named
range in Microsoft Excel, follow these steps:

1. Select the range of cells (including column headers) that you want to
import.
2. On the Insert menu, point to Name, and then click Define.
3. In the Names In Workbook box, type a name (the name may not contain
spaces nor should it be the same as one of the column header names) for
the
range that you specified, and then click Add.
4. Click OK, and on the File menu, click Save.
5. Save and quit the workbook.
(edited excerpt from http://support.microsoft.com/kb/196743/en-us)
-or-
1. Select the range of cells (including column headers) that you want to
import.
2. While that block is selected, type a name for the range in the box
above
the grid, to the left of the formula bar (it usually has a cell number in
it
and if you hover over the box it should say Name Box).
3. Save and close the spreadsheet and try to import into Outlook again.
(formatted answer given by Jocelyn Fiorello, MVP - Outlook
--
Cheers,
Alphonse


To avoid the "Named Range" requirement for the Excel file using the
Outlook
import wizard, just save the worksheet with the contact info as a CSV
file
and import the CSV file in Outlook instead.

Karl

___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com



Hi,
I have received and Excel file with Contact information; all the
headings,
Title, First Name, etc. are correctly placed at the top of each column,
but
when I try to import the xls file I receive a message stating "The MS
Excel
file [path\filename] has no named ranges. Use Excel to name the range
of
data you want to import."
I don't understand what it means by "named ranges"... I have looked in
XL
but have not found anything that helps. Can someone please tell me how
to
do
this?
Click to expand...
Click to expand...
Click to expand...
 
I will work on it... basically I have to request the file again and
re-import to be able to select the data which causes pbs... I'm in Europe
right now, Friday eve... will send it ASAP next week. Have a good one.
--
Cheers,
Alphonse


Actually meant to add this to the previous response.................

Feel free to send us (to support AT contactgenie.com) a sample of the data
that you're having a problem with (please also reference that it is being
sent by request from this newsgroup should you decide to do so). Would be
interesting to see if we can replicate the issue(s) both with the Outlook
import wizard and our products and if we do - our programs get updated to
handle anything new found (if possible) since we have the capability to
monitor everything at a pretty discrete level. At a minimum, all we need is
an XLS file that contains a header row and one contact row that doesn't
import correctly for you in case you're concerned about confidential info
(please make sure that you try to import your data from the new XLS file or
corresponding saved CSV file created from that XLS file first just so that
any potential XLS file errors are excluded and we're working from the exact
same page).

Karl

___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com



Alphonse said:
Thanks for a very clear explanation. It all makes perfect sense and I do
believe everything that you are telling me, so I imagine that there is
something on my side that has caused this in my computer. I will retry all
this in my computer, and if part or all fails, I will try in another
computer with a clean install of Windows XP and a full, clean install of
Office 2002. Maybe some of this has to do with the import filters
selections
of the Office2k2 installation?

Well, I do appreciate your taking the time to tell me all this because I
will, one way or another--clean re-install my PC-- make it work for me and
avoid me all the work-arounds I have been taking... so this might take me
a
few days to sort out. Well, the tunnel has become a ditch, and now I just
have to find the path to climb out.

On a yet more positive side, if I am the first to have experienced these
things, let us hope I keep being the only one.
--
Cheers,
Alphonse


In terms of exporting/importing contacts with multi-line notes - not
something that is new to us, been dealing with data movement in and out of
Outlook for quite a while now and have had hundreds of data samples
submitted to us since the release of our first product more then 4 yrs
ago.
Moving multiline "notes" (body) data from any data source including CSV is
something that's done all the time and as much as I'd like to say that
this
would be a good reason to use a ContactGenie product, reality is that for
importing multi-line notes that are "properly" formatted, the Outlook
import
wizard works perfectly (it's never failed on us yet). That includes a
multi-line CSV file with one or more embedded double-quote characters. For
what it's worth, also added all the characters you mentioned copying the
various characters from two different fonts (MS Serif & Arial) and
everything was imported with no problems.

Since you are using Outlook '2002 for which the PST standard is ANSI
versus
Unicode (if memory serves me correctly), is it possible you're trying to
import Unicode data into a PST file that doesn't support Unciode?

Also FYI - a simplistic explanation of how Excel converts text fields to
CSV
that contain comma's and double quotes is that an additional double quote
is
inserted in front of every double-quote found - after that's completed the
entire field is bracketed by one double-quote at the beginning and end of
the particular field. Ergo, when a CSV file is then processed - the
double-quote pairs are treated as field content with each resulting pair
translated as a single double quote (providing the CSV translator deals
with
everything in a conventional manner - which the MS CSV translator does in
all MS products I've worked with so far). The only caveat with the term of
CSV in terms of MS translators is that it uses the list separator
character
which is not a comma in many other regions so "CSV" is not completely
accurate especially when sending those files from one country to another
using different regional settings (these files in reality then become
custom-delimited files versus comma-separated).

As for the leading zeros issue in Excel - that falls in the realm of Excel
worksheet formatting. If leading zeros are displayed within a given field
in
an Excel worksheet then those zeros will be saved in a CSV file (which
also
means that the column has likely been formatted as "text"). Anything
else,
I'll leave to the Excel experts.

To get back to point - this started with a simple comment of saving an
Excel
worksheet to CSV and importing the CSV file to Outlook IF you didn't want
to
deal with "named ranges". On that note, still haven't heard anything other
then the potential issue of Ansi vs Unicode that should cause problems
importing a CSV file (saved by Excel) into Outlook. This is also the first
time I've heard of anyone in the newgroup having similar problem(s) after
following that suggestion.

Karl
___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com



Alphonse said:
Sorry about the lack of version info... oversight.
Win XP Pro SP2
Office 2002 SP3
Regional Settings:
Standards and formats - English US
Location - US
Languages - English, French and Spanish

About the double-quotes, I have noticed them within the CSV files, I have
always understood the concept, and for ever I have avoided using
dbl-quotes
in the Notes field to avoid OL a possible confusion, but I have not
understood why, nonetheless, all multi-line notes get imported
incomplete.
If I bother to go and fix the file by replacing all line returns to
something like semi-colons, then all will turn out fine... I just took it
as
a glitch and adapted.

Humor me a bit and try this: export a couple of contacts with multi-line
notes as a CSV file, then open Excel, then open the CSV file. If you ask,
why not open it directly from Explorer, the answer is that any numbers
that
begin with 0, like some foreign countries' phone #s, zip codes, or
overseas
access codes, will all lose that initial 0.

In terms of special characters changing, you can test by using
Ctrl+SingleQuote then type a vowel to obtain an accented vowel, or
Ctrl+Comma, then type C for c with a little tail (cedilla) or Ctrl+Colon
(Shift+Semicolon), then a vowel for the two little dots over the vowel.
--
Cheers,
Alphonse


Interesting results you are encountering

When Excel saves a worksheet to a CSV file - it does so using double
quotes
as the field qualifiers which handles any fields that have embedded
comma's
(pretty much a universal method of dealing with that issue). It's never
been
an issue for us in the all the years we've been dealing with Outlook
importing/exporting nor has anyone else ever told us that they couldn't
import a file after saving an XLS file to CSV. Ergo, this is the first
time
have seen a problem report about how Excel saves a CSV file.

In terms of multi-line notes - Outlook does import those for the same
reason
mentioned above - the notes field is surrounded by double-quote
qualifiers
(in fact just re-tested Outlook's import/export wizard with a file that
contains both the conditions above and all worked just as advertised).

In terms of characters being replaced, may have something to do with your
system's configuration but don't currently have a test file with those
conditions to test the Outlook import/export wizard so can't comment.

Having said all of that, you have also not provided any version
information
regarding the versions of Outlook or Excel you're using - or operating
system in use etc (my response above applies to all versions of
Excel/Outlook '2000 and above). Would also be interesting to know what
your
regional settings are.

Karl
___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com


The problem with converting the xls file into a csv file is that any
carriage returns in the Notes field creates a new record or entry, not
only
creating bogus contacts but also rendering the Notes incomplete. Also,
for
those records which have names that contain special characters, meaning
any
letter with an accent mark, thus, Spanish, French and German to name but
three, such characters get often replaced by a ? or some other ANSI
character. As if this were not enough, converting into CSV causes
anything
that has a comma, like addresses and notes, will shift all data beyond it
to
another field, pushing some past the last column. A better solution to
the
commas problem is to convert to a Tab Separated Values, which actually
saves
it as a txt file, but even this does not save you from the first two
problems I stated.

Here is a better way:
To use a Microsoft Excel workbook to import information, cancel
everything
in Microsoft Outlook, and then open up the file in Excel. To define a
named
range in Microsoft Excel, follow these steps:

1. Select the range of cells (including column headers) that you want to
import.
2. On the Insert menu, point to Name, and then click Define.
3. In the Names In Workbook box, type a name (the name may not contain
spaces nor should it be the same as one of the column header names) for
the
range that you specified, and then click Add.
4. Click OK, and on the File menu, click Save.
5. Save and quit the workbook.
(edited excerpt from http://support.microsoft.com/kb/196743/en-us)
-or-
1. Select the range of cells (including column headers) that you want to
import.
2. While that block is selected, type a name for the range in the box
above
the grid, to the left of the formula bar (it usually has a cell number in
it
and if you hover over the box it should say Name Box).
3. Save and close the spreadsheet and try to import into Outlook again.
(formatted answer given by Jocelyn Fiorello, MVP - Outlook
--
Cheers,
Alphonse


To avoid the "Named Range" requirement for the Excel file using the
Outlook
import wizard, just save the worksheet with the contact info as a CSV
file
and import the CSV file in Outlook instead.

Karl

___________________________________________________
Karl Timmermans - The Claxton Group
ContactGenie - Importer 1.3 / DataPorter 2.0 / Exporter
"Power contact importers/exporters for MS Outlook '2000/2007"
http://www.contactgenie.com



Hi,
I have received and Excel file with Contact information; all the
headings,
Title, First Name, etc. are correctly placed at the top of each column,
but
when I try to import the xls file I receive a message stating "The MS
Excel
file [path\filename] has no named ranges. Use Excel to name the range
of
data you want to import."
I don't understand what it means by "named ranges"... I have looked in
XL
but have not found anything that helps. Can someone please tell me how
to
do
this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top