0

I have a txt file that contains data which I need to input to Excel.

The problem is I have several of those files and I want to create an excel file for each. So I was thinking of scripting. Can anyone point me to how to start atleast the tool needed and language to use

Thanks

EDIT: turns out naming the extension .csv can directly open it in excel. Now looking on how to open .csv , change format to autofit width and then save to excel

Syntax_Error
  • 5,964
  • 15
  • 53
  • 73

2 Answers2

1

Why don't you just write CSV files... They open on Excel and require no effort.

http://en.wikipedia.org/wiki/Comma-separated_values

Bruna
  • 366
  • 2
  • 15
  • I am writing a CSV file but I'll have to go to excel and import from text then save with the file name...It's an easy job to do once, but I want it many times. So was checking if I can automate it – Syntax_Error Jan 05 '12 at 17:48
  • 1
    Chip Pearson has code and instruction posted on how to loop through directories http://www.cpearson.com/Excel/QuickTree.aspx – Jesse Jan 05 '12 at 18:20
  • 1
    CSV files have some problems to be aware of. The most common is that it will translate numeric-looking strings into numbers and drop leading zeros. There is no way to turn this off except via the manual import method. See http://stackoverflow.com/questions/137359/excel-csv-number-cell-format also. – Mark Ransom Jan 05 '12 at 18:29
0

Of course. I have happen to enjoy php a lot for this type of task, but most any other scripting language is capable of this. Ie: Python, Ruby .. and many more.

PHP comes with many built-in functions for CSV. It also runs fine on windows, is cross-platform if you ever need to port on a 'nux environnement. The language is quite easy to learn, there are tons and tons of tutorials out there for most anything you want to script.

To get started, if you want to crack the ice there is a very simple but working example here, with links to more php/excel ressources. OF course, if you take this path, there are many many more examples waiting for you on google.

Hope that helps, good-luck!

Stephane Gosselin
  • 9,030
  • 5
  • 42
  • 65
  • 3
    Seems unnecessary to go outside of Excel to script this when Excel's VBA is more than sufficient to accomplish it. – Jesse Jan 05 '12 at 18:22
  • Yes! Obviously, now that you mention it. Being a Linux developper, I have not used Office for the past 10 years. I still would slap this up in php, but to be fair the builtin scripting engine would probably do just as well, minus the portability and flexibility. – Stephane Gosselin Jan 05 '12 at 19:06
  • 2
    You linux guys, there's a joke about a hammer and a nail in there somewhere..... ;) – Jesse Jan 05 '12 at 19:25
  • 1
    Yeah. +1'ed all of you! *I should have known* mentionning PHP in a Windows thread would make me an easy target! Totally worth the laugh. – Stephane Gosselin Jan 06 '12 at 06:16