Read columns from excel into tables ?!

mberggren · #1 (**permalink**) 10-03-07, 02:55

Hi,

I have an excel ark. Collumns A1, B1.
A1 is listing articles and B1 is listing prices. This is a long list. products.xls

A1 B1
Article 1 50
Article 2 40
Article 3 30
etc .. etc..

In Adaptive SQL Server i have two tables.
Article (tbl_article)
Price (tbl_price)

These are linked together with an unique ID called
obj_num

I want to read my excel ark into Adaptive SQL Server and sort out article and price into the right table.

Is this possible ?

Regards
Magnus

stuarta · #2 (**permalink**) 10-04-07, 15:37

export the excel to a delimted file..
bcp the file into sybase tbl

mberggren · #3 (**permalink**) 10-04-07, 18:14

Thx stuarta,

You don´t have any example code to show me. Have read some about the bcp utility but have no idea how it works out with sybase. The procedure from exporting excel sheets to bcp and from bcp to sybase. Or any good toturials ? Pretty newbi here

..

Regards
Magnus

pdreyer · #4 (**permalink**) 10-05-07, 02:50

Quote:

Originally Posted by mberggren

These are linked together with an unique ID called obj_num

What value should be used for the insert of obj_num? An identity field?

mberggren · #5 (**permalink**) 10-05-07, 04:14

The data type for obj_num is integer

pdreyer · #6 (**permalink**) 10-05-07, 04:39

From excel Save as type Text (Tab delimited)
then e.g.

Code:

C:\tmp>isql -Usa -P -Spd -w333 -Dtempdb
1> create table t1 (c1 int,c2 char(3),c3 varchar(10))
2> go
1> exit
C:\tmp>more t1.txt
c1      c2      c3
2       x       b
3       y       c
4       z       a

C:\tmp>bcp tempdb..t1 in c:\tmp\t1.txt -Usa -P -Spd -c -F2

Starting copy...

3 rows copied.
Clock Time (ms.): total = 1  Avg = 0 (3000.00 rows per sec.)

C:\tmp>isql -Usa -P -Spd -w333 -Dtempdb
1> select * from t1
2> go
 c1          c2  c3
 ----------- --- ----------
           2 x   b
           3 y   c
           4 z   a

(3 rows affected)
1> drop table t1

pdreyer · #7 (**permalink**) 10-05-07, 05:34

You can read more about bcp in the ASE Utility Guide

mberggren · #8 (**permalink**) 10-05-07, 07:07

Thx,

See if I have time in the weekend to check bcp in the ASE Utility Guide.
It´s a bit hard to get an clue of this, little bit of an rookie in this area.

I think I have some understadning of what you wrote.

In my case I want to split up the table you create in bcp and sort out price to tbl_price and article to tbl_article when you insert in Sybase

So you write the bcp commands in CMD prompt ?

Regards
Magnus

pdreyer · #9 (**permalink**) 10-05-07, 09:57

Quote:

Originally Posted by mberggren

In my case I want to split up the table you create in bcp and sort out price to tbl_price and article to tbl_article when you insert in Sybase

You will need a format file to specify which columns should be skipped and if you e.g. want to insert column 3 from the text file into table column 2. It might be easier to just copy and paste your data into a new sheet.

Quote:

Originally Posted by mberggren

So you write the bcp commands in CMD prompt ?

Yes bcp is an executable and should be executed like any other OS command