Bulk Insert from Excel [Programming SQL]

From: UnglueD on 28 Jul 2006 09:11

Manuel wrote:
> I´m new to this subject. As a matter of a fact, the first time I´m trying
> this.
> I need to run a BULK INSERT query where the datasource is an Excel file and
> the destination is a table in SQL 2000 Server.
> After looking at the Books on Line, I´ve tried first:
> BULK INSERT Database.dbo.Table_tbl
> FROM 'c:\files\File.xls'
> It returns an error "column 2 truncated".
> I need directions on this subject.
> Thanks
>
> --
> Manuel

You may need to specify a format file to resolve this. What kind of
data are we looking at

From: Manuel on 28 Jul 2006 11:28

Hi Steve,

What I did, was to create a list in the Excel file, named the columns as the
columns of the database table and follow your advice, specifying the column
names in the query.
It worked beautifully ;-)
Learned one more today ;-)
Thank you so much for your help.

--
Manuel

"Steve Kass" wrote:

> There is apparently a mismatch between the columns of the source and
> destination.
>
> Look at the column lists of the table and the Excel sheet:
>
> select top 1 * from iterartis.dbo.projectoItems_tbl
>
> select top 1 * from
>
> OpenRowSet(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=c:\files\_ImportSQL\testImport.xls;HDR=YES;IMEX=1'
> ,'select * from [Folha1$]'
> )
>
> You can probably solve this problem by specifying the column names
> explicitly. For example, you might have to write:
>
> insert into iterartis.dbo.projectoItems_tbl(columnname1,columnname2, ...)
> select somecolumn, othercolumn, thirdcolumn, ...
> from
> OpenRowSet(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=c:\files\_ImportSQL\testImport.xls;HDR=YES;IMEX=1'
> ,'select * from [Folha1$]'
> )
>
> SK
>
>
> Manuel wrote:
>
> >Steve,
> >
> >I´ve tried this:
> >
> >insert into iterartis.dbo.projectoItems_tbl
> >select * from OpenRowSet(
> > 'Microsoft.Jet.OLEDB.4.0',
> > 'Excel 8.0;Database=c:\files\_ImportSQL\testImport.xls;HDR=YES;IMEX=1'
> > ,'select * from [Folha1$]'
> >)
> >
> >It returned this error:
> >
> >Insert Error: Column name or number of supplied values does not match table
> >definition.
> >
> >How do I accomplish this match.
> >
> >Thanks again
> >
> >
> >
>

From: sloan on 28 Jul 2006 16:20

Another alternative.

http://www.sqlservercentral.com/columnists/sholliday/leveragingxpexcelxmlandopenxmlfordataimports.asp

"Manuel" <Manuel(a)discussions.microsoft.com> wrote in message
news:8C7D716F-05A9-4FC0-9C0C-43F49FE7EFBE(a)microsoft.com...
> Im new to this subject. As a matter of a fact, the first time Im trying
> this.
> I need to run a BULK INSERT query where the datasource is an Excel file
and
> the destination is a table in SQL 2000 Server.
> After looking at the Books on Line, Ive tried first:
> BULK INSERT Database.dbo.Table_tbl
> FROM 'c:\files\File.xls'
> It returns an error "column 2 truncated".
> I need directions on this subject.
> Thanks
>
> --
> Manuel

From: Manuel on 29 Jul 2006 05:32

Steve,

As I said in the previous post, you've anwered my question and the import
works beautifully. But I've another question.
Suppose that the first column is named ProjectID.
I want to run a query before the insert to prevent duplication. I´m using
Dreamweaver 8.0 and ColdFusion. Usually I run a query Like;

<cfquery name="q1" datasource="DatabaseName">
SELECT ProjectoID
FROM dbo.ProjectoItems_tbl
WHERE ProjectoID = '#Form.ProjectoID#'
</cfquery>
<cfif q1.recordcount GT 0>
<cfoutput>message alerting the record already exists</cfoutput>
</cfif>

How do I refer to ProjectID in the Excel spreadsheet to substitute in query
q1 '#Form.ProjectoID#'?

Thanks for your time and your help.
--
Manuel

"Steve Kass" wrote:

> There is apparently a mismatch between the columns of the source and
> destination.
>
> Look at the column lists of the table and the Excel sheet:
>
> select top 1 * from iterartis.dbo.projectoItems_tbl
>
> select top 1 * from
>
> OpenRowSet(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=c:\files\_ImportSQL\testImport.xls;HDR=YES;IMEX=1'
> ,'select * from [Folha1$]'
> )
>
> You can probably solve this problem by specifying the column names
> explicitly. For example, you might have to write:
>
> insert into iterartis.dbo.projectoItems_tbl(columnname1,columnname2, ...)
> select somecolumn, othercolumn, thirdcolumn, ...
> from
> OpenRowSet(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=c:\files\_ImportSQL\testImport.xls;HDR=YES;IMEX=1'
> ,'select * from [Folha1$]'
> )
>
> SK
>
>
> Manuel wrote:
>
> >Steve,
> >
> >I´ve tried this:
> >
> >insert into iterartis.dbo.projectoItems_tbl
> >select * from OpenRowSet(
> > 'Microsoft.Jet.OLEDB.4.0',
> > 'Excel 8.0;Database=c:\files\_ImportSQL\testImport.xls;HDR=YES;IMEX=1'
> > ,'select * from [Folha1$]'
> >)
> >
> >It returned this error:
> >
> >Insert Error: Column name or number of supplied values does not match table
> >definition.
> >
> >How do I accomplish this match.
> >
> >Thanks again
> >
> >
> >
>

From: Manuel on 29 Jul 2006 05:32

First | Prev | Next | Last
Pages: 1 2 3 4 5
Prev: Dateformat
Next: Reading .LDF