Load twenty fixed width text files into Excel

The Sandbox Software/Technology
1

I have about twenty text files that I want to load into Excel. The suffix is NOT either .txt nor .csv which seems to be the only thing that Excel will accept.

Instead the suffix is .xyz because I work for the xyz insurance company.

What are my options for quickly getting those files into my workbook (each into their own sheet)?

I could possibly xcopy (or something) all of them changing their names to .txt. (The data within is all nicely spaced into columns.)

I’m using office professional 2019, fwiw.

I’ll probably resort to opening each one by one and copy/pasting the data, but, by golly, there ought to be a better way.

2

Is txt insurance company hiring? (But seriously, hope someone else has a helpful idea for you.)

4 Likes
3

Can you open them all with notepad and convert them to .txt or .csv files?

1 Like
4

Certainly, but I can also open them all in excel, text to column them, then copy paste into the final destination.

I’m hoping there were some way to import them all at once into excel without having to clunkily copy paste .

5

Ah, good luck. I’ve never seen a company have it’s own file type before. Sounds annoying!

1 Like
6

I think it’s because they came from an outside consultant that they were so-named.
In the end, they’re just text files. Blech.
You know what, I have a copy-files macro where I could rename them as I copy them. I’m going to use that.

7

Why bother with a macro? A very simple DOS command will do it:

ren *.abc *.txt
1 Like
8

Space delimited sounds veeeery risky

9

So I have written a VBA macro that would append each of the files in a list (on a sheet in Excel) with a suffix, and combine into a single text file. Then you can import the single file all at once. You would then copy each part to a new sheet.

If the data in the 20 files is clearly defined in a way you can tell which file it belongs to, you can use DOS to combine the files into 1, no need for suffix

Alternatively, you can write a macro that opens each .insco file and does the parsing space delimited on each sheet.

Anyway, like I was sayin’, data is the fruit of the sea. You can barbecue it, boil it, broil it, bake it, saute it. There’s uh, data-kabobs, data creole, data gumbo. Pan fried, deep fried, stir-fried. There’s pineapple data, lemon data, coconut data, pepper data, data soup, data stew, data salad, data and potatoes, data burger, data sandwich. That- that’s about it

3 Likes
10

Nice RN!

11

What’s the reference? Seems like something I’d find amusing…

12

I’d create a macro if I were going to do this several times.
One time uploading 20 files? Done in 25 minutes.

And I agree with ND: Space-Delimited is very risky. You might have to check each upload separately for stuff in, say, Column X where there should be no data.

13

A little dBase III for you:
REPLACE “data” WITH “shrimp”

1 Like
14

Read them into SAS and write them out to an excel file.

1 Like
15

Forrest Gump

1 Like
16

I misspoke in my OP…they’re not space delimited per se…they’re more like flat files where each field is at a predetermined position.

17

Good ol’ DOS - nuthin’ beats that!

Thanks!

18

Yeah, this is a one-off. Maybe I should hire an intern to do it for me.

Fortunately, there’s only 10 columns, so I should be able to verify by inspection.

19

Oh fixed width, that’s less perilous but a pain

1 Like
20

I wrote a generic fixed record flat file to comma delimited prepped for SQL BCP macro in excel. You put the field length and type in a list (you can make multiple import “types”) and list the input and output files (and import type to use for each file). Helps with “one-offs”. You can layer in the BCP call as well.