Solved

Need Script to Export Daily Excel files to MSSQL 2012 Experss Database

Posted on 2014-01-15
2
338 Views
Last Modified: 2014-01-24
Greeting Experts:
I am in need of PowerShell/VBS script that can import multiple excel files to an MsSql database (2012 Express) used to analyze user metrics on any given day…  The files sit in 3 different folders (marked Region1, Region2, and Region3) defined by naming convention” Region<1,2,3>  - <Date(MMDDYYYY)>” depending on the what file the excel documents sits in… I looking for a script that can Quarry each network file directory (Region1, Region2, and Region3), look at the first two columns (A & B) as shown below, and import it in to MySQL Database based on  the date..

Can anybody give me a hand in creating a script that could handle this process…

screenshot of the first two columns of each Report
0
Comment
Question by:amstoots
2 Comments
 
LVL 7

Accepted Solution

by:
cstsang earned 500 total points
ID: 39787765
The following script is checking whether the excel file exists.
dim folderName(2),region(2),fso
folderName(0)="x:\":region(0)="Region 1"
folderName(1)="d:\":region(1)="Region 2"
folderName(2)="z:\":region(2)="Region 3"
'Set Object
Set fso = CreateObject("Scripting.FileSystemObject")
today=now()
for i=0 to ubound(folderName)
	doTask folderName(i),region(i)
next 
set fso =nothing
msgbox "done"
'=========================================================
sub doTask(path,name)
	'Create Condition
	fileName=path +name+"-"
	if (month(today)<10) then
		fileName=fileName+"0"
	end if
	fileName=fileName & month(today)
	if (day(today)<10) then
		fileName=fileName+"0"
	end if
	fileName=fileName & day(today)
	fileName=fileName & year(today) & ".xls"
	
	If (fso.FileExists(fileName)) Then
		msgbox fileName+" exists"
'		doImport(fileName)
	end if
end sub

Open in new window

For reading excel problem, you can refer here

For insert data to mysql server, you can refer here
0
 

Author Closing Comment

by:amstoots
ID: 39808070
Thanks....
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now