none
CSV with semicolon as delimiter

    Question

  • Hi,

    I recently used Power Query to import data from a CSV file that was generated by an other tool which I do not have influence on how the CSV is generated
    in this case semicolon is used to separated the single fields/columns

    when Power Query opens the CSV files it tries to use the CSV importer which fails and gives and error for each row because the delimiter is actually semicolon (and not comma). Further the data itself also contains comma within the texts so this may be an other issue

    fact is, that after Power Query has created the source-step as CSV you have to manually change it to text and do the split by semicolon on my own

    even if I select "From File --> From Text" Power Query is super smart here, realizes it is a CSV file (according to the file-extension I guess) and I run into the same issue again

    the funny thing is that the same CSV file can be imported using plain old Excel "Data --> From Text"
    for this import I have a wizard to specify the delimiter (or Fixed Width), "My data has headers" and it even reads the "file origin" correctly!
    why can't Power Query do all this out of the box when it was already possible like 10 years ago???

    any feedback on this is highly appreciated!

    kind regards,
    gerhard


    - www.pmOne.com -

    Wednesday, September 04, 2013 6:03 PM

Answers

  • Hi Gerhard,

    Thanks for your feedback. The "From Text/CSV" import experience is one area of improvement for Power Query. This is already in our radar and we are aware of the issues that you highlighted.

    The current workaround is, as you discovered alread, to use Edit Settings on your source step and specify to open the file as Text. Then, apply transformations to the generated single-column table to split into multiple columns, exclude rows based on value or position, etc.

    Regards,
    M.

    Saturday, September 07, 2013 8:35 PM
    Owner

All replies

  • Hi Gerhard,

    Thanks for your feedback. The "From Text/CSV" import experience is one area of improvement for Power Query. This is already in our radar and we are aware of the issues that you highlighted.

    The current workaround is, as you discovered alread, to use Edit Settings on your source step and specify to open the file as Text. Then, apply transformations to the generated single-column table to split into multiple columns, exclude rows based on value or position, etc.

    Regards,
    M.

    Saturday, September 07, 2013 8:35 PM
    Owner
  • When can we expect this improvements to be public?

    Is there any timeline?


    - www.pmOne.com -

    Monday, September 09, 2013 12:11 PM
  • any further input on this??

    - www.pmOne.com -

    Monday, September 16, 2013 12:46 PM
  • Hi Gerhard,

    At this point, we can't provide any specific timeline for when this improvement will be available.

    Thanks,
    M.

    Wednesday, September 18, 2013 1:55 AM
    Owner