Import data from Excel/CSV to SQL Server

Question

I have some questions about importing data from Excel/CSV File into SQL Server. Let me first explain the overall scenario.

We receive data from multiple sources and in either Excel/CSV format. This data is to be imported into SQ Server into a table. Because we receive the data from multiple sources we have a requirement to map the columns in the Excel files to the columns in our SQL Server table.

I understand that either DTS or the Import / Export wizard is the way to import this data if we were to do this import manually. But I have the following questions

Are there alternatives available to DTS/Import export wizard?
If I were to write an application for importing data what are the .net framework classes that I would or could use? For some reason I don't want to use or build a SQL script within the application. What would be the best way of going about doing this?
Is there any way we can reduce the effort involved in mapping data?

Any suggestions, help would be most welcome

Regards

Romi

Are you using an older version of sql? You said DTS and not SSIS. — sam yi
– sam yi, Commented Jun 20, 2012 at 18:01
I have in the past have setup a Linked Server to access excel spreadsheets: support.microsoft.com/kb/306397 — Mark Kram
– Mark Kram, Commented Jun 20, 2012 at 18:26
Sam, I am using SQL Server 2008 r2 Express. So only the import/export features /capabilities of SSIS are not available to me as I understand. — Romi24
– Romi24, Commented Jun 20, 2012 at 18:48
Thanks Mark for the insight. But my Excel files are going to be from different sources and with different column names. A linked server would be very helpful if my Excel file had pre-determined columns with predictable names etc. Tell me if i am wrong or not on the right track — Romi24
– Romi24, Commented Jun 20, 2012 at 18:51

sam yi · Accepted Answer · 2012-06-20 18:30:37Z

2

Are there alternatives available to DTS/Import export wizard? -- bulkinsert.

If I were to write an application for importing data what are the .net framework classes that I would or could use? For some reason I dont want to use or build an SQL Script within the application. What would be the best way of going about doing this? -- SSIS.

Is there any way we can reduce the effort involved in mapping data? -- ?

SSIS is a very powerful tool. May want to explore that option first. You can even build custom component using .net as well.

edited Jun 20, 2012 at 18:30

answered Jun 20, 2012 at 17:57

sam yi

4,9451 gold badge33 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Romi24 Over a year ago

I think bulkinsert would be great for speed but I cant map column names with bulkinsert, can I? Also can you please give me some high level insight into how SSIS will be helpful? Appreciate it

sam yi Over a year ago

How come you don't want to use scripts / stored procs?

sam yi Over a year ago

You can use SSIS Data Flow task to copy the data over to the database and use SQL Script task to run the mapping logic. Ideally though, that logic would reside in a store procedure.

Collectives™ on Stack Overflow

Import data from Excel/CSV to SQL Server

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related