| Author |
Topic |
|
AJSkydiver
Starting Member
8 Posts |
 Posted - 2011-07-11 : 07:20:02
|
| Hey guys... If someone could help me with this, I would be so appreciative. I have an excel spreadsheet (2007) with a list of a little over 1000 serial numbers listed that I need to load into an existing inventory table. I know I need to create a view, bulk insert into the view and then load from the view into my device inventory table, but I'm stuck...Here is what I have so far...if someone could help me understand the steps I need to perform (along with the code) it would help me out so much...CREATE VIEW vwserialimportASSELECT Field1, Field2, Field3 (do I select all of the columns from the table that I need here? I have all of the data in the excel sheet, so I don't need to pull any values from the existing table..)FROM Device (this is my table that exists)GOBULK INSERT vwserialimportFROM ‘C:\YourFilePath’WITH ( FIELDTERMINATOR = ‘,’,ROWTERMINATOR = ‘\n’)GOAnd then I'm assuming I need to insert into the existing device table the values I'm loading into my view?Thanks in advance guys! edit: moved to proper forum |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-07-11 : 08:20:16
|
Create a staging table instead of a view. When you create the table, you might want to create all columns as varchar(something) if you suspect there may be invalid data.CREATE dbo.Inventory_Staging( col1 VARCHAR(511), col2 VARCHAR(511), ... colN VARCHAR(511)); Once you import into the staging table using bulk insert (or other means), then you can insert the data into your inventory table using a simple select after examining the data to make sure it is correct and as you expect.The other thing I want to point out is that if your file is really an Excel file (.XLS as opposed to a plain text .CSV file), you should use the Import/Export Wizard or SSIS to import the data into the staging table. |
 |
|
|
AJSkydiver
Starting Member
8 Posts |
|
|
AJSkydiver
Starting Member
8 Posts |
Posted - 2011-07-11 : 10:41:09
|
| eek...another issue. I created the package above, but having an issue with a couple of the numeric fields (can't convert between types DT Numeric and DT 14)... As I was looking around trying to find a conversion fix, I've read that I can't do this remotely? Can you only develop the packages (and test) at the server level? If so, i'm back to square 1. :( Sorry to be a pain, I have to get this figured out.. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-07-11 : 12:18:42
|
| Assuming you have proper permissions, you should be able to connect to a SQL server from a remote box and create SSIS packages. |
|
|
|
AJSkydiver
Starting Member
8 Posts |
Posted - 2011-07-11 : 14:13:46
|
| Is there any way someone can help me with how to import data from Excel to an existing SQL Table? This is a one time thing and I seem to be having connection issues when I try via SSIS. If anyone can help, you'll be my new hero :) Thanks for your help sunita, I really need to understand how to do this via my original post. It was a great idea, but this being a one time thing, if I can get help writing the code, that would be so wonderful :) |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-07-11 : 14:32:04
|
| There is step by step instructions to use SSIS Import/Export wizard here. Give it a try and if you run into a problem, post which step you encountered the problem and the error message? |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
AJSkydiver
Starting Member
8 Posts |
Posted - 2011-07-11 : 15:01:53
|
| Thank you...I will give it another go. The link you provided is for SQL 2008... Is there a way I can run the import/export wizard from Management Studio for SQL 2005? |
|
|
|
AJSkydiver
Starting Member
8 Posts |
Posted - 2011-07-11 : 15:04:42
|
| Hey Brett! Yes, I can save the worksheet to a csv file easily. That would be my preference. Can you tell me what steps I need to produce to do a bulk insert using the csv file? Thank you!!! :) |
|
|
|
|
Bulk Insert from xls file