cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
duncanwil Frequent Visitor
Frequent Visitor

csv data type

Dear All,

 

I downloaded a CSV file from a finance web site. I have downloaded their CSV files before and use them in Power Query, Excel and so on so I know they are usually fine.

 

The first row the file contains the headers I want so there is no problem there.

 

The table in the file contains a column of account names and then the next 11 columns contain annual values for each account: really simple accounting report.

 

I noticed that a Column chart was showing the bars as COUNT so I went to Data View and tried to change the Data Type for the 11 columns from Text to Whole Number ... it says We can't automatically convert the column to Whole Number type.

 

Any idea why and what I can do as a work around? I know I can convert the file to Excel and so on but it's a step I don't want to have to take as you will appreciate.

 

Many thanks

 

Duncan

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
adamflath Member
Member

Re: csv data type

I have see this happen sometimes when you try to convert it to a whole number and it wont let you.  For me, it was because some of the fields really were NOT NUMBERS.  Double check your data to make sure that:

1. There really are only numbers in the column

2. If your header repeats itself in the file, make sure you remove that header by filtering it out.

8 REPLIES 8
pqian Senior Member
Senior Member

Re: csv data type

Can you try changing the types in the queries editor window? There should already be a change type step, which maybe incorrectly changing the types to text.
fbrossard Member
Member

Re: csv data type

Hi @duncanwil

 

  1. Just modify your query,
  2. select your column and verify the data type, in your case it should be "Text" or Any "Type"
  3. change the type to "Whole Number" => it adds a new tranformation step on the right panel
  4. Close and apply your changes

And then it should works.

 

 

Highlighted
adamflath Member
Member

Re: csv data type

I have see this happen sometimes when you try to convert it to a whole number and it wont let you.  For me, it was because some of the fields really were NOT NUMBERS.  Double check your data to make sure that:

1. There really are only numbers in the column

2. If your header repeats itself in the file, make sure you remove that header by filtering it out.

B129 Member
Member

Re: csv data type

Hi @duncanwil,

 

   this COUNT column contains only numbers? If the answer is yes, did you try to auto-detect the data type?

You can do it like this:

1) Go to "Edit Queries"

2) Go to "Transform"

3) In the "Any column" section select "Detect Data Type"

If the COUNT column data type turns into Any, it means that your column contains different type of values.

 

Let me know.

 

#I'M Not An Expert#

duncanwil Frequent Visitor
Frequent Visitor

Re: csv data type

Thanks Adam,

 

It's a while since I posted my request but over the last few days it's happened again. I went through the processes that I always go through but got the problem. I checked here and found that we are all thinking the same things. Still it happened.

 

So I left the problem alone for 24 hours as I thought about it. 

 

In the end, I went back to the query editor and tried one more time and it all came right. I cannot say that I did anything different this final time. Of course, I don't know whether this will happen again either.

 

Thanks for your suggestions anyway.

 

Duncan

duncanwil Frequent Visitor
Frequent Visitor

Re: csv data type

Thank you for your help.

 

I do what you say and normally it works but just now and again it flares up. It's probably me but I can't think how and why!

 

Thanks for your suggestions anyway.

 

Duncan

duncanwil Frequent Visitor
Frequent Visitor

Re: csv data type

Thank you for your help. It's probably me but I can't think how and why! All is well at the moment!

 

Thanks for your suggestions anyway.

 

Duncan

PMoreno Regular Visitor
Regular Visitor

Re: csv data type

Hello,

 

be very careful with blank cells at your excel / csv file in your number columns. Make sure your blank cells are valued to '0' 

 

Thank you