SQL insert using Excel file

Question

In my Firebird database, I have a Table_Product which stores many individual numbered tables, for example:

   TBID PRODUCT  PRICE
    1       A      10
    1       B      20
    1       C      70
    ...

And I need to make an SQL INSERT to include a new one:

      TBNID PRODUCT  PRICE
        2       D      80
        2       F      40
        2       G      30

My new table data is in Excel file.

How could be an SQL Statement to accomplish it? How can I convert the excel table so I can plug the data into the SQL statement ?

INSERT INTO Table_Product (TBNID,PRODUCT,PRICE) values ... ?

It this a one shot operation or something you are going to do several times ? — Julien R
– Julien R, Commented Aug 4, 2017 at 2:47
well in this case, you can save your excel as csv an use a command line tool like isql or fsql mujweb.cz/iprenosil/interbase/fsql.htm to insert — Julien R
– Julien R, Commented Aug 4, 2017 at 2:53
if you want to have something build into the excel file, you can do a quick vba macro which would read an area of a sheet, generate the insert, connect to the db and run inserts — Julien R
– Julien R, Commented Aug 4, 2017 at 2:55
humm, so I need to create a code to generate an insert for every row on my excel data ? so If I have 100 rows in my excel , I have to create 100 inserts , Is that correct ? — Jonathan Livingston Seagull
– Jonathan Livingston Seagull, Commented Aug 4, 2017 at 3:01

Gabong · Accepted Answer · 2017-08-04 02:52:53Z

4

=" INSERT INTO Table_Product (TBNID, PRODUCT , PRICE) values ('"&H2&"','"&I2&"','"&J2&"') ;"

just change the Cell locations then drag down.

answered Aug 4, 2017 at 2:52

Gabong

685 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jonathan Livingston Seagull Over a year ago

Hi, you mean , I need to do it in Excel first and then SQL ?

Gabong Over a year ago

Yes, that will get the value based on cell locations and it will generate the script. If u have 100 records just drag down it will auto populate. Copy+paste it in ur DB then commit the script.

Jonathan Livingston Seagull Over a year ago

I got your idea. I didn't know this trick on spreadsheets. It worked. Thanks a lot !

Collectives™ on Stack Overflow

SQL insert using Excel file

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related