SQL Server OPENROWSET Numeric vales changes as int

Question

I have a problem in my SQL Server 2008 R2 OPENROWSET to read the csv file.

It automatically converts the numeric values as int while read from the csv file.

I have a csv file with column ColumnA with 30 rows.

Here is my code

declare @SysFilepath as varchar(max)
set @SysFilepath='C:\Users\rtdev1\Desktop\'
declare @SysFilename as varchar(max)
set @SysFilename='Test.csv' 

 declare @str as varchar(max)=''                                                         
 set @str='SELECT *  FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',  
 ''Text;Database='+@SysFilepath+';HDR=yes'',''select * from ['+@SysFilename+']'')'
 exec(@str)

Any my result as below.

5.654 value changed as 5 and 0.25475 value changed as 0.

the column datatype automatically changed if the column has int value for more than 25 rows.

can anyone please help to sort out.

Thanks.

Answer 1

After Change the code to Bulk Insert now i can able to get the correct result.

create table #CSVLine
(
  ColumnA   VARCHAR(MAX)
)

declare @Sql as varchar(max)
declare @InputFile as varchar(max)
set @InputFile = 'E:\MyPath\MyFile.csv'
declare @FirstLine int
set @FirstLine =1

SET @Sql = 'BULK INSERT #CSVLine
 FROM ''' + @InputFile + '''
 WITH
 (
    FIRSTROW=' + CAST(@FirstLine as varchar) + ',
    FIELDTERMINATOR = '','',
    ROWTERMINATOR = ''\n''
 )

 select * from #CSVLine'

 EXEC(@sql)

Thanks @Jeroen Mostert