Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cplusplus
Aged Yak Warrior
567 Posts |
 Posted - 2013-10-14 : 10:58:13
|
| i have 36 excel files all has same columns how to merge all 36 excel files data into one single table, excluding just first row. column headers.Is it possible thru queries?columns are:prog, proj, contract, logtype, sequence, title, mtd_dollars, ytd_dollars, cost_centerThanks a lot for the helpful info. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-14 : 11:04:41
|
| You can put all the files into a folder, and use a foreach loop. There is an example here: http://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspxAnother low-tech approach would be to use Import/Export Wizard. Run the import/export wizard to import the first file, and in the process save the package. Once you have the package, open it up and change the connection string to point to the next file and run it. You can then semi-automate it by making the change to the connection string and looking up the command line script to effect that change (on the package one of the tabs shows you the command line parameters). Once you know that commandline script, replicate the script with each filename. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-14 : 12:35:43
|
| you can use SSIS,OPENROWSET or BULK INSERT methodsIf using SSIS you've for each loop available which can iterate through files in a folder. In other cases you might need to ad some logic to do iteration yourself.I'd a similar requirement where I had to do daily processing of files and I used approach as explained belowhttp://visakhm.blogspot.in/2012/05/package-to-implement-daily-processing.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-14 : 12:44:05
|
quote:
Originally posted by visakh16
you can use SSIS,OPENROWSET or BULK INSERT methodsIf using SSIS you've for each loop available which can iterate through files in a folder. In other cases you might need to ad some logic to do iteration yourself.I'd a similar requirement where I had to do daily processing of files and I used approach as explained belowhttp://visakhm.blogspot.in/2012/05/package-to-implement-daily-processing.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
How do you import an Excel file into SQL Server using BULK INSERT? I have not even tried, assuming perhaps incorrectly that that is not possible. |
|
|
|
|
|
|
|
|
i have 36 excel files all has same columns how to