How do you create text files from a spreadsheet?

May 2, 2007 10:55 PM

How do you create text files from a spreadsheet?

I export a query from my Access database to an excel spreadsheet. I would like to take 3 fields and create a text file from them. I would like the name of the file to contain 2 of those fields and inside the file to contain another field. How would I go about doing that? So I would like this 400 record query to become 400 txt files.

posted by bigmusic to Computers & Internet (6 answers total)

Response by poster: Office XP(2002) if that's important.
posted by bigmusic at 11:02 PM on May 2, 2007

Best answer: I don't think there's a way to do this without writing some sort of script or macro.

You can save it to a .csv file and do some shell-script trickery to generate the text files. I wouldn't recommend this approach since Windows gives you such a crippled shell, but for what it's worth, the command would be something like this:

for /f "skip=1 tokens=1,2,3 delims=," %i in (file.csv) do @echo %k > %i%j.txt

Go to Start/Programs/Accessories/Command Prompt and type for /? for more information on the for command.

It would also be easy to do with a macro.

Sub GenerateTextFiles()
For Each Row In Selection.Rows
With Row.Cells
Open .Item(1) & .Item(2) & ".txt" For Output As #1
Print #1, .Item(3).Text
Close #1
End With
Next Row
End Sub

Let me know if you have any questions. I haven't tested either of these solutions very much, hopefully someone will be able to point out if I've made a mistake.
posted by zixyer at 11:25 PM on May 2, 2007

You would need to need some sort of programming language to acheive this (e.g. PHP). You could also do this easily in something like R.

Save the excel file as a comma seperated file (.csv)
Read that in a script
Go through each row and save column 3 into Column1_Column_2.txt
rinse, repeat (via a loop).
posted by special-k at 11:26 PM on May 2, 2007

Keep in mind that if you go the .csv route you'll need to handle some special cases if any of your cells contain commas or quotes.
posted by zixyer at 11:47 PM on May 2, 2007

Response by poster: I went with the for method. Thanks zixyer!
posted by bigmusic at 3:12 AM on May 3, 2007

The for method, as given, is going to put extra spaces before and after the text it writes to the file. You could fix that by replacing the "echo" part of it with

@echo.%k>%i%j.txt

Also note that you can save this for command in a .bat or .cmd file, leave it in the same folder with your .csv file, and double-click it to generate the .txt files. If you do this, you'll have to change every occurrence of % in the command to %% in the batch file.

It's almost certainly going to end up misbehaving in data-dependent ways, though. If this were my problem, I'd be writing on-click handlers for a button in an Access form that just spits out the .txt files directly. The code would mostly be similar to what zixyer suggested for the macro option.
posted by flabdablet at 4:41 AM on May 3, 2007