Solved

MS SQL Insert data into table from excel file

Posted on 2006-11-16
7
18,744 Views
Last Modified: 2012-05-05
Sir, I have an excel file with the following two columns:

File_job & ICNumber


I have an table in my SQL Server name cargodetail where i have the same column:

file_job & ICNumber

I want to insert data from excel file in my table (cargodetail where file_job table file_job)

Please help
0
Comment
Question by:Mehram
7 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 17955750
hi
you can export the data from excel and save it as tab delimited file
then, in mysql you can use the load data command
http://dev.mysql.com/doc/refman/5.0/en/load-data.html

the link describes the syntax and the file format
0
 

Expert Comment

by:emerkel
ID: 17955960
Hi,

Is this a one time import or something that will have to be run periodically?  If it's a one time deal and you have a good text editor, the thing that I have found easier than anything else is just to export the data from excel into a delimited file, as momi sabag said above, then run a regular expression find and replace to generate all the insert lines, then open the file in query analyzer or ssms and run it.  You could also just make a forumla in your excel file to create a column of the insert statements, like 'insert tablename ' + CELL:ROW + ', ' + CELL:ROW and then copy all of the values from that column into query analyzer or ssms and run it.  Either way works really easily.  

Hope this helps

Eddie
0
 
LVL 5

Expert Comment

by:MrNetic
ID: 17956187
Mehram,

The best practice is to use the SQL Server Import, with this tool, you can directly import the data.

Best Regards,

Paulo Condeça.
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 9

Expert Comment

by:PeterMac
ID: 17957873
Need a little more detail before can give best advice.
Does your SQL Table contain more than just the two already given columns ??
Is this a one off operation, or something you would need to do on a regular basis ??
Are you using full version of MSSQL Server, or cut down version MSDE, or Express ??
Does Excel data contain any leading Zeroes - e.g. File_Job = 000123 ??
0
 
LVL 23

Expert Comment

by:Christopher Kile
ID: 17958601
Consult T-SQL help on the stored procedure sp_addlinkedserver, as this includes an Excel example, if you need to use the Excel data directly in queries (and if you have the necessary OLE DB drivers).  Otherwise, some of these other suggestions might prove more useful.
0
 
LVL 3

Expert Comment

by:pweegar
ID: 17959247
Importing from Excel into SQL Server isn't all that difficult. Open the Entrise manager. Expand Databases and find your db. Right click, then choose "All Tasks". From there choose Import Data. Follow the on line prompts. MAke sure you choose your version of Excel as the source. And choose which db and table to import. Once you have choosen everything, let it run either immediately or whenever you want it to.
0
 
LVL 9

Expert Comment

by:PeterMac
ID: 17963725
Warning, while quite simple in principle, there are number of potential pitfalls with import into SQL. Make sure you have backup of original database, and preferably work initially with copy, until you are sure of what you are doing.
Still need additional information requested before to advise on best method for this.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question