none
How to ignore the first row in a csv import RRS feed

  • Question

  • Hello

    This is the file we want to  add to the datamodel:
    https://www.mediafire.com/?a9xrwrwu79whu18
    (BTW this is the csv output from a neat & free software called Jstock)

    Here is an image of the problem:


    As you can see they add automatically a timestamp in the A1 cell ruining the possibility of a clean import.

    Can anyone help with a query to fix this and get the real headers?
    Friday, November 14, 2014 3:38 AM

Answers

  • hi NicoPer,

    I find out your jstock.csv have problem. You can fixed it by open it with excel and press Ctrl + S to save your file.

    the warning windows will appear to chose yes to save. Closing your csv file and using Power query to load into excel.

    By the way, i also attached the finish file for your reference.

    https://onedrive.live.com/edit.aspx?cid=94B99C319E63236A&resid=94B99C319E63236A%21316&app=Excel

    Regards,

    • Marked as answer by NicoPer Tuesday, November 18, 2014 6:46 PM
    Tuesday, November 18, 2014 5:35 PM

All replies

  • hi NicoPer,

    You can use Table.Skip function as below :

    Table.Skip( YourSource , 1)

    Regards,

    Friday, November 14, 2014 10:52 AM
  • Hi CDzung

    Thank you very much for your reply.

    I couldn´t make it work yet (I´m new to this).
    When I import the csv I automatically get this query:

    let
        Source = Csv.Document(File.Contents("C:\PATH\JSTOCK.csv"),null,null,null,1252),
        #"First Row as Header" = Table.PromoteHeaders(Source),
        #"Changed Type" = Table.TransformColumnTypes(#"First Row as Header",{{"timestamp=1415926728041", type text}})
    in
        #"Changed Type"


    I then delete the last 2 (auto) steps, and get:


    let
        Source = Csv.Document(File.Contents("C:\PATH\JSTOCK.csv"),null,null,null,1252)
    in
        Source


    Can you tell me how to add the mentioned line? I´m probably missing something.
    Sunday, November 16, 2014 5:18 PM
  • Hi NicoPer,

    To follow your M code. You have  this lines  #"First Row as Header" = Table.PromoteHeaders(Source). This mean you use first line ( the line include "timestamp=1415926728041" ) to make table header. So you no need to use Table.Skip function. You just use one more Table.PromoteHeaders to make second line to your table header. Below is your code:

    let
        Source = Csv.Document(File.Contents("C:\PATH\JSTOCK.csv"),null,null,null,1252),
        #"First Row as Header" = Table.PromoteHeaders(Source),
        #"Changed Type" = Table.TransformColumnTypes(#"First Row as Header",{{"timestamp=1415926728041", type text}}),
        FirstRowAsHeader = Table.PromoteHeaders(#"Changed Type")
    in
        FirstRowAsHeader

    Regards,

    Monday, November 17, 2014 1:39 AM
  • I copied the code exactly and have this error:

    ***********
    DataFormat.Error: There were more columns in the result than expected.
    Details:
        Count=1
    ***********

    It seems to be counting only the first column.

    And the timestamp is never the same so I think we´ll need a wildcard or something, right?

    Thanks again very much for your support.

    Monday, November 17, 2014 5:49 PM
  • Can you share the file ?
    Tuesday, November 18, 2014 2:27 AM
  • I did on the first post. Here:

    jstock

    Tuesday, November 18, 2014 3:06 PM
  • hi NicoPer,

    I find out your jstock.csv have problem. You can fixed it by open it with excel and press Ctrl + S to save your file.

    the warning windows will appear to chose yes to save. Closing your csv file and using Power query to load into excel.

    By the way, i also attached the finish file for your reference.

    https://onedrive.live.com/edit.aspx?cid=94B99C319E63236A&resid=94B99C319E63236A%21316&app=Excel

    Regards,

    • Marked as answer by NicoPer Tuesday, November 18, 2014 6:46 PM
    Tuesday, November 18, 2014 5:35 PM
  • Hi CDzung

    I normally use csv files, but Jstock also have the possibility to save the data as xls.

    So when you said that there is a problem in the csv file, I tried the xls and it worked at once.

    It doesn´t even read the timestamp cell.. and that´s good.

    Thanks a million. I really appreciate your responses.

    Tuesday, November 18, 2014 6:45 PM
  • let
        Source = Csv.Document(File.Contents("C:\PATH\JSTOCK.csv"),null,null,null,1252)
        #"Removed Top Rows" = Table.Skip(Source,2)
    in
        #"Removed Top Rows"
    Wednesday, August 30, 2017 8:59 PM