Need feedback on my plan to import a terribly formatted Excel spreadsheet

Good morning, all,

I have an Excel workbook that needs to be imported. It has three
sheets, but it's really the first that is giving me fits. Each of the
three worksheets have header info and instructions on the first 8
rows. Worksheet 1 then has, on row 9, the column names for the group
informtion. Row 10 has the group information. Row 11 has detail
column headers. Row 12 and later have detail information. Worksheets
2 and three do not have detail information, just row 9 with the column
names for the group informtion and Row 10 with group information.

Here is how I am thinking of handling this.
Run a script, outside of SSIS to save each sheet as a CSV file to a
folder. I believe that this must be done because some of the first 8
rows are blank and according to the docs, SSIS cannot have blank rows
in imported Excel sheets.
Loop over the files in the folder.
For each file, exclude the first 8 rows.
if the file name is the first worksheet then
get the next two rows and process group info
get the rest of the worksheet and process detail information
if the file name is not the first worksheet then
get the next two rows and process group info

My questions are: Does this seem feasible? Is there an easier way to
do this? Any hints or tricks that might be helpful? Any pitfalls
that I should watch out for?

Thanks so much for any insights,
Kathryn

It sounds like you have two record types to import, group and detail. I would load the data into a staging table designed to handle both or multiple record types as a first pass. Once the data is loaded to the staging table, you can pass through the table and load each record type into separate tables and process from there.

However, your approach should work as well.

need assistance with project

I have a project that is going to use ADP as a front end and SQL server as
the database. Now I need to import flat text files to a temptable in SQL. I
been reading that I should use DTS packages but unfor, from what I have
heard, DTS can only be triggered if scheduled. I am creating a form where th
e
user will have to trigger it. Wonder if there are any other suggestions out
there. Right now, what I created is a macro in ADP that will call a batch
file on the drive that will open up an MDB database and import the files tha
t
way onto sql. Few problems I have right now
1) I have a form where the user clicks on "Locate files" and a subform
opens. It shows all the files in that particular directory. Right next to
each files there is an upload check box. If it check box is ckecked, then
files will be uploaded. There are 2 drop downs in the subform where user wil
l
have to fill in. What I need is if
the check box has been checked but either or both drop downs hasn't been
filled that an error message should pop up before procedure can execute. I a
m
tryin to have to msg box be like a list of all the files that is missing a
drop down. My message box right now only tells one file name but not the
others, if applicable.
for instance, if there are 7 files tha are to be uploaded and 2 of them i
didn't have any drop downs for, i have a message to say "file name text 1 an
d
text 8 are missing..."
2) everytime I open up that database, I get SQL server login error
Connection Failed
SQL state '28000'
Login failed for user(null). Reason, not associated with trusted SQL server
connection.
If i click on "ok" connection pops up and I have to manually enter the
infor. Can I somehow add this in a vb code so user wont' have to keep
manually typing info.
3) Currently, after the import, the MDB kills itself and you are back in the
ADP. Wonder if before the MDB kills itself, opens up an form in ADP and then
kills. THe form is a summary form that shows all the data that was just
imported.(if any other way, suggestions are welcomed).
please help"Justin" <Justin@.discussions.microsoft.com> wrote in message
news:F4E455A7-49AC-4E29-8178-9FE43071C905@.microsoft.com...
>I have a project that is going to use ADP as a front end and SQL server as
> the database. Now I need to import flat text files to a temptable in SQL.
> I
> been reading that I should use DTS packages but unfor, from what I have
> heard, DTS can only be triggered if scheduled. I am creating a form where
> the
> user will have to trigger it. Wonder if there are any other suggestions
> out
> there. Right now, what I created is a macro in ADP that will call a batch
> file on the drive that will open up an MDB database and import the files
> that
> way onto sql. Few problems I have right now
> 1) I have a form where the user clicks on "Locate files" and a subform
> opens. It shows all the files in that particular directory. Right next to
> each files there is an upload check box. If it check box is ckecked, then
> files will be uploaded. There are 2 drop downs in the subform where user
> will
> have to fill in. What I need is if
> the check box has been checked but either or both drop downs hasn't been
> filled that an error message should pop up before procedure can execute. I
> am
> tryin to have to msg box be like a list of all the files that is missing a
> drop down. My message box right now only tells one file name but not the
> others, if applicable.
> for instance, if there are 7 files tha are to be uploaded and 2 of them i
> didn't have any drop downs for, i have a message to say "file name text 1
> and
> text 8 are missing..."
> 2) everytime I open up that database, I get SQL server login error
> Connection Failed
> SQL state '28000'
> Login failed for user(null). Reason, not associated with trusted SQL
> server
> connection.
> If i click on "ok" connection pops up and I have to manually enter the
> infor. Can I somehow add this in a vb code so user wont' have to keep
> manually typing info.
> 3) Currently, after the import, the MDB kills itself and you are back in
> the
> ADP. Wonder if before the MDB kills itself, opens up an form in ADP and
> then
> kills. THe form is a summary form that shows all the data that was just
> imported.(if any other way, suggestions are welcomed).
> please help
There are a number of options for executing DTS packages. They don't have to
be scheduled and they can be run from your code or using the DTSRUN.EXE
executable. See Books Online for details of DTSRUN. See the following link
for other options:
http://www.sqldts.com/default.aspx?104
Depending on the format of your source file another possibility could be to
use BCP or BULK INSERT to load the data. Again, see BOL for details.
For the rest of your questions you might get more help in an Access group.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Need advice on a Data Import Routine

Hello,
I have a system that I am writing to automaticly import Credit Transaction
data into a SQL Server 2000 Database. I am using a VB.Net application to
detect when the file arives and prep it for parsing. The file is
aproximately 10Mb of relatively complex hierarchal data that is defined by 2
character tokens at the begining of each data line. The structure breaks ou
t
into aproximately 6 parent-child related tables with numerous lookup tables
on each primary table.
The previous aproach loaded each line of raw data into an Import table and
used a Stored Procedure to break down the information into the tables. The
Stored Procedure is large and very complex. It also has the problem that th
e
Parsing takes so long I need to implement a very long timeout on my
connection or I get errors. It is also difficult to track progress through
the Import for the user to see.
The options I see at this point are;
1. Use the current approach of loading the raw data into an Import table and
Parsing with a Stored Procedure.
2. Write the Parsing Routine in VB.NET and use ADO.NET to load the data into
the apropriate tables.
3. Use a mixed aproach of breaking the massive Stored Procedure into smaller
procedures and managing the steps with VB.Net.
I am looking for any tips and advice that the guru's out there are willing
to give. Especially a comparision of the effiency of doing ADO.NET bulk
inserts vs. Stored Procedure parsing.
Thanks for any help given.
Doug
PS - Sorry for the cross posting in dotnet.general but I am hoping to get a
balanced set of views on this issue.Doug,
I have heard that sql server is not good for parsing routines, so this can
lead you to do it in your client app.
What about using XML to represent this complex hierarchal data?
AMB
"Doug R" wrote:

> Hello,
> I have a system that I am writing to automaticly import Credit Transaction
> data into a SQL Server 2000 Database. I am using a VB.Net application to
> detect when the file arives and prep it for parsing. The file is
> aproximately 10Mb of relatively complex hierarchal data that is defined by
2
> character tokens at the begining of each data line. The structure breaks
out
> into aproximately 6 parent-child related tables with numerous lookup table
s
> on each primary table.
> The previous aproach loaded each line of raw data into an Import table and
> used a Stored Procedure to break down the information into the tables. Th
e
> Stored Procedure is large and very complex. It also has the problem that
the
> Parsing takes so long I need to implement a very long timeout on my
> connection or I get errors. It is also difficult to track progress throug
h
> the Import for the user to see.
> The options I see at this point are;
> 1. Use the current approach of loading the raw data into an Import table a
nd
> Parsing with a Stored Procedure.
> 2. Write the Parsing Routine in VB.NET and use ADO.NET to load the data in
to
> the apropriate tables.
> 3. Use a mixed aproach of breaking the massive Stored Procedure into small
er
> procedures and managing the steps with VB.Net.
> I am looking for any tips and advice that the guru's out there are willing
> to give. Especially a comparision of the effiency of doing ADO.NET bulk
> inserts vs. Stored Procedure parsing.
> Thanks for any help given.
> Doug
> PS - Sorry for the cross posting in dotnet.general but I am hoping to get
a
> balanced set of views on this issue.|||I have done the same type of thing in the recent past. The methodology I
have used is to execute a BCP statement into a properly structured table.
BCP is native SQL Server, much faster than a Bulk Insert statement, deals
with security in-line (you specify user.password in the command) and if the
data is consistent (the type of data appears in the same position in each
line) the BCP command will import into the properly formatted table without
any parsing required. The syntax for command is listed in Books On-line.
You will need to create a format file for the import to use. This is simply
executing a BCP FORMAT command against the desired SQL Server destination
table. Once the format file is built, you use that to define for the BCP IN
command the data structure you are importing. It is an extra step to create
the format file, but once you have it you place it in a location where your
process can access it will do the heavy lifting for you.
The advantages to this process are several. First, because you are
pre-defining a data structure to the import process, no additional parsing
is needed. Second, because BCP runs in a DOS environment it is very fast.
Third, because user ID and password are contained in the command line,
connection strings and other security items are unnecessary.
There is one additional caveat to using BCP in a procedure. You will have
to run it in a DOS environment using the xp_cmdshell extended procedure.
This allows SQL Server to run DOS commands from within a stored procedure.
The user ID that runs this will need privileges assigned to the
master.dbo.xp_cmdshell proc in SQL Server Enterprise Manager.
My suggestion for implementation would be to write a simple stored procedure
that builds the BCP statement as a Varchar variable and passes it to
master.dbo.xp_cmdshell to run. Call this stored procedure using the execute
nonquery option of the SQLServer.SQLCommand object in VB.Net. Doing this
will allow for full automation of the import process.
If you need any further assistance you can contact me at
groutme_alternate@.sbcglobal.net.
Good Luck:
groutme in SO Cal.
"Doug R" <DougR@.discussions.microsoft.com> wrote in message
news:D7FEFA89-E6BD-49C9-A0E5-5464130B3440@.microsoft.com...
> Hello,
> I have a system that I am writing to automaticly import Credit Transaction
> data into a SQL Server 2000 Database. I am using a VB.Net application to
> detect when the file arives and prep it for parsing. The file is
> aproximately 10Mb of relatively complex hierarchal data that is defined by
2
> character tokens at the begining of each data line. The structure breaks
out
> into aproximately 6 parent-child related tables with numerous lookup
tables
> on each primary table.
> The previous aproach loaded each line of raw data into an Import table and
> used a Stored Procedure to break down the information into the tables.
The
> Stored Procedure is large and very complex. It also has the problem that
the
> Parsing takes so long I need to implement a very long timeout on my
> connection or I get errors. It is also difficult to track progress
through
> the Import for the user to see.
> The options I see at this point are;
> 1. Use the current approach of loading the raw data into an Import table
and
> Parsing with a Stored Procedure.
> 2. Write the Parsing Routine in VB.NET and use ADO.NET to load the data
into
> the apropriate tables.
> 3. Use a mixed aproach of breaking the massive Stored Procedure into
smaller
> procedures and managing the steps with VB.Net.
> I am looking for any tips and advice that the guru's out there are willing
> to give. Especially a comparision of the effiency of doing ADO.NET bulk
> inserts vs. Stored Procedure parsing.
> Thanks for any help given.
> Doug
> PS - Sorry for the cross posting in dotnet.general but I am hoping to get
a
> balanced set of views on this issue.|||Thanks for the reply. Regretably I have no control over the form of the
input data or I would be using XML. The data is being sent to us from an
outside business.
Are you familiar with ADO.NET for doing bulk inserts? I would be very
interested in hearing the Pros and Cons of Stored Proc vs ADO.NET.
An alternative would possibly be something that parses the import data file
into XML before the insert. Any ideas on how this could be done efficiently
?
"Alejandro Mesa" wrote:
> Doug,
> I have heard that sql server is not good for parsing routines, so this can
> lead you to do it in your client app.
> What about using XML to represent this complex hierarchal data?
>
> AMB
> "Doug R" wrote:
>|||If your stored procedure uses cursors or some other method to processes the
data line-by-line then probably you can rewrite some or all of it as
set-based SQL code for a significant performance improvement.

> Especially a comparision of the effiency of doing ADO.NET bulk
> inserts vs. Stored Procedure parsing
Bulk Insert is equally possible from TSQL or ADO.NET code so this doesn't
seem to me like a meaningful comparison. The real question is whether TSQL o
r
client code can transform the data efficiently. Since this is fundamentally
about data manipulation my money would be on TSQL ... but that's without
seeing the detail of the actual problem.
David Portas
SQL Server MVP
--|||Doug,
If you are subcribed to SQL Server Magazine, there is a good article about
this theme.
Bulk-Insert Options for ADO.NET
http://www.windowsitpro.com/Article...>
oad_7pv0.asp
ADO.NET 2.0 will include this feature.
http://www.windowsitpro.com/SQLServ...62.html

AMB
"Doug R" wrote:
> Thanks for the reply. Regretably I have no control over the form of the
> input data or I would be using XML. The data is being sent to us from an
> outside business.
> Are you familiar with ADO.NET for doing bulk inserts? I would be very
> interested in hearing the Pros and Cons of Stored Proc vs ADO.NET.
> An alternative would possibly be something that parses the import data fil
e
> into XML before the insert. Any ideas on how this could be done efficient
ly?
>
> "Alejandro Mesa" wrote:
>