But our second row has three commas, which means three columns… That’s not the same number of columns as our header row, so Power Query freaks out and throws an error for every subsequent record.Power Query sets us up for a one column table. We therefore must have a one column table. Our first row has no commas before the line feed.(That’s why you’ll see records in some CSV’s that read, – because there isn’t anything for that record, but we still need the same number of commas to denote the columns.Īnd now some joker builds us a file masquerading as a CSV that really isn’t. And we also know that every CSV file has a consistent number of columns (and therefore commas) on every row. Every comma indicates a column break, every carriage return a new line. If I click the white space beside one of those errors, I get this:
Let’s try importing the sample file into Power Query: Well yay, so what, right? Who cares about the guts of a CSV file? The answer is “you” if you ever get one that is built like this… Yet when you look at the data in the rows below, they are plainly separated by commas. Notice the first row… that’s where our issue is. If you open the sample file in Notepad, you’ll see that it contains the following rows:
#Excel text import wizard rows download
If you’d like to follow along, you can click here to download MalformedCSV.csv (the sample file). It is NOT A GOOD IDEA.) The Issue in the Real World Awesome… until some bright spark decides to inject a line or two of information above the CSV contents which doesn’t contain any commas. CSV files are a prime example of this, and we should be able to assume that any CSV file will contain a list of Comma Separated Values, one record per line, followed by a new line character. IT standards are generally a beautiful thing, especially in programming, as you can rely on them, knowing that certain rules will always be followed. The problem is that sometimes this doesn’t work as expected, and you need to be able to force Power Query to import as a text file, not the file format that Power Query assumes you have. Power Query takes certain liberties when importing a file, assuming it knows what type of file it is. I’ve run into this issue in the past, and also got an email about this issue this past week as well, so I figured it’s worth taking a look.
0 kommentar(er)
