Importing Selected csv data into SQL tables

Hi,

I hope you can help me.

I've got a csv that I need to import. It's about 74 columns wide and 2500 rows long. What I need to do is select the first 3 columns from the columns from the csv and put them into one table and then put every 7 columns (plus the 1st column as the primary key) into a different table.

I've not explained that very well, basically it's like this...

A B C D E F G H I J K L M N O P

1

2

3

4

5

Table 1 Data

cs# A1
fe B1
cl C1

Table 2

cs# A1
desc D1
date E1
pay F1
amount G1
vat H1
total I1
cReq J1

I'm not really sure were to start. I want to import the data nightly into the tables. I've tried to use the import wizard but I can't seem to select the data I want and exclude the data I don't.

Steve

Which version of SQL are you using? If its 2005 you should be able to do this neatly using Integration Services (SSIS).

For 2000 you might be best off dumping the whole thing into a holding table and then manipulating the data from there. This could be handled by several steps of a SQL Job.

HTH!

|||Thanks for the reply,

Yeah I'm using 2005 sp2. Where can I find the integration services? I can't seem to find it.

Steve|||You need to install SSIS(integration services) @. the time of installing sql server 2005 (you need to choose the services you need to install)........go to startall programsmicrosoft sql server 2005configuration toolssql server configuration manager.....under that you can see the list of sql 2005 services just check if SSIS is present else install it.......after installing connect to SSMS choose integration services instead of database engine to connect to SSIS..........

Thanxx
|||

Follow Deepaks instructions to get it installed if you haven't already. Then, you can open up the Business Intellignece Studio (Visual Studio skin) and select an Integration Services project. This will then give you a graphical interface in which you can drag and drop different datasources (eg Excel & SQL Server) and dataflow methods (eg copy column and export column )

Take a look at the Books Online walkthroughs that will give you a flavour of the different tasks you can achieve and hopefully that will give you a good steer on what to do.

Good luck!

|||Thanks very much.

I feel more at home in Visual Studio!

I'll probably be back when I get stuck again.

Steve

Importing from excel

Hi there, could let me know how to reference to an excel database in SQL code?

I think

Select * from OPENROWSET ('Microsoft.Jet.OleDB.4.0', 'EXCEL 8.0;Database=C:\MyExcel1.xls',Sheet1$)

Would import everything from a spreadsheet but im not sure how you can reference individual columns?Select column_name from OPENROWSET ('Microsoft.Jet.OleDB.4.0', 'EXCEL 8.0;Database=C:\MyExcel1.xls',Sheet1$)
where .....

Importing data from excel spread sheet to SQL 2005

I'm attempting to import data from an Excel spread sheet into an SQL 2005
this is what I try
Select *
Into SQLServerTable
From OPENROWSET(â'Micorsoft.jet.OLEDB.4.0â',â'Excel 5.0, Database = C:\example.xls;HDR = YESâ',â'Select * From [example])
I got error message abour Micorsoft.jet.OLEDB.4.0â'OLEDB.4.0
I also try link server option linkink excel
is there an easier way to import excel?
ThanksIn Management Studio you can right click the database name and choose Tasks
> Import Data... to open the "SQL Server Import and Export Wizard. But that
only works well for on demand import.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Kim_Quest" <KimQuest@.discussions.microsoft.com> wrote in message
news:50BC66AB-3882-4066-9777-913568D17BF5@.microsoft.com...
> I'm attempting to import data from an Excel spread sheet into an SQL 2005
> this is what I try
> Select *
> Into SQLServerTable
> From OPENROWSET('Micorsoft.jet.OLEDB.4.0','Excel 5.0, Database => C:\example.xls;HDR = YES','Select * From [example])
>
> I got error message abour Micorsoft.jet.OLEDB.4.0'OLEDB.4.0
> I also try link server option linkink excel
> is there an easier way to import excel?
> Thanks
>
>
>
>
>|||Kim,
Could it be that you are missing the final single quote after [example]?
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Kim_Quest" <KimQuest@.discussions.microsoft.com> wrote in message
news:50BC66AB-3882-4066-9777-913568D17BF5@.microsoft.com...
> I'm attempting to import data from an Excel spread sheet into an SQL 2005
> this is what I try
> Select *
> Into SQLServerTable
> From OPENROWSET('Micorsoft.jet.OLEDB.4.0','Excel 5.0, Database => C:\example.xls;HDR = YES','Select * From [example])
>
> I got error message abour Micorsoft.jet.OLEDB.4.0'OLEDB.4.0
> I also try link server option linkink excel
> is there an easier way to import excel?
> Thanks
>
>
>
>
>|||Try the IMPORT wizard , as a one off
--
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=uk/">http://www.itjobfeed.com">UK IT Jobs</a>
"Kim_Quest" <KimQuest@.discussions.microsoft.com> wrote in message
news:50BC66AB-3882-4066-9777-913568D17BF5@.microsoft.com...
> I'm attempting to import data from an Excel spread sheet into an SQL 2005
> this is what I try
> Select *
> Into SQLServerTable
> From OPENROWSET('Micorsoft.jet.OLEDB.4.0','Excel 5.0, Database => C:\example.xls;HDR = YES','Select * From [example])
>
> I got error message abour Micorsoft.jet.OLEDB.4.0'OLEDB.4.0
> I also try link server option linkink excel
> is there an easier way to import excel?
> Thanks
>
>
>
>
>

Importing data from excel

Hi all,
I use the following to import data from Xl.
SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
This creates a new table. How could I alter this statement to update an
existing table
Thanks
RobertINSERT dbo.tablename
SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
Good luck
"Robert Bravery" wrote:

> Hi all,
> I use the following to import data from Xl.
> SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
> This creates a new table. How could I alter this statement to update an
> existing table
> Thanks
> Robert
>
>|||Thanks. I must ahad something wrong
Robert
"Derekman" <Derekman@.discussions.microsoft.com> wrote in message
news:C6B20B05-F0BF-4A3E-BD17-1782B9CB1FF2@.microsoft.com...
> INSERT dbo.tablename
> SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
> Good luck
> "Robert Bravery" wrote:
>|||Robert Bravery wrote:
> Hi all,
> I use the following to import data from Xl.
> SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
> This creates a new table. How could I alter this statement to update an
> existing table
> Thanks
> Robert
>
>
Instead of using "SELECT * INTO <newtablename> FROM", use "INSERT INTO
<existingtablename> SELECT * FROM".