Help me autoimport!
May 14, 2007 7:47 AM Subscribe
Automate Excel Filter.....
I am getting via email a CSV file daily that I would like to automate into excel.
The file is like this....
000001,Acme,Box99,This is the description,05/10/2007
I need to put the data in to particular columns.
000001 into column B, Acme into column D, Box99 into Column G, etc.
Anyone have a thought on automating this so I don't have to always just cut and paste?
Thanks in advance!
The file is like this....
000001,Acme,Box99,This is the description,05/10/2007
I need to put the data in to particular columns.
000001 into column B, Acme into column D, Box99 into Column G, etc.
Anyone have a thought on automating this so I don't have to always just cut and paste?
Thanks in advance!
Best answer: May I be the first to point out the fact that macros are your friend.
posted by chuckdarwin at 8:00 AM on May 14, 2007
posted by chuckdarwin at 8:00 AM on May 14, 2007
It can sometimes be easier to push the CSV into a database, then make a spreadsheet which can query and refresh therefrom using ODBC. If it's not going to be ongoing, and noone besides you uses the data, it's probably not worth it. But if you want it to happen while you're not running Excel manually, and/or other people want to pick up the changes automagically, it's actually pretty easy to script up. OTOH if you don't have access to a UNIXish server or feel comfy with scripting, Macros and automation are probably as good as you can get.
posted by freebird at 8:33 AM on May 14, 2007
posted by freebird at 8:33 AM on May 14, 2007
Best answer: You could just save it as an .xls file to begin with, then open in excel, highlight the first column, and choose Data -> Text to Columns, and choose to use Delimiter of Comma. Once it's split, just choose to save it again and you're done.
If you want programmatic control, just do a search on vbscript excel.application and you'll find a ton of sample scripts using the Excel object; you could rather easily automate a "for every line in the csv file, split it by comma, and add it to an excel spreadsheet as formatted text".
You could also get fancy, and use the CDO object for Outlook (if Outlook was your mail read) and have it automatically poll your mailbox at the right time every day, find the email, save the attachment, and process it, even then re-send it out as a formatted Excel spreadsheet, all without you doing a single thing... :)
posted by hincandenza at 2:16 PM on May 14, 2007
If you want programmatic control, just do a search on vbscript excel.application and you'll find a ton of sample scripts using the Excel object; you could rather easily automate a "for every line in the csv file, split it by comma, and add it to an excel spreadsheet as formatted text".
You could also get fancy, and use the CDO object for Outlook (if Outlook was your mail read) and have it automatically poll your mailbox at the right time every day, find the email, save the attachment, and process it, even then re-send it out as a formatted Excel spreadsheet, all without you doing a single thing... :)
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
Set FSO = CreateObject("Scripting.FileSystemObject")
Set myFile = FSO.OpenTextFile( "filename.csv", 1 )
iRow = 0
Do while not MyFile.atendofstream
curLine = myFile.ReadLine
arrSplit = Split(curLine, ",", -1, 1)
iColumn = 0
for iColumn = 0 to Ubound(arrSplit)
objExcel.Cells(iRow, iColumn + 1).Value = arrSplit(iColumn)
iRow = iRow + 1
next
Loop
posted by hincandenza at 2:16 PM on May 14, 2007
This thread is closed to new comments.
posted by AaRdVarK at 7:58 AM on May 14, 2007