Solved

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

Posted on 2014-01-15
2
349 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Question has a verified solution.

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

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

734 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