Need to import Excel data into MSDE

  • Thread starter Thread starter Steve Klett
  • Start date Start date
S

Steve Klett

(sorry, sent too soon)

Hi-

OK, I'm very new to data conversion, ado.net, etc.

What I need to do is read a HUGE excel file that has a bunch of data(about
20 columns X 1000 rows) and insert it into a yet defined MSDE database.

I want to structure the database so that it is fairly relational, as clean
as possible for what the project is worth, etc. I can handle this part,
what I'm facing right now is:

1) the beast method to execute the actual insert calls. Should I use a
DataSet, load it up with everything, then update it to the database? The
database is local, so speed is not a huge concern and this will be ran 1
time

2) How to relate the xls columns and data with the appropriate MSDE tables
and columns. The method that comes to mind is to write a very long SQL
string or SPROC, then feed it about 20 parameters. This seems like it could
get ugly.

3) Let's say that I have an Excel column named "ReasonForReturn" where the
office staff has entered any numbers of text descriptions for each row, some
repeat, some are slightly different some very different. Now, I would like
to store these reasons in a table and then (I don't know the term) but use
their FK in the main table to JOIN them together later. Easy. Except, as I
encounter a new reason in the Excel file, I need to query the DB to see if
it exists, if not, I need to insert it, get the Id, then do the main Insert
utilizing the FK for this new reason. CONFUSING and UGLY. Is there some
new amazing ado.net class that can assist with this?

4) As off right now I'm planning on looping through the DataRows in my
DataTable of Excel data. Along the way I would do whatever I needed to get
the data into the MSDE store. Is this a good approach? Is there some new
class that assists with importing or syncing in batch?





As you can tell, I'm a newbie. I can do this, it's just a matter of
utilizing ADO the best way possible... which as of this writing, I don't
know how to do. If you have any pointers, please share.



Thanks in advance,

Steve
 
(sorry, sent too soon)

Hi-

OK, I'm very new to data conversion, ado.net, etc.

What I need to do is read a HUGE excel file that has a bunch of data(about
20 columns X 1000 rows) and insert it into a yet defined MSDE database.

I want to structure the database so that it is fairly relational, as clean
as possible for what the project is worth, etc. I can handle this part,
what I'm facing right now is:

1) the beast method to execute the actual insert calls. Should I use a
DataSet, load it up with everything, then update it to the database? The
database is local, so speed is not a huge concern and this will be ran 1
time

2) How to relate the xls columns and data with the appropriate MSDE tables
and columns. The method that comes to mind is to write a very long SQL
string or SPROC, then feed it about 20 parameters. This seems like it could
get ugly.

3) Let's say that I have an Excel column named "ReasonForReturn" where the
office staff has entered any numbers of text descriptions for each row, some
repeat, some are slightly different some very different. Now, I would like
to store these reasons in a table and then (I don't know the term) but use
their FK in the main table to JOIN them together later. Easy. Except, as I
encounter a new reason in the Excel file, I need to query the DB to see if
it exists, if not, I need to insert it, get the Id, then do the main Insert
utilizing the FK for this new reason. CONFUSING and UGLY. Is there some
new amazing ado.net class that can assist with this?

4) As off right now I'm planning on looping through the DataRows in my
DataTable of Excel data. Along the way I would do whatever I needed to get
the data into the MSDE store. Is this a good approach? Is there some new
class that assists with importing or syncing in batch?





As you can tell, I'm a newbie. I can do this, it's just a matter of
utilizing ADO the best way possible... which as of this writing, I don't
know how to do. If you have any pointers, please share.



Thanks in advance,

Steve
Click to expand...
The full version of sql server has a toolset called dts (data
transformation service). This is designed for this sort of task. I'm
not familiar with MSDE but I think it doesn't include this tool. Is
there a possibility of using full sql server? Failing that maybe there
is a way to use dts with msde?

If MS-Access is available that might be a good staging option.

As a third option I'd save the sheet as csv and then using a decent
text editor[1] I'd turn it into a long series of insert statements
that can be run as a script from a command line tool. I suspect this
would be quicker to knock together, as well as quicker to run. If this
is not a one-off, but will be a regular task, then the last approach
might not be the best.

Personally I like Ultraedit, which has powerfull search/replace and a
macros. Programmers file editor is free and also good. UE does syntax
highlighting as well tho'.
 
Hi Steve

I would be tempted to:
1. Dump the spreadsheet as a CSV file.
2. Write a program to load each line into a large 2 dimension array. An
array of 1000x20 is not too fearsome.
3. Write various "data scrubbing" routines to standardise about 95% of the
bad data entry
4. Go back to the spreadsheet and manually correct the last 5% of problems.
5. Dump the array out to a text file as INSERT INTO statements.

This is the way I have done several conversions. Usually it is important
that the process can be run at least twice - once for providing test data;
once for converting the production data when your system goes live.

Regards

Ron
 
Thanks both for the suggestions.
Where would I execute this text file of INSERTs? Would it just be a Sql
script? (I'm new)

Thanks!
 
Yes it would be an SQL script. Just open a Query Analyser window and use
file open, or use osql.exe from a command line to execute the file (I think
it is the -i option to read commands from a file).

Regards

Ron
 
Sorry Steve, but to do any real work you are going to need a reasonable SQL
submission tool. You could write your own, but I suggest you look for some
open source tool, or convince your boss to spend some money.

You NEED a tool that will allow you to inspect what you have in your
database.

If you have SQL Server running anywhere within your system, it will have
client tools that can attach to an MSDE instance.

Regards

Ron
 
in addition
i would suggest adding an extra collumn to your tables where you save the
insert date (i have extra cols for edit date and the users that insert and
edit 2), this way you can correct if there are problems (at least you will
know when the problem occurred and what could be affected).

if you dont have a query analyzer you can create the insert statements as
strings in your application and execute them directly, or even better create
a stored proc for the inserts and call that.

eric

btw depending on w version of visual studio you have (if you have it of
cource) you can do a lot of things using the server explorer in there.
 
I agree Ron. I'm doing this project as a favor for my Dad, otherwise I
would hit them up for some $$.
VS.NET has some decent design tools, but I do need a Enterprise Manager. I
never understood while MS never sold this by itself.

Either way, your suggestions have been helpful, thank you!
 
Back
Top