Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- Problem with formats in "Import-Wizard"

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-26-2008 09:22 AM

Hi. There's a little problem i can't resolve.

I want import data from an excel-sheet. In one of the columns are both integers and decimals in european notation (like 35.012 or 40.256,35). I can't use a format like eurox12.2 cause i get wrong values for integers (35.012/10² in this example).

In a manually written import procedure i can use the eurox. format without a value for w. or d. and it works, but the import-wizard requires values for w. and d. (min. eurox1.0).

Is there any way to use eurox. format with import-wizard?

I want import data from an excel-sheet. In one of the columns are both integers and decimals in european notation (like 35.012 or 40.256,35). I can't use a format like eurox12.2 cause i get wrong values for integers (35.012/10² in this example).

In a manually written import procedure i can use the eurox. format without a value for w. or d. and it works, but the import-wizard requires values for w. and d. (min. eurox1.0).

Is there any way to use eurox. format with import-wizard?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

03-13-2008 09:18 AM

Greetings!

I have attempted to reproduce the problem you describe, but have not experienced the difficulty you seem to have had.

Let me start by describing how I created my test data in Excel so as to confirm that I have data similar to yours.

Since I am was working in an English version of Excel I first went to**Tools** --> **Options...** and on the **International** tab changed the **Decimal separator** character to a comma and the **Thousands separator** to a period.

Then I created a new worksheet with field headings in cells A1 to C1. These were**Integer**, **Fixed2** and **Fixed4**.

Next in cell A2 I placed the value**1** and in cell A3 I placed **2.345**. I also formatted these cells as **Euro** with 0 decimal places.

Cell B2 had the value**1,23** and B3 had **2.345,67**. I formatted both of these as **Euro** with 2 decimal places.

Finally, cell C2 had the value**1,2345** and C3 had **2.345,6789**. The format on these two cells was **Euro** with 4 decimal places.

I then saved the Excel workbook and started Enterprise Guide 4.1.

I created a new Enterprise Guide project and then started the Import Data task by selecting**File** --> **Import Data...** and in the Open dialog selecting my test Excel workbook and Sheet1$ (I had kept Excel's default name for the worksheet).

I didn't change anything on the**Region to Import** or **Results** panels of the task, but on the **Column Options** panel I made these changes:

For the field**Integer** I changed both the **Display format** and **Read-in format** to the currency format **EURO12.0**.

For the field**Fixed2** I changed the **Display format** to **EURO12.2** and the **Read-in format** to **EURO12.0**.

And finally for the field**Fixed4** I changed the **Display format** to **EURO12.4** and the **Read-in format** to **EURO12.0**.

When I ran the task a data set was created with the values E1 and E2.345 for the field Integer, E1,23 and E2.345,67 for the field Fixed2 and E1,2345 and E2.345,6789 for the field Fixed4.

A note about the**read-in formats** I specified: By specifying the EURO format with a value of 0 for the decimals (which is the default value anyway) we are telling SAS that we want the number that's read in to be unscaled. If we were to specify a value for the decimals on the informat, then we would be instructing SAS to scale the number read in by the EURO informat by dividing it by 10**d. So a decimal value of 3 would result in the numeric value being divided by 1000. The EURO informat doesn't need to be told how many decimal places there actually are in the incoming value because it knows how to read a European formatted number and can therefore determine the position of the decimal separator for itself.

It may well be that I've misunderstood something about the structure of the data in your Excel file, in which case please let me know and I can take another look at the problem.

Regards,

David.

I have attempted to reproduce the problem you describe, but have not experienced the difficulty you seem to have had.

Let me start by describing how I created my test data in Excel so as to confirm that I have data similar to yours.

Since I am was working in an English version of Excel I first went to

Then I created a new worksheet with field headings in cells A1 to C1. These were

Next in cell A2 I placed the value

Cell B2 had the value

Finally, cell C2 had the value

I then saved the Excel workbook and started Enterprise Guide 4.1.

I created a new Enterprise Guide project and then started the Import Data task by selecting

I didn't change anything on the

For the field

For the field

And finally for the field

When I ran the task a data set was created with the values E1 and E2.345 for the field Integer, E1,23 and E2.345,67 for the field Fixed2 and E1,2345 and E2.345,6789 for the field Fixed4.

A note about the

It may well be that I've misunderstood something about the structure of the data in your Excel file, in which case please let me know and I can take another look at the problem.

Regards,

David.