Import error using Openrowset
The problem cause is the Oledb provider
When importing csv file or excel files with mixed data types column it will replace non dominant types by null
. (Using Oledb or Ace.Oledb)
Workarounds
You can do some workaround by adding a first row that contain string values then removing it after impirting is finish
ID,A,B,C
0,a,a,a
1,12,hh,i
2,ab12,tt,b
3,2,aa,o
4,bc12,ee,l
And it will fix the issue if using IMEX=1
This will read columns as string and ID column as number. (0 is used)
Or add HDR=NO
property to connection string so the header is the first line imported (all its values are strings)
Read more about mixed data types in this article
Other methods
Or try to achieve this without aceoledb provider just import csv file in other way like the following:
Using Microsoft Text Driver
SELECT * FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\;',
'SELECT * FROM abc.csv')
Using BULK INSERT
CREATE TABLE dbo.MyTable
(ID INTEGER,
A VARCHAR(50),
B VARCHAR(50),
C VARCHAR(50)
)
BULK INSERT dbo.MyTable
FROM 'C:\abc.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
Other Details in these articles:
- How to import csv file with OPENROWSET?
- T-SQL – Read CSV files using OpenRowSet (A detailed tutorial)
Error Importe Excel With OpenRowSet
The SELECT
statement inside your connection string is not needed, you can remove it:
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;HDR=YES;Database=C:\Hola.xlsx', [Ripley$]')
You may also not need the Xml
portion (but I'm not sure if that would cause an error):
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=YES;Database=C:\Hola.xlsx', [Ripley$]')
Related Topics
Issue Related to Join in Ms Access Database
How to Find 11Th Entry in SQL Access Database Table
How to Write the Equivalent SQL Case Statement for Query Given Below
Get the Row with the Highest Value in MySQL
Syntax Error with Update Query When Join with Some Table
SQL Query: Return Max Value Record of a Group
How to Get Specific Xml Namespace in Xquery in SQL Server
Concat the Second Column Value If the First Column Value Is Same
How to Remove Certain Results from My Search Count
How to Find Duplicate Records in Postgresql
Time Attendances Query in Microsoft Access
How to Concatenate More Than Two Columns in Plsql Developer
Call a Stored Procedure in SQL Cte