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

查看:243
本文介绍了使用OpenRowSet导入.CSV文件时,将科学计数法转换为浮点数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在使用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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆