From Folder Connector Converting Some data to Null

[kylon_19]

Hi All, 

I am trying to join a series of CSVs that all follow the same structure and have the same headings into a data model using power query (get data > folder method).

It is pulling data from about 40 CSVs. The CSVs contain a number of binary demographic columns about the survey respondents (nothing personally identifiable but more like "My primary focus a work is typing" - as an example). It appears that 5 of those binary columns are being pulled into the data model as NULL. It appears to be happening on a CSV-by-CSV basis and it's all or nothing for the issue. So either all the data in the 5 columns for a CSV are coming in as NULL or they are coming in normal. 

Additionally, within an affect CSV other binary columns are not encountering the same issue. 

I have confirmed that the headers for the columns in the affected CSVs match the sample file. I have played around with the changing the data type conversion. 

I can't think of anything that might be causing the issue and don't know how to diagnose deeper. Has anyone else encountered similar problems when using the folder connector?

[edhans · Accepted solution]

How can a CSV file have binary data? It should be pure ASCII.

You also want to make sure the file formats are actually the same. ASCII and ANSI are different, and some may have a CR/LF at the end and others a CR. 

When pulling in a CSV file you can change the type it is.

I'd pull a few of those in manually and see what the import preview shows. It may help in debugging then combining the files from a folder.

[kylon_19]

Hi edhans,

Apologies for the confusion, I meant that the values in the problem columns are either a 0 or a 1 (I'll be sure to be more clear on this in the future).

Your recommendation for debugging worked. It turned out the sample file was in a weird format compared to the rest of the files and was causing the system to not recognize the values. 

Thanks!

[edhans]

Great! Glad you found the issue.