Solved

vbscript to import a csv and output in a different format

Posted on 2013-01-04
12
1,314 Views
Last Modified: 2013-01-14
Hi Experts. Let me apologize in advance for my ignorance as I do not know much vbscript.

Here is what I am looking for help on. I need a vbscript that will import the contents of a csv file, into a specific worksheet, in a predefined excel file. Then it needs to save as a tab delimited file.
The csv file and the predefined excel file is attached.

This is what I currently do and I am trying to automate the process.
1. I receive a csv file from vender (attached called "FileReceived")
2. I copy the contents into the "DAX Depts File" tab of predefined excel file (attached as "PredefinedFile")
3. Then I save just the first tab "DAX Miscellaneous Load File" as a tab delimited file (text)

My goal is to automate this process using vbscript by simply importing the contents into one tab and saving the other tab as a text file. Is this possible?

Thank you
FileReceived.csv
PredefinedFile.xlsx
0
Comment
Question by:jacobJL
12 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38745865
In the workbook you attached there are 3 sheets: DAX Miscellaneous Load File, Work Input File and DAX Depts File.

You say you copy the csv file into the DAC Depts File sheet. Is that sheet empty when you do that? More importantly you then say you save the DAX Miscellaneous Load File as a tab delimited file. Have you left out a step because you haven't done anything to the DAX Miscellaneous Load File sheet?
0
 
LVL 10

Expert Comment

by:tdlewis
ID: 38745873
Do you want the contents of "FileReceived.csv" to replace the contents of the "DAX Depts File" tab?

There is nothing happening when you import the CSV file that would cause the first tab to be changed (unless you have already written that code).
0
 
LVL 16

Accepted Solution

by:
terencino earned 500 total points
ID: 38746035
Hi Jacob, I made a sight change to your XLSX file, to link the data in the DAX Depts File sheet direct to the CSV. You can do this by clicking on A1, Data > Get External Data section > From Text (importing as CSV as normal) and when you get to the Import Data dialog as below, hit Properties and modify the properties dialog to overwrite existing data as below . You only need to do this once and your XLSX file will automatically update whenever your FileReceived.CSV is replaced. Also, we don't have to put it in the VBScript code, which is relatively short as follows
:
Set objExcel = CreateObject("Excel.Application")
objExcel.Application.DisplayAlerts = False
objExcel.Workbooks.Open Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "PredefinedFile.xlsx"
Set objPredefined = objExcel.ActiveWorkbook
Call objPredefined.RefreshAll()
objPredefined.Worksheets("DAX Miscellaneous Load File").Activate
objPredefined.SaveAs Replace(objPredefined.FullName, ".xlsx", ".txt"), 3
objPredefined.Close False
objExcel.Quit

Open in new window

So the process is now:
1. Make sure CSV_XL_Tab.vbs, PredefinedFile.XLSX and FileReceived.CSV are in the same folder. (If different file names or locations, update the script and the linked data range to suit.)
2. Save your new CSV file over the old one (It must have the same name, otherwise modify the VBS file)
2. Double-click on CSV_XL_Tab.vbs
3. Open Predefined.TXT to check all is in order. (This will be in the same folder as the XLSX, otherwise just update the VBS file to suit)

Note that none of the files will be visible when they open, and it only takes 5 second to complete.
Hope this works for you, let us know if any issues
...Terry
CSV-XL-TAB.zip
0
 

Author Comment

by:jacobJL
ID: 38746099
Thank you everyone,
terencino, I like your solution. Never thought of doing something like that. However I am getting an error when I run the script.

line 5
char 1
unknown runtime error.

@tdlewis the code is already in the Predefined worksheet.
@MartinLiss yes the sheet is empty when I copy the csv file. the DAX Miscellaneous Load File sheet has formulas and work input file sheet both have formulas that convert the data imported into a specific format.

Regardless, and I appreciate all your help on this, I think what terencino is suggesting may be the best solution if I can get the script to work.
error.png
0
 
LVL 16

Expert Comment

by:terencino
ID: 38746134
Hi Jacob are you using the same small CSV file?
0
 

Author Comment

by:jacobJL
ID: 38746136
I extracted the zip you sent and just ran the script. So yes same file.

I should also note that when I double click the vbs it just opens in textpad so I right click and select open with Microsoft Windows Based Script host.

Another edit.
So I went through the steps you provided and tried it in a different folder. When I run the script it prompts for the file to import. I chose the FileReceived.csv and it created the text file. Great! However, can we make this just choose the file automatically, so that I can just run a batch to execute this script whenever a new file is received?

Thank you
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 16

Expert Comment

by:terencino
ID: 38746145
Right, the CSV is in a different folder after it is unzipped, should have warned about that!

First because it crashed, there will be some instances of Excel open which you need to close, go to Task Manager > Processes tab > find each EXCEL.EXE image name and End Process.

Then open the XLSX file, go to DAX Depts File, right click > Data > Connections > Properties button > Definition tab > Edit query... button > navigate to where the CSV file is now > click on it > when the text file import wizard pops up hit Finish, then OK out of all the dialogs then save and close the Excel file. Or you could simply overwrite with a new connection as noted above.

Let me know how it goes
0
 

Author Comment

by:jacobJL
ID: 38751021
Terry,

Sorry I made some edits to my previous comments you may not have seen.
I got it work the way you said, however, can we make this just choose the file automatically, so that I can just run a batch to execute this script whenever a new file is received? Right now it's asking for a file to import when I run the script.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38753010
OK, need to replace the data range:
1. Delete the existing query
In the XLSX file, can you go to DAX Depts File sheet, and delete all the data. It should come up with a message "The range you deleted is associated with a query that retrieves data from an external source..." Press Yes to delete the query as well
2. Import text file
Select range A1,  Date > Get External Data > From Text. Select the FileReceived.CSV and import your data, selecting "My data has headers" Step 1, Comma delimiter in Step 2, then Finish
3. Modify the query
In the Import Data dialog that pops up next, press Properties... button and make sure it looks like the picture below, especially to uncheck the "Prompt for file name on refresh" and select "Overwrite existing cells"
4. Save the XLSX file and close
5. Run the VBS file
Data range propertiesLet me know how it goes
0
 

Author Closing Comment

by:jacobJL
ID: 38765120
Thank you Terry. Works great! unfortunately I may have to do it a completely different way. But you did exactly what I wanted.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38765157
Ha ha well that's the way it goes, all the best and thanks for the grade
0
 

Author Comment

by:jacobJL
ID: 38775514
Terry,
Feel free to take a stab at my new question if you's like.
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_27994886.html

Thanks
Jacob
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now