Excel formatting is annoying!

[DEnc]

I’m having trouble importing text files into Excel. I’ve downloaded a text file of the Washington Double Star Catalog, which I’d like to import to Excel using Data/GetData/FromText. But when I parse the file to columns using the delimiter, I can’t get Excel to stop being “helpful” by treating entries such as “+034+005” as a calculation upon import.

My current version is Office 2016. I’ve had luck in turning off this feature in earlier versions of Excel with File/Options/Advanced/TransitionFormulaEvaluation, but with the current version, it’s a no go.

With other text files I haven’t delimited the file for import; I extracted columns in Excel with the left(text)/right(text) commands. Even then it’s a slog to fight the formatting, but ultimately it’s doable. However this file’s rows are particularly busy--with space delimiters of varying lengths across 11 columns of data.

Is there anyone out there that’s successfully battled through this nonsense? Is there some setting that finally and definitively turns formatting features off? Any advice would be appreciated, else I’ll resort to cracking open SQL For Dummies!

Thanks,

David

[JimMinCT]

I know to get excel to ignore the string as a formula, you insert an apostrophe at the beginning of the string.
Not sure if this is possible over the entire document you're importing. Sorry if not enough help. Yeah, Excel is no fun...

[DEnc]

Yup, that's a useful thing. But the database is 4 files, the first of which has ~32,000 rows!

[JimMinCT]

Could you write a macro that would automate the task? I have no idea what your skill level is.
My excel skills are probably about a 6 on a 1-10 scale, but I get by.
I've written a couple that ended up saving me a ton of time.

[DEnc]

RE: macros, I have written a few. The challenge remains: how to tease out the columns.

[Graeme1858]

Hello DEnc

Any chance you can post a Dropbox or Google Drive etc link to your text file?

It might be easier to sort out and then explain if the data was on the screen in front of me.

I'm too Excel rusty to visualize it otherwise!

Regards

Graeme

[DEnc]

Thanks Graeme, here's a dropbox link.

I've instituted Plan B just in case; my SQL primer will arrive tomorrow!

David

https://www.dropbox.com/s/24j15aohufie7 ... 1.txt?dl=0

[ARock]

From
https://www.cloudynights.com/topic/4448 ... sv-format/

the last post has a link to a github page which has an excel spreadsheet (maybe not the most current version of WDS)
https://github.com/miguelvb/wds_catalog

[Ylem]

I dislike Excel

[DEnc]

I dislike Excel

You're not alone!

How Excel is ruining the world - The Term Sheet_ Fortune's deals blogTerm Sh.pdf

(339.75 KiB) Downloaded 114 times

[DEnc]

From
https://www.cloudynights.com/topic/4448 ... sv-format/

the last post has a link to a github page which has an excel spreadsheet (maybe not the most current version of WDS)
https://github.com/miguelvb/wds_catalog

Thank you for these! I got the Excel file, looks great. Also the links look helpful with regard to ideas for SQL syntax once I have SQL up and running--I'll nab the latest catalog if all goes well.

[Graeme1858]

I’m having trouble importing text files into Excel

Nailed it!

In Excel
Click File, Open, Browse
Change file type to all files
Open your text file
Text Import Wizard starts
Original data type - Fixed width
Click Next
On Step 2 of 3 Create break lines to the left of the two DEC" headers
Click Next
Click Finish

If that didn't make sense, here's an Excel file:

https://1drv.ms/x/s!AtzUKEWck9gpvtI1qFi ... g?e=j0v2jk

I love Excel!

Regards

Graeme

[DEnc]

Nailed it!

Nicely done--thank you!

David

[Graeme1858]

No worries!

[Graeme1858]

Nicely done--thank you!

Did the method make sense?

[DEnc]

It looks sensible and devilishly simple--I'll try it once I find my way back out of a rabbit hole I'm working in.

[DEnc]

Did the method make sense?

That was easy! I glossed over the part of the wizard that allows you to manually create column breaks. Usually I work with the delimited file type, but this is much easier.

Thanks again Graeme.

David