Import data from Excel/CSV to SQL Server

2
votes

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

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

  2. 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?

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

Any suggestions, help would be most welcome

Regards

Romi

1
sql-serverexcelcsvimport
Are you using an older version of sql? You said DTS and not SSIS. – sam yi
I have in the past have setup a Linked Server to access excel spreadsheets: support.microsoft.com/kb/306397 – Mark Kram
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
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

1 Answers

2
votes

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.