converting no of batch excel files into my sql database

i have no of  excel sheets and i would like to move the datas into master detail tables kind of relation in mysql database
can it be automated so that it can take the no of excel files map it into table columns of mysql database

let me know
roy_sanuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Is It MYSQL or My SQL database?

Regards Marten
0
roy_sanuAuthor Commented:
My SQL database
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
One time hit or something to automate?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

roy_sanuAuthor Commented:
Automate it by reading number of excel files from a folder and copying to database table
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Are they the same format?
0
roy_sanuAuthor Commented:
Yes all are same format xls
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
For starters, Excel is a poor choice of a data source for an ETL project, as users can do dang near anything that would cause the 'contract' to fail between source and destination that the ETL requires.

Expanding on 'contract' and Marten's comment above, does the schema of the Excel file(s) match the schema of the SQL destination database?  If not or 'no guarantees' then what you are asking is not possible, as SSIS and other ETL packages require a contract, and can't handle a 'just slam what's on the spreadsheet into a table' scenarios, unless the ETL pumps the entire row into a single column, and then T-SQL is executed to custom handle it.

SSIS has a 'ForEach File Loop' sequence that can process any number of files within a given folder, so it is possible to build one with a data pump inside that will process every Excel file and pump data into a destination table.
0
roy_sanuAuthor Commented:
Our excel is a timesheet format which we have data already for a 6 months in a excel which has data like emp I'd ,employee name ,project name date and  days in a week,task name .. we have defined a masters detail relationships in table strctures of my sql database.. issue is how we can automate this process...  is there any way to do that let me know....

Thanks
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If we're talking SQL Server (and not MySQL, that's not a skill of mine so I can't comment on it), a quickie way to pull this off would be to use T-SQL and OPENROWSET to attempt code that performs your insert.  A demo is here.

Another way is to use the Import Data Wizard to create an SSIS package.  Open up SSMS, then...
Right-click on the Database that is the destination > Tasks > Import Data
Follow the prompts to connect to your Excel source and SQL Destination
Towards then end when it asks you to save the package, check the box to save it.
A demo is here.   At the end you'll have an SSIS package that you can open up and make further modifications.

Good luck.
Jim
0
roy_sanuAuthor Commented:
Is it Sql Server is free.. our organization do not want to go for any paid database as we want only the last 6 months excel data to be exported to anything databases which is open source. Can you suggest any other databases which is free
0
David Johnson, CD, MVPOwnerCommented:
sql express is free but doesn't have all of the tools that one might want to use). one way is to export the excel data to a csv (comma separated) and import the csv into the database..
some ways of importing a csv into sql.
http://blogs.technet.com/b/heyscriptingguy/archive/2011/11/28/four-easy-ways-to-import-csv-files-to-sql-server-with-powershell.aspx
https://msdn.microsoft.com/en-us/library/ms188609.aspx

You might even have MS Access as part of your office suite. The potential problem is that your excel sheets may not be in a format that can be imported directly and some massaging of the data may be involved before you have the data in a common format that can be used. You may have to go to the source data before it is imported into excel to get the raw data in a form that is database friendly. So this could be a 5 minute job or a 5 month job depending upon how the data is stored in excel.
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
I would go with Powershell, and code a insert by Reading row by row in the Excel file, and inserting them into the database

These two links will help you get started.
http://blogs.technet.com/b/heyscriptingguy/archive/2008/09/11/how-can-i-read-from-excel-without-using-excel.aspx
for Reading excel, and:
https://technet.microsoft.com/en-us/magazine/hh289310.aspx
to access and insert to a SQL database.

There are ofcourse tons of articles, google searchs out there. It's just a matter of choosing whats suitable for you and your organisation.

Somebody created a module doing exactly what you want, I don't know if its free. But this might also be a good starting Point.
http://www.dbascript.com/import-excel-file-into-sql-server-table-using-powershell

Regards Marten
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.