Importing Excel table back into SQL 2005 database

[jdpatterson74]

Hello, I am joshua...

I am new to ablecommerce for a few months now and looking to import my excel tables back into my database.

I used excel 2007 to connect to my local server and export a database table from my dev. site. I have made some major changes and would like to import the new data back into my database.

If it helps, I am using Excel 2007, SQL Management Studios 2005...

Could someone point me in the right direction or share with me the steps to tranfer this data?

Thanks!

Joshua

[nickc]

What used to be "DTS" in SQL 2000 is "SSIS" in SQL 2005.
Import can be found by right-clicking on the database in SMSS, selecting "Tasks" and "Import Data".
You may need to save your Excel in .xls (97-2003) format first.

The wizard tool can be run independently as "C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe"

[jdpatterson74]

I am sorry, I have the SQL Management Studios Express edition. When I right click on the database and go to "Tasks"...detach, shrink, backup, restore, generate scripts.

What edition do I need to get? I checked out the prices for workgroup and standard and I am

Do I have to use those programs to import and export data?

[nickc]

Hmm. Only other option I'm familiar with is to write some code.

You could make a small web app inside Able (so you can use CommerceBuilder methods) that accepts an Excel file as an HttpPostedFile object, builds a DataTable from a tab or named range, and then iterates the DataTable calling Product.Load() for each row, updating Product.<Property> where appropriate and calling Product.Save().
Or, you could also have someone who does have full SQL Server import your excel file as is to a new temporary table in your Able database (or in a separate database), then write some SQL scripts to update the ac_ table(s) from your table.

[jdpatterson74]

Can I use the Able Commerce Data Client utility for this purpose? I am downloading it now...

[nickc]

Huh. Forgot all about that. I've not used it for products, only for exporting orders. Not sure that you can update existing products. Perhaps someone else can comment.

Good Luck,
-Nick

[jdpatterson74]

Thanks Nick...

Off to learn xml...any quick tip pointers about importing or editting a xml file in excel?

"If you mess up enough, you are sure to get it right." - Me

[jmestep]

Yes, you can update existing products. If it only affects data in one table, like product names, you can do it with the .csv import. The best thing to do is export your product table and use those headings to format your import. I did find out that if you don't import all the fields, unexpected things might happen, at least what I didn't expect. I exported the products, changed the status on some, then imported the file back, but I only mapped a few fields. I didn't have my prices mapped, so they all turned to 0.

[jdpatterson74]

Ok....

So the only way to import new products or tables (using the pre-existing format) will be SQL Workplace Edition or higher.....or to have perl codes made up?

Currently, I am using the original design of the tables.....is there a standard code for importing or export tables that have the original layout design?

[jmestep]

My shared hoster just locked down the databases more and I had to switch from SQL Server Management Studio to EMS SQL Manager to be able to view the tables, etc. You might look into that- there is a lite edition that is free, which is what I started using.
http://www.sqlmanager.net/en/products/mssql/manager