none
Import data from text file

    Question

  • Hi

    I have a text file that contains dates in a "mm/dd/yyyy" format

    My operating system is set to "dd/mm/yyyy" format

    when I import the text file to my powerpivot (2010) all dates that are not recognized by the powerpivot as dates, are imported as blanks (for example december 31 is shown as 12/31/2014, instead of 31/12/2014 that is recognized by the system).

    Is there a way to solve this problem (similar to the wizards in excels that allow you to change the format when impoting)

    Thanks

    Thursday, February 20, 2014 7:05 AM

Answers

  • I found a solutions:

    since I have excel 2010 on Windows XP (can not install powerquery)

    and I have a text file that is over 1.5M records (can not import to excel)

    and the dates in the text file are in MDY format while my system is in DMY formatt

    I imported the text file to access, defined the source as MDY and thats it (:

    Friday, February 21, 2014 3:25 AM

All replies

  • import it as text type. then parse out year, month and day parts and use DAX function DATE() to create datevalue from it onto a new calculated column. You can hide the original column from client tools and use this new calculate column instead which will have the valid date values

    http://visakhm.blogspot.in/2012/07/dax-date-function-demystified.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, February 20, 2014 7:23 AM
  • I Think I'm missing something:

    I use the "import from text" option in the powerpivot window

    there is no place I'm asked to determine the type of the data

    I can only select columns which I do not want to import

    It is not working like the excel's import wizard, which I can not use, since the text file contains over 2M records

    Thanks

    Thursday, February 20, 2014 10:04 AM
  • you may use Power Query in order to load data directly into Power Pivot
    this would also allow you to do the conversion within Power Query and import the field as native date-column

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Thursday, February 20, 2014 2:29 PM
  • Thanks, but my OS is XP, and I can not install power query...
    Thursday, February 20, 2014 4:08 PM
  • You can import the file into Excel sheet (not PowerPivot), then convert date to your system date format i.e. dd/mm/yyyy. You can use Excel function DATE() to create the date value from the text Date. On PowerPivot ribbon bar, click on "Create Linked Table" to add it to your PowerPivot data model.

    HTH!

    Ayad



    Thursday, February 20, 2014 8:26 PM
  • I found a solutions:

    since I have excel 2010 on Windows XP (can not install powerquery)

    and I have a text file that is over 1.5M records (can not import to excel)

    and the dates in the text file are in MDY format while my system is in DMY formatt

    I imported the text file to access, defined the source as MDY and thats it (:

    Friday, February 21, 2014 3:25 AM