Importing from Excel problems

What do others do if you need to import excel files into SQL Server?
My main problems are

1) zipcode formatting issues. If the column is a mix of zip and zip+4,
I have problems retrieving all zipcodes.

2) If the last column contains NULL no information is imported.

All this with using the Management console using Import data in SQL
Server 2005. I am simply trying to import the data into NEW databases.
The excel files vary in structure. Right now I am working on case by
case basis.

Does anyone see these types of problems?

What I am doing now is converting the excel file to a tab delimited
file and that seems to work.

TIA.On Dec 11, 12:32 pm, scoots987 <scoots...@.gmail.comwrote:

Quote:

Originally Posted by

What do others do if you need to import excel files into SQL Server?
My main problems are
>
1) zipcode formatting issues. If the column is a mix of zip and zip+4,
I have problems retrieving all zipcodes.
>
2) If the last column contains NULL no information is imported.
>
All this with using the Management console using Import data in SQL
Server 2005. I am simply trying to import the data into NEW databases.
The excel files vary in structure. Right now I am working on case by
case basis.
>
Does anyone see these types of problems?
>
What I am doing now is converting the excel file to a tab delimited
file and that seems to work.
>
TIA.

Hi scoots987,

I usually use a dummy row in my excel files to force the correct data
types and column mappings (typically I import everything as text and
convert it downstream). One of the big problems with importing data
from an Excel file is that SQL Server (well .. the I think it's
actually the OLEDB driver) only looks at the first 8 rows of data to
determine what data types to use. To force it to look at more rows,
you need to change a couple registry settings, which in my experience
is usually off-limits in a managed production environment (check out
http://blog.lab49.com/?p=196 for info .. it's not a SQL blog, but it
explains the issue well).

Good luck!
J|||I've recently had great experience importing Excel into the DB using
SSIS (SQL Server Integration Services) and the OLE DB Excel Connection.
It has transforms and all sorts of goodies to make the import easy and
omplete.

HTH

aj

scoots987 wrote:

Quote:

Originally Posted by

What do others do if you need to import excel files into SQL Server?
My main problems are
>
1) zipcode formatting issues. If the column is a mix of zip and zip+4,
I have problems retrieving all zipcodes.
>
2) If the last column contains NULL no information is imported.
>
All this with using the Management console using Import data in SQL
Server 2005. I am simply trying to import the data into NEW databases.
The excel files vary in structure. Right now I am working on case by
case basis.
>
Does anyone see these types of problems?
>
What I am doing now is converting the excel file to a tab delimited
file and that seems to work.
>
TIA.

importing data from excel

I have an excel document that I need to import into a table. The format of the Excel spreadsheet has three columns:

|First Name | Last Name | Zip code|

This data is not normalized. I want to convert the data in the spreadsheet to foreign key values for two existing tables.

The table I'm importing to will contain records with a many-to-many relationship.

For example,

|John|Smith|77079

imports as:

|1|1|

Where "Smith" is the first record in the 'rep' table and "77079" is the first record in the 'zip' table.

How can I bring the data in and then convert the de-normalized data into ID values from my existing tables?

I have thousands of records that I need to do this for and want to automate the process.

Thanks for any help.
Regards,
-D-Create a staging table for the import, where you can bring in the "dirty" data from the spreadsheet. Once you've got it there, check to be sure you've got all of the users, and determine how you want to handle any that are AWOL. Do the same with zip codes. After you've got clean data, insert the appropriate rows into your production table, then you probably don't need your staging data anymore so you may decide to delete it.

-PatP

Importing a zip folder

Hello all,
I will like to know how to create a process that unzips a folder and
import all its files into SQL Server 2000/2005.
Any suggestions will be greatly appreciated.
Thanks.You want to import it the files as binary objects into the SQL Server? I
haven't made a tool to do unzip and import all, but I have done a simple
binary file import utility for SQL 2000 enviornment that I can send over to
you if you want.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"SQL Pupil" wrote:
> Hello all,
> I will like to know how to create a process that unzips a folder and
> import all its files into SQL Server 2000/2005.
> Any suggestions will be greatly appreciated.
>
> Thanks.
>|||> I will like to know how to create a process that unzips a folder and
> import all its files into SQL Server 2000/2005.
> Any suggestions will be greatly appreciated.
There are a number of approaches. One method is to create a DTS package
that runs a command-line extract utility and then an ActiveX Script like the
one example below. You can similarly do this with SSIS and a .Net Script
task.
If you want to get fancy and forego the command-line extract, you extract
directly from .Net Script using ICSharpCode.SharpZipLib.dll
(http://www.icsharpcode.net/OpenSource/SharpZipLib/Download.aspx).
'DTS ActiveX example
Const adOpenKeyset = 1
Const adLockOptimistic = 3
Const adTypeBinary = 1
Function Main()
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set fso = CreateObject("Scripting.FileSystemObject")
Set strm = CreateObject("ADODB.Stream")
ConnectionString = "Provider=SQLOLEDB" & _
";Data Source=MyServer" & _
";Initial Catalog=MyDatabase" & _
";Integrated Security=SSPI"
conn.Open ConnectionString
rs.Open "SELECT File_Name, File_Contents FROM dbo.MyTable", conn,
adOpenKeyset, adLockOptimistic
Set folder = fso.GetFolder("C:\MyExtractedFiles")
Set fileList = folder.Files
For Each file In fileList
InsertFile(file.Name)
Next
conn.Close
Main = DTSTaskExecResult_Success
End Function
Sub InsertFile(FileName)
strm.Type = adTypeBinary
strm.Open
strm.LoadFromFile FileName
rs.AddNew
rs.Fields("File_name") = FileName
rs.Fields("File_Contents") = strm.Read
rs.Update
strm.Close
End Sub
Hope this helps.
Dan Guzman
SQL Server MVP
"SQL Pupil" <guido_a_perez@.yahoo.com> wrote in message
news:1182447929.191367.191100@.w5g2000hsg.googlegroups.com...
> Hello all,
> I will like to know how to create a process that unzips a folder and
> import all its files into SQL Server 2000/2005.
> Any suggestions will be greatly appreciated.
>
> Thanks.
>|||Sure, any help that you can provide or any ideas, they all are going
to be welcome.
Thanks again|||I don't know .NET but my code is pretty similar to what Dan has offered here.
My code has some extra stuff, for pulling the table information and such on
the fly. It's still under fix up though, because I only use it for few
databases. So I can't say it will work all accross the board. But if you
want I can still send it ;-)
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Dan Guzman" wrote:
> > I will like to know how to create a process that unzips a folder and
> > import all its files into SQL Server 2000/2005.
> >
> > Any suggestions will be greatly appreciated.
> There are a number of approaches. One method is to create a DTS package
> that runs a command-line extract utility and then an ActiveX Script like the
> one example below. You can similarly do this with SSIS and a .Net Script
> task.
> If you want to get fancy and forego the command-line extract, you extract
> directly from .Net Script using ICSharpCode.SharpZipLib.dll
> (http://www.icsharpcode.net/OpenSource/SharpZipLib/Download.aspx).
>
> 'DTS ActiveX example
> Const adOpenKeyset = 1
> Const adLockOptimistic = 3
> Const adTypeBinary = 1
> Function Main()
> Set conn = CreateObject("ADODB.Connection")
> Set rs = CreateObject("ADODB.Recordset")
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set strm = CreateObject("ADODB.Stream")
> ConnectionString = "Provider=SQLOLEDB" & _
> ";Data Source=MyServer" & _
> ";Initial Catalog=MyDatabase" & _
> ";Integrated Security=SSPI"
> conn.Open ConnectionString
> rs.Open "SELECT File_Name, File_Contents FROM dbo.MyTable", conn,
> adOpenKeyset, adLockOptimistic
> Set folder => fso.GetFolder("C:\MyExtractedFiles")
> Set fileList = folder.Files
> For Each file In fileList
> InsertFile(file.Name)
> Next
> conn.Close
> Main = DTSTaskExecResult_Success
> End Function
> Sub InsertFile(FileName)
> strm.Type = adTypeBinary
> strm.Open
> strm.LoadFromFile FileName
> rs.AddNew
> rs.Fields("File_name") = FileName
> rs.Fields("File_Contents") = strm.Read
> rs.Update
> strm.Close
> End Sub
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SQL Pupil" <guido_a_perez@.yahoo.com> wrote in message
> news:1182447929.191367.191100@.w5g2000hsg.googlegroups.com...
> > Hello all,
> >
> > I will like to know how to create a process that unzips a folder and
> > import all its files into SQL Server 2000/2005.
> >
> > Any suggestions will be greatly appreciated.
> >
> >
> > Thanks.
> >
>|||Mohit, since I am in the process of gathering all the SQL info and
knowledge that I can get, anything that you can send me will be very
usefull.
Thanks again|||Emailed to your profile address ;-).
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"SQL Pupil" wrote:
> Mohit, since I am in the process of gathering all the SQL info and
> knowledge that I can get, anything that you can send me will be very
> usefull.
> Thanks again
>

Importing a zip folder

Hello all,
I will like to know how to create a process that unzips a folder and
import all its files into SQL Server 2000/2005.
Any suggestions will be greatly appreciated.
Thanks.You want to import it the files as binary objects into the SQL Server? I
haven't made a tool to do unzip and import all, but I have done a simple
binary file import utility for SQL 2000 enviornment that I can send over to
you if you want.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"SQL Pupil" wrote:

> Hello all,
> I will like to know how to create a process that unzips a folder and
> import all its files into SQL Server 2000/2005.
> Any suggestions will be greatly appreciated.
>
> Thanks.
>|||> I will like to know how to create a process that unzips a folder and
> import all its files into SQL Server 2000/2005.
> Any suggestions will be greatly appreciated.
There are a number of approaches. One method is to create a DTS package
that runs a command-line extract utility and then an ActiveX Script like the
one example below. You can similarly do this with SSIS and a .Net Script
task.
If you want to get fancy and forego the command-line extract, you extract
directly from .Net Script using ICSharpCode.SharpZipLib.dll
(http://www.icsharpcode.net/OpenSour...b/Download.aspx).
'DTS ActiveX example
Const adOpenKeyset = 1
Const adLockOptimistic = 3
Const adTypeBinary = 1
Function Main()
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set fso = CreateObject("Scripting.FileSystemObject")
Set strm = CreateObject("ADODB.Stream")
ConnectionString = "Provider=SQLOLEDB" & _
";Data Source=MyServer" & _
";Initial Catalog=MyDatabase" & _
";Integrated Security=SSPI"
conn.Open ConnectionString
rs.Open "SELECT File_Name, File_Contents FROM dbo.MyTable", conn,
adOpenKeyset, adLockOptimistic
Set folder =
fso.GetFolder("C:\MyExtractedFiles")
Set fileList = folder.Files
For Each file In fileList
InsertFile(file.Name)
Next
conn.Close
Main = DTSTaskExecResult_Success
End Function
Sub InsertFile(FileName)
strm.Type = adTypeBinary
strm.Open
strm.LoadFromFile FileName
rs.AddNew
rs.Fields("File_name") = FileName
rs.Fields("File_Contents") = strm.Read
rs.Update
strm.Close
End Sub
Hope this helps.
Dan Guzman
SQL Server MVP
"SQL Pupil" <guido_a_perez@.yahoo.com> wrote in message
news:1182447929.191367.191100@.w5g2000hsg.googlegroups.com...
> Hello all,
> I will like to know how to create a process that unzips a folder and
> import all its files into SQL Server 2000/2005.
> Any suggestions will be greatly appreciated.
>
> Thanks.
>|||Sure, any help that you can provide or any ideas, they all are going
to be welcome.
Thanks again|||I don't know .NET but my code is pretty similar to what Dan has offered here
.
My code has some extra stuff, for pulling the table information and such on
the fly. It's still under fix up though, because I only use it for few
databases. So I can't say it will work all accross the board. But if you
want I can still send it ;-)
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Dan Guzman" wrote:

> There are a number of approaches. One method is to create a DTS package
> that runs a command-line extract utility and then an ActiveX Script like t
he
> one example below. You can similarly do this with SSIS and a .Net Script
> task.
> If you want to get fancy and forego the command-line extract, you extract
> directly from .Net Script using ICSharpCode.SharpZipLib.dll
> (http://www.icsharpcode.net/OpenSour...b/Download.aspx).
>
> 'DTS ActiveX example
> Const adOpenKeyset = 1
> Const adLockOptimistic = 3
> Const adTypeBinary = 1
> Function Main()
> Set conn = CreateObject("ADODB.Connection")
> Set rs = CreateObject("ADODB.Recordset")
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set strm = CreateObject("ADODB.Stream")
> ConnectionString = "Provider=SQLOLEDB" & _
> ";Data Source=MyServer" & _
> ";Initial Catalog=MyDatabase" & _
> ";Integrated Security=SSPI"
> conn.Open ConnectionString
> rs.Open "SELECT File_Name, File_Contents FROM dbo.MyTable", conn,
> adOpenKeyset, adLockOptimistic
> Set folder =
> fso.GetFolder("C:\MyExtractedFiles")
> Set fileList = folder.Files
> For Each file In fileList
> InsertFile(file.Name)
> Next
> conn.Close
> Main = DTSTaskExecResult_Success
> End Function
> Sub InsertFile(FileName)
> strm.Type = adTypeBinary
> strm.Open
> strm.LoadFromFile FileName
> rs.AddNew
> rs.Fields("File_name") = FileName
> rs.Fields("File_Contents") = strm.Read
> rs.Update
> strm.Close
> End Sub
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SQL Pupil" <guido_a_perez@.yahoo.com> wrote in message
> news:1182447929.191367.191100@.w5g2000hsg.googlegroups.com...
>|||Mohit, since I am in the process of gathering all the SQL info and
knowledge that I can get, anything that you can send me will be very
usefull.
Thanks again|||Emailed to your profile address ;-).
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"SQL Pupil" wrote:

> Mohit, since I am in the process of gathering all the SQL info and
> knowledge that I can get, anything that you can send me will be very
> usefull.
> Thanks again
>