使用OpenRowSet导入.CSV文件时，将科学计数法转换为浮点数 [英] Convert scientific notation to float when using OpenRowSet to import a .CSV file

问题描述

我正在使用openrowset将csv文件导入SQL Server. csv文件中的一列包含科学计数法(1.08E + 05)中的数字以及要插入的表中的列

I am using openrowset to import a csv file into SQL Server. One of the columns in the csv file contains numbers in scientific notation (1.08E+05) and the column in the table it is being inserted

默认情况下，它将值导入为1并忽略.08E + 05.

By default it is importing the value as 1 and ignoring the .08E+05.

我尝试使用cast()和convert()在执行查询时直接转换值，以及将表中的数据类型设置为字符串并将其导入.在忽略.08E + 05的情况下，所有这些方法都具有相同的行为.

I have tried using cast() and convert() to convert the value directly when the query is executed as well as setting up the datatype in the table as a character string and importing it as such. All of these methods have the same behavior where the .08E+05 is ignored.

有没有一种方法可以在不更改csv文件本身的情况下将值导入为108000(而不是1)而不带.08E + 05?

Is there a way to have the value imported as 108000 instead of 1 without the .08E+05 without having to change the csv file itself?

将数据类型设置为varchar并读取csv文件似乎与以下代码具有相同的效果:

Setting up the datatype as a varchar and reading in the csv file appears to have the same effect with the following code:

CREATE TABLE #dataTemp (StartDate datetime, Value varchar(12))

SET @insertDataQuery = 'SELECT Date, CSVValue from OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=' 
SET @insertDataQuery = @insertDataQuery + 'C:\Data\;'',''SELECT * FROM '+ '11091800.csv' + ''')'

INSERT INTO #dataTemp EXEC(@insertDataQuery)

SELECT * FROM #dataTemp

并非CSV文件中的所有值都具有科学符号，而没有CSV值的值，例如81000没问题.

Not all of the values in the CSV file have the scientific notation and the value without it, e.g. 81000 come across without issue.

推荐答案

对于BULK INSERT方法，我经常发现先将数据移到所有varchars的表中，然后摆脱诸如带引号的定界符之类的无关紧要的东西，这比较简单.并修复格式.我记得曾经有一段时间摆脱科学记数法，您可以只使用varchar表，直到正确为止.我记得尝试过各种精度/比例组合，直到最终找到兼容的组合.我认为对我来说是FLOAT然后是DECIMAL(24,12) ...

For BULK INSERT methodologies I've often found it simpler to first move the data into a table of all varchars, then get rid of extraneous things like quoted delimiters and fix formatting. I remember having a heck of a time getting rid of the scientific notation, you can just play with the varchar table until you get it right. I remember attempting all kinds of precision/scale combinations until I finally found one that was compatible. I think for me it was FLOAT then DECIMAL(24,12)...

SELECT CONVERT(DECIMAL(24, 12), CONVERT(FLOAT, '1.08E+05'));

编辑，添加了我尝试复制和/或演示一种不太复杂的方式的内容.

EDIT adding what I did to try to repro and/or demonstrate a less convoluted way.

我创建了一个非常简单的CSV文件:

I created a very simple CSV file:

StartDate,Value
20110808,81000
20110808,1.08E+05

然后，我运行了以下代码(出于某种原因，我无法使MSDASQL在我的计算机上运行以挽救生命):

Then I ran the following code (for some reason I can't get MSDASQL to run on my machine to save my life):

CREATE TABLE #dataTemp(StartDate DATETIME, Value VARCHAR(32));

BULK INSERT #dataTemp FROM 'C:\data\whatever.csv' 
    WITH (ROWTERMINATOR='\n', FIELDTERMINATOR=',', FIRSTROW = 2);

SELECT * FROM #dataTemp
GO
SELECT StartDate, CONVERT(INT, CONVERT(FLOAT, Value)) FROM #dataTemp;
GO
DROP TABLE #dataTemp;

结果:

StartDate               Value
----------------------- --------
2011-08-08 00:00:00.000 81000
2011-08-08 00:00:00.000 1.08E+05

StartDate               (No column name)
----------------------- ----------------
2011-08-08 00:00:00.000 81000
2011-08-08 00:00:00.000 108000

这篇关于使用OpenRowSet导入.CSV文件时，将科学计数法转换为浮点数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！