Import Excel file into SQL Server - have issues on datatype

Question

Import Excel file into SQL Server - have issues on datatype

I have some Excel data got from SQL Server, but I updated the script of SQL Server, so I need to import Excel into SQL Server again. I used task - import data.

There are some errors:

Copying to [dbo].[AA] (Error) Messages . Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Invalid date format". (SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task 1: There was an error with Destination - AA.Inputs[Destination Input].Columns[Time] on Destination - AA.Inputs[Destination Input]. The column status returned was: "Conversion failed because the data value overflowed the specified type.". (SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Destination - AA.Inputs[Destination Input]" failed because error code 0xC020907A occurred, and the error row disposition on "Destination - AA.Inputs[Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - AA" (107) failed with error code 0xC0209029 while processing input "Destination Input" (120). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)

I don't know if anybody have any suggestions.

Because Excel file only have limited data type, i.e number is float type. There are a lot of datatypes are not matched with destination SQL Server table.

sql-server

excel

asked on Stack Overflow Aug 30, 2016 by

joan • edited Aug 30, 2016 by

marc_s

2 Answers

For high performance and control data type conversion:

1) Save Excel to CSV text file using save As.

2) Use the following script to import data:

BULK
INSERT mytable
FROM 'path_to_csdv_file.csv'
WITH
(
  FIELDTERMINATOR = ';',
  ROWTERMINATOR = '\n'
)

You can use format file to describe data for more details, review:

BULK INSERT

answered on Stack Overflow Aug 30, 2016 by

M.Hassan

It sounds like the data types are misaligned. I thin the easiest thing to do is to use the Import Wizard to get the data types setup correctly.

http://www.wiseowl.co.uk/blog/s231/schedule_data_import_in_sql_server_pt2.htm

Once you have your data types setup correctly, do this: right-click the table > Script Table As > Create To > New Query Editor Window. Now, just copy/paste the results to Excel. Fill in all data types for all fields. Also, do this:

right-click the table > Script Table As > Insert To > New Query Editor Window.

Eventually, you should get something like this.

CREATE TABLE [dbo].[Something]
 (
     [Date]    VARCHAR(MAX),
     Change    VARCHAR(MAX),
     Index_Code    VARCHAR(MAX),
     Price_Return_Level    VARCHAR(MAX)
)
  bulk insert dbo.GII_ICLstg
  from 'C:\your_path_to_file.txt'
  WITH
  (
  FIRSTROW = 2,
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '\n'
  )
  GO
  INSERT INTO dbo.Something
    SELECT
        [Date] = CASE WHEN ISDATE([Date])=1 THEN CAST([Date] AS DATETIME) ELSE NULL END,
        [Change],
        Index_Code,
        Price_Return_Level = CASE WHEN ISNUMERIC(Price_Return_Level) = 1 
    FROM dbo.Something

Personally, I think that's the easiest way to do these kinds of things.

answered on Stack Overflow Dec 10, 2016 by

ASH

User contributions licensed under CC BY-SA 3.0