Importing multiple Excel Files

I have a client who is sending me 800+ excel files each month with sales data. Each of the files is identical in structure, but has sales data for different stores. I receive all these files at the same time.

Is there a method with Data Transformation Services where I can have it work off of all the files in a given directory. I can set up DTS to work off of specific Excel files with no problem, but what I would like to do is set up a DTS so it could pull from each of the 800+ files.

Is this possible, or do I need to look at a solution outside of SQL to consolidate the Excel files first?

The Excel file would have columns similar to the following: store_id, zip_code, sales, transactions.There are lots of different ways to handle this kind of problem.

By far my largest concern with 800+ Excel files is GIGO (Garbage In, Garbage Out). Even one sheet of undetected junque data, and you can't get a correct answer! Before I can even suggest a technical solution, I need to understand what the risks of bad data are for you, and how much manpower you are willing to commit to either detecting or correcting the bad data... That answer will strongly influence what I suggest for a solution.

-PatP|||In terms of the quality of the data, it comes directly from my client, so as long as what I have stored in our database matches what they gave me, I am in good shape. I run some additional reports from our application to spot anomalies in the data quality.

In terms of confidence in the file structure integrity: the client isn't very sophisticated (i.e. they cannot generate one file with all the stores in it), but their result file is very simple and routine. Last month was the first time I had gone through this process with them, and all 843 files were clean.

In terms of manpower... I'm a one man technical staff for a company with 50--70 employees, and several clients, and the database management / design is only one part of my responsibilities. Considering I would be doing this particular task once a month, 30 min - 45 min of effort would hopefully be the max I would have to spend.

Having done manipulation of customer / client files for several years now, I know to expect the unexpected; and GIGO is always one of my biggest concerns. Having no support staff to help, limits the effort I can make, but I need to know that what I received is what I loaded. If something cannot be loaded, identifying that would be great.

I don't mind loading the data into a staging area... and then doing some post-load validations in SQL before moving the data to the intended tables in the database. From my perspective, the key is getting it out of the Excel format, which is terribly unfriendly for me to validate against, into a format / structure that I can.

Does that help guide you?|||I am not sure about DTS, because scripting is my preference, so there's gotta be someone that would come up with a DTS solution (I've seen a couple of samples on the net that demonstrate ennumeration of files in a directory from a DTS/SSIS package).

But in scripting world you can have a batch file that would dump a list of files into a file queue table, and then extract a script-like output into a TSQL script that you can run in the next step with osql/sqlcmd.

When I had to deal with Excel files in the past, I ended up building linked servers on the fly for each file in order to be able to handle everything in TSQL (plenty of references on the net as to how to create a linked server to an excel spreadsheet).|||You can do this in DTS by defining a filename variable and using VB to cycle through the files. So what you do depends upon whether your comfort zone is in VB or in scripting.

Importing from XML - Base64 to image

I need to exchange image data from a SQL Server database with a client using
XML. The client will also be sending me image data (converted to base 64) in
an XML document.
The export has been made very easy using the FOR XML, BINARY BASE64 clause.
However I'm having trouble with the import as I cannot seem to find a
comparable method to import the data.
The data that will be imported will contain other elements in the XML
document and can be very large files.
I was having great success using the SQLXMLBulkLoad object in SQLXML to move
large XML files directly into the database. Unfortunately, I can't figure ou
t
how to use this tp import the base64 data and convert it into an image file
to put in the database.
Any clues how this can be done?
Thanks in advance for your help.Any help? Thanks.
"shawn" wrote:

> I need to exchange image data from a SQL Server database with a client usi
ng
> XML. The client will also be sending me image data (converted to base 64)
in
> an XML document.
> The export has been made very easy using the FOR XML, BINARY BASE64 claus
e.
> However I'm having trouble with the import as I cannot seem to find a
> comparable method to import the data.
> The data that will be imported will contain other elements in the XML
> document and can be very large files.
> I was having great success using the SQLXMLBulkLoad object in SQLXML to mo
ve
> large XML files directly into the database. Unfortunately, I can't figure
out
> how to use this tp import the base64 data and convert it into an image fil
e
> to put in the database.
> Any clues how this can be done?
> Thanks in advance for your help.|||To anyone who may be interested. I finally figured this out. D'oh! It's much
easier than it first appeared to me.
You can do it through an xsd mapping file the syntax will look something
like this for the base64 encoded element:
<xsd:element name="TifFileNode" type="xsd:base64Binary"
sql:field="TiffFileColumn" sql:datatype="image" />
"shawn" wrote:

> I need to exchange image data from a SQL Server database with a client usi
ng
> XML. The client will also be sending me image data (converted to base 64)
in
> an XML document.
> The export has been made very easy using the FOR XML, BINARY BASE64 claus
e.
> However I'm having trouble with the import as I cannot seem to find a
> comparable method to import the data.
> The data that will be imported will contain other elements in the XML
> document and can be very large files.
> I was having great success using the SQLXMLBulkLoad object in SQLXML to mo
ve
> large XML files directly into the database. Unfortunately, I can't figure
out
> how to use this tp import the base64 data and convert it into an image fil
e
> to put in the database.
> Any clues how this can be done?
> Thanks in advance for your help.

importing data to MSDE

Hello,
What is the best way to import data to a client's MSDE
database from our website? We want our client to log onto
our website so we can update a table on the client's PC. I
need a little direction on this, not sure which is the best
way to go.
Thank you,
Randers
hi Randers,
Randers wrote:
> Hello,
> What is the best way to import data to a client's MSDE
> database from our website? We want our client to log onto
> our website so we can update a table on the client's PC. I
> need a little direction on this, not sure which is the best
> way to go.
> Thank you,
> Randers
perhaps you could provide an ftp based solution to download new data from
your website and the MSDE remote client proceed with a scheduled job to bulk
insert it..
you can even set the job to notify via mail of the success/failure of it...
(just use an SMTP solution like http://www.sqldev.net/xp/xpsmtp.htm )
or mail them, but this become more manual as the final user has perhaps to
read the mail, save the attached file in some defined file system position
and let the scheduled task process the file..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.msde:20144
send them an sql file as they will not be able to login to msde remotely
"Randers" <rmonroe@.aulcorp.com> wrote in message
news:06cc01c52e3e$1e481e20$a501280a@.phx.gbl...
> Hello,
> What is the best way to import data to a client's MSDE
> database from our website? We want our client to log onto
> our website so we can update a table on the client's PC. I
> need a little direction on this, not sure which is the best
> way to go.
> Thank you,
> Randers

Importing and merging csv using sql

Hi Gurus.

My client drops for me many files like this on a shared drive M: daily
1_Test.csv
2_Test.csv
3_Test.csv

I would like you to advice on how to write a SQL code (that can include DTS if possible) that will take this files, merge them into one (since they have same columns) and send them to another client as one file. But it must only take the files of the same date and must not resend files i have send already. I need this to be an automated process since the files are dumped into M:drive midnite and I need this code so that I can schedule it as a job and run around 4h00am.OK..

I would do all (and have...now where is that code) in a stored procedure.

I would

1. us xp_cmdshell to interogate the directory and store all of the contents in a temp table
2. I would then parse it out and look for the files I want
3. I would then load these file to a temp table and audit them 1 at a time using bcp
4. When everything looked good, I would insert from the temp table to the final destination
5. I would then use xp_cmdshell again to vreate an datetime stamped sub archive folder, and would move EVERYTHING to that archive (i'm a neat freak...well...)
6. Thoughout the process I would echo out messages as to what;s going on to a file, and on the way out I would bcp that file to a log table.

That's what I would do.

If you want I can dig up some code.|||I would also go the route that Brett suggested, there might be some "features" that you could skip, but on the other hand you get quite flexible code if you follow his suggestions!

The only thing that I would do differently is that I would create one folder for the archived files and store them there (with dates as filenames), but this is just a matter of personal preference. :)

Why would you like to include DTS to this?
...I know that DTS is good for a lot of things, but personally I find scritping much more portable (this might have something to do with that I haven't used DTS that much, so my opinion could change in the future).

Check out this page from Nigel Rivett:
http://www.nigelrivett.net/ImportTextFiles.html
...you should at least get an idea of how to aproach the problem.

Pati|||I would do like Brett Kaiser and query the directory where these files are kept. And store all the contents in a #temp table. What I would do differently is that I would use a directory structure. I would for example keep the files in C:\CSV and once the file has been processed I would move the file to another directory say C:\CSV\sent by using a stored procedure.

In the database say TEST, I would create a table test_csv with the same fields as the .csv file.

I will then use a procedure like this one, I have written it specially for you. The input parameter will come from the #temp table well which means this procedure will be called from another procedure that will have loop for the filenames field and the procedure csv_test will be called from inside the loop.

-- SQL CODING BEGINS HERE
-- csv_test 'C:\CSV\Book2.csv'
create proc csv_test
@.filename varchar(70)
as
begin

declare @.cmd as varchar(100)
declare @.cmd_insert as varchar(300)

SET @.filename = replace(@.filename, '''', ''')
set @.cmd_insert = 'BULK INSERT TEST.dbo.test_csv FROM ''' + @.filename + ''' WITH ( FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'' )'
exec (@.cmd_insert)

set @.cmd = 'xp_cmdshell ''move ' + @.filename + ' C:\CSV\SENT'''
exec (@.cmd)

end

-- SQL CODING ENDS HERE

And then at last transfer the table to the destination table and truncate csv_test. Remember you can do everything using a procedure and schedule a DTS to run at a specified time every day.
:)

Roshmi Choudhury