SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Import Excel db to 2012 Express
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

fgruber
Starting Member

USA
3 Posts

Posted - 06/29/2013 :  17:08:18  Show Profile  Reply with Quote
I'm just playing with this stuff for fun. I have an excel spreadsheet database of 50,000 records of basic contact data, and tring to import to
SQL Server Exp 2012. From Excel, I saved the file as a .csv and used the import progrm. It looks like it started to work but i got an error that said a field was truncated and the import process seemed to abort.

I don't even know how to check if there is a database somewhere on my pc or not?

Any suggestions and is there a SQL Server for Dummies anywhere someone might recommend.
Thx

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 06/29/2013 :  17:57:36  Show Profile  Reply with Quote
quote:
Originally posted by fgruber

I'm just playing with this stuff for fun. I have an excel spreadsheet database of 50,000 records of basic contact data, and tring to import to
SQL Server Exp 2012. From Excel, I saved the file as a .csv and used the import progrm. It looks like it started to work but i got an error that said a field was truncated and the import process seemed to abort.

I don't even know how to check if there is a database somewhere on my pc or not?

Any suggestions and is there a SQL Server for Dummies anywhere someone might recommend.
Thx

I assume you used the Import/Export wizard. In the wizard, when you get to the "Select Source Tables and Views" dialog, there is a "Edit Mappings" button. Click on that - it will show you the columns that are going to be imported, and what the Import/Export wizard thinks the data type of each column is and what the width for each character column is. That determination is made my sampling the first few rows (100 rows, if I am not mistaken).

The error you are seeing is because some of the rows that were not sampled have data that is longer than what Import/Export wizard calculated by sampling. So you need to manually change those in that dialog to the appropriate larger values.

The "Select Source Tables and Views", in spite of the name, also show the destination tables/views as well. So that is where the data goes to.
Go to Top of Page

fgruber
Starting Member

USA
3 Posts

Posted - 06/29/2013 :  20:47:54  Show Profile  Reply with Quote
thanks...tried that, didn't work. Edit Mappings screen - no apparent way to edit length of column assigned by default. Box w/ text "Edit SQL" was grayed out - not functional. But I was able to tell mappings to ignore the column. However, still got the same "truncation error". Since I started w/ a Flat file (csv), I wasn't sure what the destination file should be but it defaulted to some sort of SQL Client.
Also Wizard indicated, before execution, that the "package" it was creating could not be saved since i would have to upgrade to Server Standard, Enetrprise, Deve;oper or Eval. Maybe the Edit mapping function is limited w/ Express version too.
Maybe the Express version isn't a good way to start. What are the costs w/ the other versions? cost? should i switch? your advice is appreciated,
thx
fg
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 06/29/2013 :  23:29:53  Show Profile  Reply with Quote
fgruber, I don't have an installation of SQL Express to test. According to this page, you are right in that there is no option to save the package in SQL Express http://msdn.microsoft.com/en-us/library/ms140052.aspx but I don't see any other limitations listed.

What you should see when you click the Edit Mappings screen is a dialog which has a table labeled mappings that indicates the Source and destination widths, data type, whether it is nullable, size, precision and scale. You can click in there and change those to what you want.

As for the costs, I don't know the costs - but before you do that though, unless money is no object, explore other means - see a list here http://support.microsoft.com/kb/321686
Go to Top of Page

fgruber
Starting Member

USA
3 Posts

Posted - 06/30/2013 :  02:04:47  Show Profile  Reply with Quote
Yeah, it wasn't letting me change column size - no matter what i did.

The option you mentioned showed an example of getting Excel Data into SQL, required prior versions of Server. Sounds lie that option requires me uninstalling 2012, downloading installing priors, etc...Seems like the long way to get a simple import feature to work, but what do i know, i thought thi would be kind of a user-friendly program - obviously not the case,
thanks
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 06/30/2013 :  08:24:30  Show Profile  Reply with Quote
When you use the Import/Export wizard, by default, it tries to create a new table for the destination unless a table that matches the import dataset already exists. But you can change that.

So one thing you could do is to create the destination table in your database (with sufficient length for the character columns), and in the import/export wizard select that table for the destination.

Regarding your comment about user-friendliness, the SQL Server stack is a huge system with all kinds of features and supporting a variety of environments, sources, applications etc. So there is a lot of material, and there is a lot to learn. My experience has been that SQL Server is an awesome product with very few bugs or quirks and behaves as one would intuitively expect for the most part. But the sheer size of the features and facilities makes it hard to pick up speed for someone starting out.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000