csvgoogle-sheetsimportdata

Google Spreadsheets IMPORTDATA imports "date" from CSV file as integer instead of "date"


I have an URL to a CSV file which I would like to import to google spreadsheets. I am using IMPORTDATA function :

=IMPORTDATA(B21)

You can see the doc in here: spreadsheed link

My csv file looks like :

2023-06-14,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
2023-06-17,3,58,0,87,43,1,0,1,3,2,7,0,2,5,2,3,9,0,4,6,4,23,17
2023-06-24,7,185,0,263,123,6,1,1,8,7,16,1,7,16,7,5,15,2,7,8,7,80,31
...

The first column is a date. When the CSV is presented in the spreadsheet the dates from the first column are transformed into integer numbers:

45091
45094
45101

The whole file google spreadsheet looks like:

enter image description here

I have no clue how this numbers relate to the given dates, if I knew I could use some formula to recalculate them into proper dates. I have tried also to use query

=QUERY(IMPORTDATA( ), "Select * ")

But this did not solve my issue. How to import the dates correctly as they are?


Solution

  • Instead of formatting you can also use another trick:

    =text(B2, "yyyy-mm-dd")
    

    I can recommend to insert a new column before your data (so use importdata formula from column B). In column A you use header "Clean Date" and use the formula:

    =arrayformula(if(B2:B<>"", text(B2:B, "yyyy-mm-dd"), ""))
    

    In this case the formula first checks if there is a value in the row of column B. If there is a value there it will perform the text formula. The arrayformula makes sure that this formula will be performed over the whole column so you don't have to add a formula in every row (makes your sheet faster and it will contain less errors).