Help me autoimport!

May 14, 2007 7:47 AM

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!

posted by keep it tight to computers & internet (4 comments total)

When you put it into Excel, do you just resave it as another CSV, or do you actually do processing on it?
posted by AaRdVarK at 7:58 AM on May 14, 2007

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

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

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... :)

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