Link to home
Create AccountLog in
Avatar of tsnpdx_TSinc

asked on

Need .vbs script to format Excel data for export to SQL

Hello experts:  
I am totally new to vb script so solutions with explanatory comments would be greatly appreciated.  
I have an excel file with data that must be "massaged" before being exported to our SQL database every week during off duty hours.  My current proposed solution involves execution of a .bat file that
  runs a VBScript to format excel data and save it as a .txt file and then to bcp the data to sql each week at 6:00am.
I am looking for a .vbs file that will do (unattended) the following to an excel spreadsheet named c:\Reports\input.xls
      1) format column D to numeric 0.00000
      2) delete column A
      3) delete rows 1-15
      4) Save results as tab-delimited .txt file named c:\Reports\output.txt

I am aware that there is a more efficient way to get the data from excel to SQL  and will give extra  points for an end-to-end .vbs solution that allows me to export the data directly from excel to SQL using an OBDC connection.  
Avatar of William Elliott
William Elliott
Flag of United States of America image

how does the data get into excel?
if the shee it always the same, then i coudl probably create a macro to massage the data then upload it to sql
Avatar of RobSampson
Flag of Australia image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of tsnpdx_TSinc


100 Extra points added
Excellent!! Both solutions worked for me  total points should be 225.  
No problem.  Thanks for the points and the grade.