Forum Discussion
Csv.Document function in Power Query
Hi,
Could someone shed some light on what the Delimiter, Column , Encoding, QuoteStyle are for?
Are they compulsory fields? Are they automatcally generated the first time the file is imported? If so, do they ge updated automatically once a new file is used for example?
Im just gonna post below the one I was looking at as a reference.
= Csv.Document(File.Contents("F:\6) Financial Accounting\Team member folders\Gianmarco\Power Query\Zeno\Zeno - Report AG Accrual\Zeno_Report_AG_Accruals.csv"),[Delimiter=",", Columns=75, Encoding=1252, QuoteStyle=QuoteStyle.None])
Thanks!
Hi GGG123 ,
Per your questions:
-1- Delimiter: Although CSV stands for "Comma Separated Values", the character that actually separates the values can be changed, so this argument tells PQ which character splits values into new columns. This is particularly useful, for example, where a country's numerical region format uses commas instead of decimal points. Using comma as the delimiter will split whole parts of numbers from their decimal parts, so it can be changed to account for this, as well as any other scenario-specific needs. This is a not a mandatory argument, default is ",".
-2- Columns: This tells PQ how many columns you want to bring in from the CSV, starting from the left-most column. PQ defaults to the maximum number of columns found in the ORIGINAL CSV import. Thus, if you later add a column to your CSV source file, PQ will ignore the new column on the next import as it's hardcoded for the original columns only. This is not a mandatory argument, so 'Columns = 75' can just be deleted and PQ will pick up every column every time.
-3- Encoding: This is the 'language' that the CSV gets saved into, and determines which parts (characters) of the CSV can be stored in binary. PQ detects the encoding type from your original CSV import and generally does a pretty good job of getting it right. This is not a mandatory argument (default is 65001 (UTF-8), and can be updated if you change the encoding of your source file.
FYI: UTF-8 encoding is usually recommended as it contains the most characters that can be stored (1,112,064) and includes everything from regular alphabet characters right through to emojis - basically, if you can type it, UTF-8 can store and recover it.
-4- QuoteStyle: This is how quoted line breaks are handled, i.e. whther they are treated as part of the data or as the end of the current row. This is not a mandatory argument, default is QuoteStyle.Csv, which treats quoted line breaks as part of the data.
-5- No, these do not get updated when you use a new file, they are hardcoded. However, as per the above, you can overwrite/remove these arguments in order to match the new files requirements.
Pete
2 Replies
Hi GGG123,
Specification of the function argument values takes place when you set up the connection to the CSV. This is not dynamic or updated the next time you read the file but remains static.
See the documentation here
And this article: An In-Depth Look At The Csv.Document M Function by Chris Webb
I hope this is helpful
Hi GGG123 ,
Per your questions:
-1- Delimiter: Although CSV stands for "Comma Separated Values", the character that actually separates the values can be changed, so this argument tells PQ which character splits values into new columns. This is particularly useful, for example, where a country's numerical region format uses commas instead of decimal points. Using comma as the delimiter will split whole parts of numbers from their decimal parts, so it can be changed to account for this, as well as any other scenario-specific needs. This is a not a mandatory argument, default is ",".
-2- Columns: This tells PQ how many columns you want to bring in from the CSV, starting from the left-most column. PQ defaults to the maximum number of columns found in the ORIGINAL CSV import. Thus, if you later add a column to your CSV source file, PQ will ignore the new column on the next import as it's hardcoded for the original columns only. This is not a mandatory argument, so 'Columns = 75' can just be deleted and PQ will pick up every column every time.
-3- Encoding: This is the 'language' that the CSV gets saved into, and determines which parts (characters) of the CSV can be stored in binary. PQ detects the encoding type from your original CSV import and generally does a pretty good job of getting it right. This is not a mandatory argument (default is 65001 (UTF-8), and can be updated if you change the encoding of your source file.
FYI: UTF-8 encoding is usually recommended as it contains the most characters that can be stored (1,112,064) and includes everything from regular alphabet characters right through to emojis - basically, if you can type it, UTF-8 can store and recover it.
-4- QuoteStyle: This is how quoted line breaks are handled, i.e. whther they are treated as part of the data or as the end of the current row. This is not a mandatory argument, default is QuoteStyle.Csv, which treats quoted line breaks as part of the data.
-5- No, these do not get updated when you use a new file, they are hardcoded. However, as per the above, you can overwrite/remove these arguments in order to match the new files requirements.
Pete
