locked
Invalid import of data RRS feed

  • Question

  • I have a column consisting of text strings; if some of these strings consist of only digits, PowerPivots treats the whole column as number column, and skips all rows that can't be converted to a number.

    Changing column type to "Text" doesn't help.

    Ideas?

    Wednesday, January 4, 2012 8:24 AM

Answers

  • It seems this problem is fixed, when I append a leading space to every "all-number" field
    • Marked as answer by user4756 Monday, January 16, 2012 6:47 AM
    Monday, January 16, 2012 6:47 AM

All replies

  • Hi

    What kind of data source are you using? SQL Server 2008 R2?


    Eddy Nijs
    Wednesday, January 4, 2012 9:49 PM
  • Tab-delimited text
    Thursday, January 5, 2012 6:58 AM
  • Hi,

    The only way i've made it work for this type of scenario is to quote the text field

    HTH

    Paul

    www.paultebraak.wordpress.com

    • Proposed as answer by Challen Fu Friday, January 6, 2012 11:34 AM
    • Marked as answer by user4756 Thursday, January 12, 2012 9:55 AM
    • Unmarked as answer by user4756 Monday, January 16, 2012 6:46 AM
    Thursday, January 5, 2012 8:36 PM
  • It seems this problem is fixed, when I append a leading space to every "all-number" field
    • Marked as answer by user4756 Monday, January 16, 2012 6:47 AM
    Monday, January 16, 2012 6:47 AM
  • I had this problem and found that on some text files, the field in question was being recognized as text. What I discovered was that if the first 20 to 30 records had alphanumeric data for this field, (such as A240 as opposed to 0240), then the field was being characterized as text, otherwise if there were purely numeric data, it was characterized as numeric. So my solution was to enclose the numbers in quotes for the 1st 30 records....

    Would be great if Microsoft could give more direct control on this.... (On a dataset that I have no edit access to, I would not be able to do this...)

     
    Friday, November 29, 2013 1:51 PM
  • Actually,

    You can have complete control over it if you define the fine in a schema.ini file - its a file that the driver will use instead of its interpretation of the data.  You need this file only once (when you initially import the data) and refreshes will work fine.

    I agree though, it could be much better - especially if the importer had a configuration option.

    HTH,

    Paul

    www.paultebraak.wordpress.com

    Friday, November 29, 2013 9:21 PM