Extract data from 350 seperate Excel Files

We have used a template for 350 excel files and now we are trying to
extract certain information from these files to either one excel file
or to an access database. The problem is that in this template the rows
are not necessarily the same in each file. (E.G. If a company started
in 1995 the corresponding rows and columns for the 2000 data will be
different than a company that started in 1999.) I also would like to
change the column headings to rows and the rows into heading columns. I
know its a big task and I am not so sure how to begin. Any thoughts
would be appreciated.<acaseutk@.gmail.com> wrote in message
news:1142284202.900306.197310@.i40g2000cwc.googlegroups.com...
> We have used a template for 350 excel files and now we are trying to
> extract certain information from these files to either one excel file
> or to an access database. The problem is that in this template the rows
> are not necessarily the same in each file. (E.G. If a company started
> in 1995 the corresponding rows and columns for the 2000 data will be
> different than a company that started in 1999.) I also would like to
> change the column headings to rows and the rows into heading columns. I
> know its a big task and I am not so sure how to begin. Any thoughts
> would be appreciated.
I sympathise with you. I seem to have spent much of my career trying to
educate accountants that a spreasheet is a totally lousy way to store data.
My suggestion is that you use Excel macros or cut and paste to get the data
as straight as you can first. Then try saving the files in delimited form
(again you can automate with macros) and import from the intermediate format
to some staging tables. Then you have LOTS of validation and transformation
to do.
You can try DTS or Integration Services straight from the Excel sheets but
in my experience this rarely works in your situation. Each file will have
different formatting, column widths, heading, etc and DTS will choke again
and again unless you are lucky. I can't say I've tried it with IS though -
maybe some things have improved.
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
--|||How did you know I was an accountant...Haha. Thanks for the suggestion
I will look into it.

External Scheduler Integration

Anyone have a simple script that might be used by an external scheduler
such as Control-M to kick off .sql files and return back appropriate
return codes and error messages.
SpencerHi
Check out the osql utilitiy in books online and use the -b option to return
a value in %ERRORLEVEL%
John
"stabbert" wrote:
> Anyone have a simple script that might be used by an external scheduler
> such as Control-M to kick off .sql files and return back appropriate
> return codes and error messages.
> Spencer
>

External Scheduler Integration

Anyone have a simple script that might be used by an external scheduler
such as Control-M to kick off .sql files and return back appropriate
return codes and error messages.
SpencerHi
Check out the osql utilitiy in books online and use the -b option to return
a value in %ERRORLEVEL%
John
"stabbert" wrote:

> Anyone have a simple script that might be used by an external scheduler
> such as Control-M to kick off .sql files and return back appropriate
> return codes and error messages.
> Spencer
>

Extended Stored Procedures -> loading linked files

Hello everybody

I actually wrote a stored procedure (in xp_wrapper.dll) that is using a dll (original.dll) which uses a license file (no file extension)... clear? :)

Anyway.

All the required files are placed in the BINN dir of the server.

The problem is now, that original.dll can't find it's license file. It seems, that this file was not load by SQL Server.

How can I load this file into SQL Server's heap?

Yours
MikeYou need to checkout the Win32 level LoadLibrary() call to explicitly load the DLL. You will need to explicitly FreeLibrary the DLL before you exit from the xp call.

Extended Stored Procedures

Hi !
Please advise which libs and include files are required
to compile extended stored procedure file for SQL Server 2000
in VC++.
Thanks in advance:From SQL 2000 BOL under "extended stored procedures, creating":
To create an extended stored procedure DLL by using Microsoft Visual C++
Create a new project of type Win32 Dynamic Link Library.
Set the directory for include files and library files to C:\Program
Files\Microsoft SQL Server\80\Tools\DevTools\Include and C:\Program
Files\Microsoft SQL Server\80\Tools\DevTools\Lib, respectively.
On the Tools menu, click Options.
In the Options dialog box, click the Directories tab and set the directory
for include files and library files.
On the Project menu, click Settings.
In the Project Settings dialog box, click the Link tab. Click the General
category, and then add opends60.lib to object/library modules.
Add source files (.c, .cpp, and .rc files, and so on) to your project.
Compile and link your project.
Regards
Mike
"IMRAN SAROIA" wrote:

> Hi !
> Please advise which libs and include files are required
> to compile extended stored procedure file for SQL Server 2000
> in VC++.
> Thanks in advance:
>
>

Extended Stored Procedure

Good morning,
We are porting a legacy VB6 user interface application that stores data in
binary text files to SQL Server 2000 and C#.
The VB6 user interface hooks into backend C/C++ dll's to pass VB6 "Type"
data into the C code that writes to the binary files.
We have to preserve this strategy of writing data to binary files because a
massive C dll library uses them to analyze the data.
What I would like to do is store the data in a normalized SQL database and
then use Extended Stored Procedures (ESP) to hook into the C functions that
write the binary files.
This would require selecting a row of data from a SQL table and then passing
it to the ESP C function in a way that mimics the VB6 Type datatype. Really
the C is looking for a pointer to the beginning of the Type data.
Any thoughts/advice greatly appreciated!
Thank you
jmattSQL Server is not an application development tool. Processing data one row
at a time and calling C functions would be best implemented on the
application side rather than from the database.
"jmatt" <jmatt@.discussions.microsoft.com> wrote in message
news:8E82603C-AA1F-462A-AEA4-7EDC103E5F45@.microsoft.com...
> Good morning,
> We are porting a legacy VB6 user interface application that stores data in
> binary text files to SQL Server 2000 and C#.
> The VB6 user interface hooks into backend C/C++ dll's to pass VB6 "Type"
> data into the C code that writes to the binary files.
> We have to preserve this strategy of writing data to binary files because
> a
> massive C dll library uses them to analyze the data.
> What I would like to do is store the data in a normalized SQL database and
> then use Extended Stored Procedures (ESP) to hook into the C functions
> that
> write the binary files.
> This would require selecting a row of data from a SQL table and then
> passing
> it to the ESP C function in a way that mimics the VB6 Type datatype.
> Really
> the C is looking for a pointer to the beginning of the Type data.
> Any thoughts/advice greatly appreciated!
>
> Thank you
> jmatt|||I understand your point. It just seems so direct and efficient to go from
table to file.
Thanks!
jmatt
"JT" wrote:

> SQL Server is not an application development tool. Processing data one row
> at a time and calling C functions would be best implemented on the
> application side rather than from the database.
> "jmatt" <jmatt@.discussions.microsoft.com> wrote in message
> news:8E82603C-AA1F-462A-AEA4-7EDC103E5F45@.microsoft.com...
>
>|||> SQL Server is not an application development tool.
Not sure I totally agree with that. SQL2005 is great application server
environment. The lines are not dark any more, they are shades of grey.
This kind of thing would be ~easy to do in sql2005 clr.
William|||William,
Any ideas on how I would pass data to the C function as a parameter in ESP
so that it would mimic VB6 Type data? Would the result of a simple SELECT b
e
a start?
jmatt
"William Stacey [MVP]" wrote:

> Not sure I totally agree with that. SQL2005 is great application server
> environment. The lines are not dark any more, they are shades of grey.
> This kind of thing would be ~easy to do in sql2005 clr.
> --
> William
>
>|||You have SQL2005? If not, then I can't help.
If so, I would rewrite the function in C# and just call from a SqlUDF then
you can use the power of .Net and IO classes.
You could probably also call c dll from SqlUDF like you would call a win32
function, by defining it first.
William Stacey [MVP]
"jmatt" <jmatt@.discussions.microsoft.com> wrote in message
news:371399B9-B337-4F6F-AECD-89CFBB626DA0@.microsoft.com...
> William,
> Any ideas on how I would pass data to the C function as a parameter in ESP
> so that it would mimic VB6 Type data? Would the result of a simple SELECT
> be
> a start?
> jmatt
> "William Stacey [MVP]" wrote:
>

Exporting to multiple files from a single source table using DTS

Hi Friends

I have been trying to solve this problem for the last 2 days but no luck.

Here is the problem that I am facing.

The task on had is to transfer data from a single table (the source) to multiple files (Destination) based on the record type.

I have tried to changing the Datasource property of the Text File Connection object dynamically by using an ActiveX Script. But the data is still being written only to one file.

Can anyone please help me.

Thanks in advance.

Srinivas.Can't you split up your export by using views with restrictions on your recordtype instead of your table?|||manthenasri, does DTS allow you to redirect output on the fly? From what I have seen the source and destination is fixed at design time.

Have you tried BCP with a query? You could use DTS to script your BCP commands to a CMD file. Then at the cmd prompt you could use DTSRUN to build the CMD file and then execute the CMD file. Or just set up a procedure to script the BCP commands and use xp_cmdshell.

Sort of bassackwards but it's the best I could come up with.|||Hello Paul

I do not know much about BCP. But here is the problem that i have to solve.

I have a table by name T1 which has the following data

Business Unit Name SSN RATE
1 Joe 923456789 40
1 Jenny 568234569 50
3 Mike 234198634 35
3 Meri 743579374 45

Now i need to create a two files by name 1.ben and 3.ben on the fly.
and them put in the data related to Joe and Jenny into 1.ben and the data for Mike and Meri into 3.ben. If there is more data related to other business units i need to create those files also.

Can you please tell me how i can accomplish using BCP.

Thanks,
Srinivas.|||I think the BCP command you want is...

bcp "select * from <dbname>.<owner>.<table> where <where clause>" queryout <file name> -c -T -S <server>

probably could wrap it all up like this:
create table manthenasri([Business Unit] int, [Name] varchar(15), SSN varchar(9), Rate int)
insert into manthenasri values(1,'Joe','923456789',40)
insert into manthenasri values(1,'Jenny','568234569',50)
insert into manthenasri values(3,'Mike','234198634',35)
insert into manthenasri values(3,'Meri','743579374',45)

declare @.bu int, @.TSQL varchar(255)
select @.bu = min([Business Unit]) from manthenasri
while @.bu is not null begin
set @.TSQL = 'bcp "select * from ' + db_name() + '..' + 'manthenasri where [Business Unit] = ' + cast(@.bu as varchar) + '" queryout ' + cast(@.bu as varchar) + '.ben -c -T -S ' + @.@.servername
print @.TSQL
-- master.dbo.xp_cmdshell(@.TSQL)
select @.bu = min([Business Unit]) from manthenasri where [Business Unit] > @.bu
end

Open Query Analyzer and run the above code. It should produce a few BCP commands. Make adjustments for your table and rerun the script. Next try cutting and pasting the results to a CMD prompt and see how it works. Once that's done you can un-comment the xp_cmdshell call and try running this on your server.

THIS IS UNTESTED CODE!

Look this over and post back with questions.|||Hi Srinivas ,

Use DTS designer add one data pump task for each condition and output file.

For example : Use select statement as the source of data pump task with where clause and desination to the the disired out put file.

cheers :)

Shaji

Originally posted by manthenasri
Hi Friends

I have been trying to solve this problem for the last 2 days but no luck.

Here is the problem that I am facing.

The task on had is to transfer data from a single table (the source) to multiple files (Destination) based on the record type.

I have tried to changing the Datasource property of the Text File Connection object dynamically by using an ActiveX Script. But the data is still being written only to one file.

Can anyone please help me.

Thanks in advance.

Srinivas.|||Hai Paul


Thank you for the BCP solution that you have given. I really appreciate you patience in giving me a sample program also.

The sample worked just fine except for the brackets enclosing the variable @.Tsql. I think these brackets should not be there. I had to remove these brackets.

But now have a problem of reading from multiple files and posting that data into table. I have to do an isert and also an update on the table. How can i do that?

Thank you very much once again.

Srinivas.

Exporting text files

Hi,
Is there any way to export .rpt files as text files. I am using crystal reports 9.0 and .Net framework..
I want to export them dynamically in the code.
I could able to export to pdf,doc and html..But not to text.
Thanks,
RameshYou have to include the right dlls in order for the export to work. I use Crystal Reports Version 8.5 and there's a list of the required files in a file called runtime.hlp. (The file may be called something different with other versions of Crystal)

There are 2 types of dlls to include when you want to export: Export Destinations (Application, Disk, MAPI, Lotus Domino, etc... ) and Export Formats (HTML, pdf, doc, text, etc... ). In order to be able to export to text, I had to include U2FTEXT.DLL (from the Export Formats, this dll exports to text format) and U2DDISK.DLL (from the Export Destinations, this dll exports to disk instead of application). I couldn't get it to work if I just included the file to output to application.

Just remember that this is for CR 8.5 and VB 6, so you may or may not be able to use it, but I hope it helps anyway!|||I want to export multiple crystal reports in one file.. i.e. only one text file / pdf file or any other format... in short i want to merge the reports.
can anyone help me in that.. ?|||Hi! i too face the same prob but the differance if i want to export multipal subreport with main report in one pdf file.
Any one who can help me out plz plz do reply its urgent.
Thanks in advance|||aj_patil: When you export the main report, the subreports will be exported with it. No need to do anything else.

dhmshah: Perhaps you can set each of your reports as subreports in one main report. Other than that, I'm not sure if multiple reports can be exported into 1 file.

Also, if you have a new question, you should post it in a new thread instead of onto an old thread. That way, other people who have the same question as you can find the answer easier.|||aj_patil: When you export the main report, the subreports will be exported with it. No need to do anything else.

dhmshah: Perhaps you can set each of your reports as subreports in one main report. Other than that, I'm not sure if multiple reports can be exported into 1 file.

Also, if you have a new question, you should post it in a new thread instead of onto an old thread. That way, other people who have the same question as you can find the answer easier.

Thanks for the advice but i hv tried, any way if u get any link hear after do write to me.|||I want to export multiple crystal reports in one file.. i.e. only one text file / pdf file or any other format... in short i want to merge the reports.
can anyone help me in that.. ?

thanks for the advice. but if u get any infomation related do write- aj

Exporting text file and populate it as table using SQL

Hi,
I have a problem, I have some text files in the server. I have to export that file and read it line by line and then cut it into fields and populate it as a table in SQl with SQL commnads.

Could you anybody help mw with some hints, any relevent readings etc..
How can i use sql framework for thisIf you have control over how the text files can look like, I would recommend using the FOR XML and XML Shredding mechanisms (OpenXML, nodes() method).

Otherwise in SQL 2005, I would look into CLR user-defined functions to write the parsing code in your fav .Net language.

In SQL 2000, you would have to do it in TSQL or the mid-tier.

Also, if the data is not yet in the database but in a file, you can look into OpenRowset(BULK) in SQL Server 2005. Otherwise you need to read it in the mid-tier...

Exporting Stored Procedures to a file

I have a need to export all of the stored procedures in a database to files on the server dirve. I know that this can be done through the management interface but I need a way to do it programatically. I need to have a script or stored proc that dumps all of the procedures to a defined location on disk. Does anyone know how this can be done?

Thanks!

You can use SQL-DMO (in SQL Server 2000) or SMO (in SQL Server 2005) to do this. Please take a look at the setup CD it contains sample solutions that you can use to familiarize with these APIs. You can also search in Books Online for samples.|||

There is an article with a stored procedure that does this at http://www.databasejournal.com/features/mssql/article.php/2205291

Also a slightly different approach to the same problem at http://www.databasejournal.com/features/mssql/article.php/3401081, though the first way is probably the one you want to use.

Exporting SQL DB files

i have a VB project utilising SQL server Express. I need to export the database files so that the user data can be copied to a laptop and made available to other users.

My problem is this, connections are remaining open to the DB files even though I close all forms that include data access. Typically, my application takes around 6 minutes before the connections are released and the files can be copied.

Is there a way of either forcing all connections to close or of exporting the DB while it has connections?

Rich

Have you tried closing the connection on closing the form

Dim instance As DbConnection

instance.CloseSorry if the syntax is wrong I'm a C++ man. Same principles though.|||

In addition to what he has said about closing connections (a question might be if your access layer is caching connections, like connection pooling) you can force everyone out by using:

alter database <dbName>
set single_user with rolback immediate

Now, you have to be really careful that you want to do this, because if this is actually a database that someone else has access to they might be in the middle of doing something and this will really harsh their database experience :)

|||

I think this is exactly what's happenning, however, I am confused by your code - VS dosn't recognise it at all!

Cheers,

Rich