Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-01-15
2
Medium Priority
?
358 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:Mike
2 Comments
 
LVL 7

Accepted Solution

by:
cstsang earned 2000 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:Mike
ID: 39808070
Thanks....
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
There are times when we need to generate a report on the inbox rules, where users have set up forwarding externally in their mailbox. In this article, I will be sharing a script I wrote to generate the report in CSV format.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses

916 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