Power Pivot, decimal separator error

When working with Decimal number data in PowerPivot, you might see different general error during the import of new file or when import it the decimal number is without comma or point.

These issues can occur when the data source in your workbook is a comma separated values (CSV) file that was created on an operating system (OS) with an English locale and being accessed on an OS with a non-English locale. The Microsoft Jet Database Engine is unable to directly handle certain values in the data.

It cannot handle certain values when the format in the Regional and Language settings of the OS is set to use a character other than a period (.) as a decimal separator. For example, your OS settings may be set to use a comma (,) for a decimal separator.

To resolve this issue, do one of the following tasks:

Replace the decimal separator:  Teplace the decimal separator in the CSV file with another character, such as a semicolon (;). To do this, use the find and replace feature in the text editor you use to open the CSV file, find all characters that are used as a decimal separator, such as a period, and replace with a semicolon. It’s works oly if you csv si very small and simple to edit.

Create a schema.ini file: You can create a standard schema.ini file that will tell Jet how to read the CSV file, regardless of the regional settings, it’s work with all csv type of file:

  • First create e normal txt file and convert it to .ini ( rename the extension)
  • Call the file Schema.ini and save the schema.ini file in the same directory as the CSV file.
  • Include a line that defines the current decimal separator as used in the CSV Using the example above, the first two entries in your schema.ini file will look like the following:

[filename.csv]
DecimalSymbol=.

the first row is the nam of you csv, the second one set the character for your decimal symbol in your csv. You can also set the column name, his type and leght using these string:

Col1=CustomerNumber Text Width 10
Col2=CustomerName Text Width 30

Col is the position of colum( from left to right in you csv.

For more information about the schema.ini file, go to the MSDN website: http://msdn.microsoft.com/en-us/library/ms709353%28VS.85%29.aspx.

 

 

 

Annunci

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...