Adding Data from Excel file to SQL server

Question

Adding Data from Excel file to SQL server

aseel.pa

Valued Member

Points: 51
More actions
January 16, 2012 at 1:28 am

#391317

Hello,
i have to add data from the selected columns of an excel file to SQL server every month. we need to edit or delete if its required.
Please advise how to do this task?
Many Thanks in Advance.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

SSIS seems to me the best tool for this task.

You can set it up easily with the Import/export wizard (right click the database node in SSMS and select tasks, import data).

At the end of the wizard, you can save the package and edit to incorporate additional logic.

-- Gianluca Sartori

aseel.pa Valued Member Points: 51 More actions · Answer 2

Is it possible to Add / Edit / Delete operations from this tool? or do i need to write any programs for this?

Thanks,

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

aseel.pa (1/16/2012)
Is it possible to Add / Edit / Delete operations from this tool? or do i need to write any programs for this?
Thanks,

Sure. You can do most of the job using visual editors.

-- Gianluca Sartori

aseel.pa Valued Member Points: 51 More actions · Answer 4

Can you please tell me how will i take the reports after all these?

Many thanks for your support.

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

aseel.pa (1/16/2012)
Can you please tell me how will i take the reports after all these?
Many thanks for your support.

Reports? Which reports? Can you clarify please?

-- Gianluca Sartori

aseel.pa Valued Member Points: 51 More actions · Answer 6

Reports from the SQL server where we added the data from Excel. do we have such a facility?

Thanks.

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 7

You could use BULK INSERT instead, together with a format file (create using bcp utility). Save the Excel file as .csv first.

e.g. at the command line...

bcp Database..Table format nul -c -t, -f output.fmt -T

Then as a query...

BULK INSERT Table

FROM N'C:\inputFile.csv'

WITH (

FIELDTERMINATOR = ',',

KEEPNULLS,

ROWTERMINATOR='/n',

FORMATFILE='C:\output.fmt',

KEEPIDENTITY

)

Replacing Table with your table name, Database with your database name, and the paths as appropriate.

Not sure what you mean about the reporting though, are you referring to SQL Server Reporting Services?

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

Sorry, I quite don't get it.

What should the report contain? Rows from the Excel file?

-- Gianluca Sartori

aseel.pa Valued Member Points: 51 More actions · Answer 9

Yes, from the rows of excel file. and in the above bulk insert, how will i edit or delete?

Thanks.

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 10

Assuming you wanted to edit / delete the data using SQL Server Management Studio or at the command line, rather than using an application, you would use the UPDATE or DELETE statements.

Or edit / delete them before you import them.

More information here:

http://msdn.microsoft.com/en-us/library/ms177523.aspx

http://msdn.microsoft.com/en-us/library/ms189835.aspx

Or am I misunderstanding what you want to do here?

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.