英文:
Google Spreadsheets IMPORTDATA imports "date" from CSV file as integer instead of "date"
问题
我有一个指向CSV文件的URL，我想将其导入到Google电子表格中。我正在使用IMPORTDATA函数：
=IMPORTDATA(B21)
你可以在这里查看文档：电子表格链接
我的CSV文件看起来像这样：
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
...
第一列是日期。当CSV文件在电子表格中呈现时，第一列的日期被转换为整数数字：
45091
45094
45101
整个Google电子表格文件如下所示：
我不知道这些数字与给定的日期有什么关系，如果我知道的话，我可以使用一些公式将它们重新计算为正确的日期。我也尝试使用查询：
=QUERY(IMPORTDATA( ), "选择 * ")
但这并没有解决我的问题。如何正确导入日期，使其保持原样？
英文:
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:
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?
答案1
得分: 0
可以使用另一个技巧来代替格式化：
=text(B2, "yyyy-mm-dd")
我建议在您的数据之前插入一个新列（因此从B列使用importdata公式）。在A列中，您使用标题“Clean Date”并使用以下公式：
=arrayformula(if(B2:B<>"", text(B2:B, "yyyy-mm-dd"), ""))
在这种情况下，公式首先检查列B的行中是否有值。如果有值，它将执行文本公式。arrayformula确保此公式将在整个列上执行，因此您不必在每一行中添加公式（使您的工作表更快速，减少错误）。
英文:
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).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论