How to update csv external data and get new columns

[arbazhamayun]

Hi guys,

I have an external data source from a csv file. When I imported the csv file and the table in Excel was created there was 3 columns in the table. Now my csv file has changed and a two new columns have emerged. However when I refresh my table data the new columns are not added? Is there a way to get these new columns in my table from refreshing the data?

[CK76]

Try going into connection property.

Check in Definition tab and update Command text.

By default, it will list each columns selected. Change it to something like...

Please Login or Register  to view this content.

[arbazhamayun]

CK76 thanks for your response. This is what I see in the definition tab:

picture dd.JPG

It looks the same as what you wrote?

[CK76]

Hmm, are you using Get & Transform to bring in data? If so, you'd need to take a look in the Query editor.

If not, I'd recommend using Microsoft Access Text Driver (*.txt, *.csv) as driver, instead of OLEDB.

[arbazhamayun]

Yes originally it was the Get & Transform I used. But for updates of the file I use the Queries and Connections "Refresh All".. What should I do in the Query Editor?

[CK76]

By default, Get & Transform should bring in any update column as is. However, there are cases where new columns may be dropped.

Once in the query editor, check the right side panel for each applied steps. If you notice in any of those steps, if columns are dropped, you'll need to modify that step to include newly added columns.

If having trouble, go to "Advanced Editor" view and copy M code stored there and post. I can take a look in the code and see if anything is dropping column(s).

[arbazhamayun]

This is from the Advanced Editor:

let
Source = Csv.Document(File.Contents("test_ARK.csv"),[Delimiter=",", Columns=17, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Source File", type text}, {"Category", type text}, {"Family", type text}, {"Type", type text}, {"Component", type text}, {"F Rating", type text}, {"A Rating", type text}, {"Value", Int64.Type}, {"Id", type text}, {"Height", type text}, {"Width", type text}, {"AFDC", Int64.Type}, {"Envelope", type text}, {"Exit", type text}, {"Finish", type text}, {"Material", type text}, {"Glazing", type text}})
in
#"Changed Type"

What I can see from this is that
- Columns = 17, this seems to be a constant value and this is NOT the case. It should change according to the columns in the CSV file

[CK76]

Ok, so just remove the hard coded Column value

Please Login or Register  to view this content.

[arbazhamayun]

Thanks! That did the trick