use excel to update backend SQL Table

Question

use excel to update backend SQL Table

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 1

Excellent! I'll finish putting it together this weekend and get it out to you. Thanks, Phil.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 2

Thanks all
I have been involved in a number of projects with the purpose to import "Excel data" into SQL server.
At a start I used ACE drivers and T SQL but after many nightmares I gave it up.
Now I use Excel and a small VBA script which can do the trick.
Step one. Open the Excel file (adjust the import format).
Step two. Save the file as a TAB sep textfile
Step three. Use bulk insert to import the file.
As an example I just got a crude file CVS format from a stockbroker with a 9 row header
18 fields with header names and a non standard date format. It took me 2 hours to
set up the database, create the table and write the script.
I can publish an example of a script if you are interested.
Best regards
GostaM

´

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 3

Gosta Munktell - Friday, January 20, 2017 2:00 AM
Thanks all
I have been involved in a number of projects with the purpose to import "Excel data" into SQL server.
At a start I used ACE drivers and T SQL but after many nightmares I gave it up.
Now I use Excel and a small VBA script which can do the trick.
Step one. Open the Excel file (adjust the import format).
Step two. Save the file as a TAB sep textfile
Step three. Use bulk insert to import the file.
As an example I just got a crude file CVS format from a stockbroker with a 9 row header
18 fields with header names and a non standard date format. It took me 2 hours to
set up the database, create the table and write the script.
I can publish an example of a script if you are interested.
Best regards
GostaM
Â´

Even that has problems. First, a human has to press a button to do the export instead of being able to read the file at any time. Also, if someone embeds a comma in a cell, Excel puts quotes only around that cell no matter how you export it. Like I was telling Phil, I've developed some methods around all that. It even auto-magically handles the addition of columns even if the column names are the same (or different).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 4

Jeff Moden - Thursday, January 19, 2017 9:15 PM
Excellent! I'll finish putting it together this weekend and get it out to you. Thanks, Phil.

@Phil,
As it sometimes occurs, things happen. First, based on what you've stated as some problems, I spent the time this weekend investigating those instead of writing the article. I probably won't have the final draft ready for a while because I've taken to my own full review and doing some rewriting. I will, however, keep you in mind for a review when I'm ready.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 5

"Even that has problems. First, a human has to press a button to do the export instead of being able to read the file at any time. Also, if someone embeds a comma in a cell, Excel puts quotes only around that cell no matter how you export it. Like I was telling Phil, I've developed some methods around all that. It even auto-magically handles the addition of columns even if the column names are the same (or different)."

That is true if a human is involved. In my example the file is generated from the host system and the script to import etc the file can be scheduled if wanted. Excel may not be visible for the user.
Of course the source file must be clean in production.
My intention is not to create a general method to import Excel files but to point at an alternative way to use Excel it self (VBA). You don't have to install Excel at the database server .
Excel can be installed at a client and be connected remote to the database server.
Gosta M

Phil Parkin SSC Guru Points: 246976 More actions · Answer 6

Jeff Moden - Sunday, January 22, 2017 1:15 PM
Jeff Moden - Thursday, January 19, 2017 9:15 PM
Excellent! I'll finish putting it together this weekend and get it out to you. Thanks, Phil.
@Phil,
As it sometimes occurs, things happen. First, based on what you've stated as some problems, I spent the time this weekend investigating those instead of writing the article. I probably won't have the final draft ready for a while because I've taken to my own full review and doing some rewriting. I will, however, keep you in mind for a review when I'm ready.

I'm here when you're ready, Jeff. I've been feeling a bit under the weather anyway, so perhaps it's for the best.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel