Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

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

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
Mike
Asked:
Mike
1 Solution
 
KNVB HKSITOCommented:
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
 
MikeSecurityAuthor Commented:
Thanks....
0
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.

Join & Write a Comment

Featured Post

Worried about phishing attacks?

90% of attacks start with a phish. It’s critical that IT admins and MSSPs have the right security in place to protect their end users from these phishing attacks. Check out our latest feature brief for tips and tricks to keep your employees off a hackers line!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now