Clever Geek Handbook

Best way to import large excel file into SQL Server

We are trying to develop the best method for importing very large Excel files into a SQL database. Using SSIS is a bit of a hassle because it scans the top X records to determine the file format, but the lines can be different later on, so a lot of trial and error is required and we need to bring unusual columns up so SSIS can "learn".

When we get new file formats to import, they follow the specification in terms of string formatting, etc., so we can say that we know the schema in advance. SQL target tables share the same schema with several additional columns such as date and original filename.

Is there an easier way to create format definitions for the new files we're going to insert? We do not need to use SSIS, we are open to any other tool, with as much automation as possible. There is a common sense check on the data that we will be importing, we planned to do basic queries against intermediate datasets such as "less than 1% of records may miss the zip code" etc.

Many thanks

+3


source to share


1 answer


Perhaps you can import the data as text, after which you can transform it using the Derived Column transformation. You can read data from Excel as text using the IMEX parameter in the connection string. You can find more details about this parameter here .



0


source






More articles:


All Articles