Solved

how to import data from excel to a csv automatically via a macro

Posted on 2011-03-17
15
656 Views
Last Modified: 2013-11-25
Hi
I have an excel sheet from which i would like to import the values into a csv..I want this to be automated(it should happen at an end of day basis)..so i just created a csv file and recorded a macro and imported the data from my excel sheet...However csv's dont have macro capability.So now basically my recorded macro is useless ...any suggestions would be really helpful.
Thanks,
Kris
0
Comment
Question by:kris_257
  • 6
  • 4
  • 3
  • +1
15 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35163147
I would maintain an xls workbook that does the work and generates the CSV file.

Can you give some more information as to the process and who uses/consumes the data?

Kevin
0
 

Author Comment

by:kris_257
ID: 35163305
actually im pulling data in my xls via a web query.This gets updated on a daily basis.so basically i want to move  all this data to a csv and as and when my xls pulls the daily data off the web i want it to be reflected in a csv...so how should i go about this>>??
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35163973
Keep everything in the xls workbook with the macros. When the daily pull is done, export the CSV file using VBA at that time.

Kevin
0
 

Author Comment

by:kris_257
ID: 35163996
hey kevin...the main problem im facing here is that the export to the csv should be automated....So how do i go about that..could you help me out with a few snippets of code...
thanks,
kris
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 200 total points
ID: 35164005
The routine below queries the user for a save as file name and then saves the active worksheet in either tab or comma delimited format depending on the file extension.

[Begin Code Segment]

Public Sub ExportWorksheet()

' Export the active worksheet as either a tab delimited or comma delimited
' file without the Excel warning dialogs and without changing the workbook
' format or saved location.

   Dim FilePath As String
   ActiveSheet.Copy
   FilePath = Application.GetSaveAsFilename(FileFilter:="Text (Tab delimited) (*.txt),*.txt,CSV (Comma delimited) (*.csv),*.csv")
   If FilePath = "False" Then Exit Sub
   ActiveWorkbook.SaveAs FilePath, IIf(UCase(Right(FilePath, 4)) = ".TXT", xlTextWindows, xlCSV)
   ActiveWorkbook.Close False

End Sub

[End Code Segment]

If the file path is already known then change:

   FilePath = Application.GetSaveAsFilename(FileFilter:="Text (Tab delimited) (*.txt),*.txt,CSV (Comma delimited) (*.csv),*.csv")

to:

   FilePath = "c:\full\path\to\file.txt"

Kevin
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35164032
what excel version are you using?
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35164125
this is a vbs script that runs the conversion from xls to csv from command prompt. however this doesn't work with the new .xlsx format but if you found other command line method that works as desired, you can set a timer to run automatically everyday by using Windows Task Scheduler.

1- open a notepad
2- copy-paste the following code:
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
oBook.SaveAs WScript.Arguments.Item(1), 6
oBook.Close False
oExcel.Quit
WScript.Echo "Excel Document Successfully Converted"

3- save the file as convxls.vbs (choose Save as type = All Files (*.*)
4- try the script manually - go to command prompt and to the foler you created the convxls.vbs files
- assuming the convxls.vbs and a sample excel file called book1.xls is in C:\Temp folder
- run C:\TEMP> convxls c:\temp\book1.xls c:\temp\book1.csv
5- if this script works, you can create a Task Scheduler (Start Menu-All Programs-Accessories-System Tools-Task Scheduler) and set to run this script automatically at your desired time everyday
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:kris_257
ID: 35164297
hi kevin,
Thanks a lot..your solution works..not too sure about how well it will get automated though..will figure that out...will get back to you if i have any other problems.thanks a ton.
0
 
LVL 4

Expert Comment

by:rowanscott
ID: 35164676
Hi

Not sure if you still need help on this.

The web query gets updated on a daily basis you say. Whatever triggers this update you want to carry on and save the csv as far as i can understand.

I have attached a sample which runs as the workbook opens. I have put a debug.assert line in so that the code will break as you open it and you can step through though its very simple.

Best regards

Rowan




demo.xlsm
0
 

Author Comment

by:kris_257
ID: 35178253
hi everyone thanks for ur help...i just need a slight modification though..my xls sheet currently has historical data which gets updated on a daily basis...now i import all this data to my csv using the routines u guys gave me...now since i have all the historical data on my csv i would like to clear my xls .the xls now will get updated daily with the data tht im pulling of the web query...i would like to append this daily data to the existing data in my csv...this way my csv becomes the database and i dont have to store all the data in  my xls and csv...basically i want to append data to my csv..hope you guys got my question...
Regards,
Kris
0
 
LVL 4

Expert Comment

by:rowanscott
ID: 35178450
the crux of what you need is this i think

Dim FileNum As Integer, strMessage as String

    FileNum = FreeFile ' next file number
    Open LogFileName For Append As #FileNum ' creates the file if it doesn't exist
    Print #FileNum, strMessage ' write information at the end of the text file
    Close #FileNum ' close the file
End Sub

I use something similar in a multi-user system at work to give a log of anything i want to watch in my code to see if i can improve performance.

but you need to create the string to write first.

best regards
0
 
LVL 4

Expert Comment

by:rowanscott
ID: 35178507
Hi Kris

If you post a sample workbook i could do a demo of creating the csv string as well.
You would not have to do the whole sheet. You could export just what you want.

Best Regards
0
 

Author Comment

by:kris_257
ID: 35178651
hi scott,
I just attached a sample workbook ..it contains historical data which gets updated on a daily basis...basically all the values in my xls should get copied to the csv....once that happens..ill clear my xls...my xls will keep pulling data(one row of data) on a daily basis..so at the end of each day my xls will have one row of data which should get appended to the existing data in the csv...hope im clear in describing the problem.
Thanks.
Kris
Book8.xls
0
 
LVL 4

Accepted Solution

by:
rowanscott earned 300 total points
ID: 35179294
Have a look at the attached.
Probably not exactly what you need but should give you the knowledge you need.

The macro to run is called Main

Best Regards

Rowan
WebQandCSVappend.xls
0
 

Author Closing Comment

by:kris_257
ID: 35196173
thanks to all of you for helping me out
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

This is an explanation of a simple data model to help parse a JSON feed
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

743 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

9 Experts available now in Live!

Get 1:1 Help Now