Forum Discussion

DenHaa's avatar
DenHaa
Frequent Visitor
4 years ago

Issue when importing CSV file

Hello.   I am having an issue when I am importing a CSV-file through the Web query as it limits the amount of columns for some reason. The first time I imported the CSV it had 16 columns, I have s...
Need Help
DenHaa's avatar
DenHaa
Frequent Visitor
4 years ago

Thanks negi007 & themistoklis for your replies 🙂

 

I have tried this and then it will only load the first 16 columns.

My query now looks like this:

let
    Source = Csv.Document(Web.Contents("$url"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    #"Promoted Headers"

 

But the results is only 16 columns:

 

If I then manually enter 26 columns, it will fill them with data in the visual window but still class them as empty?

negi007's avatar
negi007
Community Champion
4 years ago

DenHaa your code is slighlty different. i have just imported the file and was able to get new columns using below code. Can you try using it.

 

let
Source = Csv.Document(Web.Contents("https://abc/xyz.csv"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
#"Promoted Headers"

 

 

  • DenHaa's avatar
    DenHaa
    Frequent Visitor
    4 years ago

    The only change I could see was the Encoding, correct?

     

    When I changed the Encoding to 1252, all of the 26 columns show up when I refresh but they still give an error:

     

    And if I try to change the type of one of the fields, I get the following error:

     

    Here is my current query:

    let
    Source = Csv.Document(Web.Contents("$url"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Assigned Date", type datetime}})
in
    #"Changed Type"
    • negi007's avatar
      negi007
      Community Champion
      4 years ago

      DenHaa try to remove the changed type step. in this step some column names are passed for processing. 

      • DenHaa's avatar
        DenHaa
        Frequent Visitor
        4 years ago

        I have tried removing that as well so my query is simply:

        let
    Source = Csv.Document(Web.Contents("$url"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    #"Promoted Headers"

        When I use this, the following error is shown:

         

        As I mentioned in my original post, it works if I do the same process in Excel and the query looks like this:

        let
    Source = Csv.Document(Web.Contents("@url"),[Delimiter=",", Columns=26, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
in
    #"Promoted Headers"