How can I import CSV or Excel data into SQL2005 Express ?

How can I import CSV or Excel data into SQL2005 Express ?
Thanks.
You can run a query using BULK INSERT to import a CSV file:
BULK INSERT MyTable
FROM 'c:\data.csv'
WITH (FIELDTERMINATOR = ',',
ROWTERMINATOR ='\n')
And one way to import Excel sheet:
INSERT INTO MyTable
SELECT *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\Test.xls;Extended Properties="Excel
8.0;IMEX=1"')...[Sheet1$];
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||I would only use this as a last resort - but depending on the spreadsheet
size , you can copy and paste via the Management Studio - assuming you have
the tables properly defined/mapped
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"Yips" <alvin@.yipschemical.com> wrote in message
news:OnpgVgGaIHA.4696@.TK2MSFTNGP05.phx.gbl...
> How can I import CSV or Excel data into SQL2005 Express ?
> Thanks.
>
|||It works. Thanks.
"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:Psydnci4PfWI4zTaRVnyvwA@.bt.com...
>I would only use this as a last resort - but depending on the spreadsheet
>size , you can copy and paste via the Management Studio - assuming you have
>the tables properly defined/mapped
> --
> Jack Vamvas
> ___________________________________
> Search IT jobs from multiple sources- http://www.ITjobfeed.com
>
>
> "Yips" <alvin@.yipschemical.com> wrote in message
> news:OnpgVgGaIHA.4696@.TK2MSFTNGP05.phx.gbl...
>

How can I import CSV or Excel data into SQL2005 Express ?

How can I import CSV or Excel data into SQL2005 Express ?
Thanks.You can run a query using BULK INSERT to import a CSV file:
BULK INSERT MyTable
FROM 'c:\data.csv'
WITH (FIELDTERMINATOR = ',',
ROWTERMINATOR ='\n')
And one way to import Excel sheet:
INSERT INTO MyTable
SELECT *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\Test.xls;Extended Properties="Excel
8.0;IMEX=1"')...[Sheet1$];
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||I would only use this as a last resort - but depending on the spreadsheet
size , you can copy and paste via the Management Studio - assuming you have
the tables properly defined/mapped
--
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"Yips" <alvin@.yipschemical.com> wrote in message
news:OnpgVgGaIHA.4696@.TK2MSFTNGP05.phx.gbl...
> How can I import CSV or Excel data into SQL2005 Express ?
> Thanks.
>|||It works. Thanks.
"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:Psydnci4PfWI4zTaRVnyvwA@.bt.com...
>I would only use this as a last resort - but depending on the spreadsheet
>size , you can copy and paste via the Management Studio - assuming you have
>the tables properly defined/mapped
> --
> Jack Vamvas
> ___________________________________
> Search IT jobs from multiple sources- http://www.ITjobfeed.com
>
>
> "Yips" <alvin@.yipschemical.com> wrote in message
> news:OnpgVgGaIHA.4696@.TK2MSFTNGP05.phx.gbl...
>> How can I import CSV or Excel data into SQL2005 Express ?
>> Thanks.
>

how can i import .csv file into database using SQL statement.

i have data with format in .CSV how i can import to databse using SQL statement. and how?
i can import data by change .CSV to .XLS and import .XLS file, but i hvae copy data from .CSv to .XLS it is not good ,so can you show me how to import .CSV file.
i am using SQL2005
thanks for your help

You could use OPENROWSET function with BULK option:

SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt',
FORMATFILE = 'c:\test\values.fmt') AS a;

About OPENROWSET: http://msdn2.microsoft.com/en-us/library/ms190312.aspx

About format files: http://msdn2.microsoft.com/en-us/library/ms175915.aspx

how can I get the row number(error row) while constraint failure at the OLE DB destination?

my project is insert data to OLE DB destination from csv file.

my question is

how can I get the row number(error row) while constraint failure at the OLE DB destination?

thanks.

You can't. There is no concept of row numbers in the SSIS pipeline (for good reasons).

What you CAN do is divert the erroring rows elsewhere for examination later.

-Jamie

|||

thank you.

it saves me a lot of time.

|||What good reasons ?

how can I get the row number(error row) while constraint failure at the OLE DB destination?

my project is insert data to OLE DB destination from csv file.

my question is

how can I get the row number(error row) while constraint failure at the OLE DB destination?

thanks.

You can't. There is no concept of row numbers in the SSIS pipeline (for good reasons).

What you CAN do is divert the erroring rows elsewhere for examination later.

-Jamie

|||

thank you.

it saves me a lot of time.

|||What good reasons ?

how can I get the error message?

I want to insert csv file into table.

In Control Flow Tab I insert a Data Flow Task and a Script Task

The Script Task is under the Data Flow Task as the failure handle to generate Log.

but I don't konw how to get the Data Flow Task's error message in Script Task?

thank you for you answer!!

Try moving the script task into an OnError eventhandler. The error message is available in there.

Let me know if this creates a problem.

-Jamie

|||

thank you for the answer.

I moved the script task to event handler tag. but it didn't run though my deta flow task generated a primary key error.

also I would like to know wether it is the err.description from that property I can konw the error message?