• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • 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

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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