Personal computing discussed

Moderators: renee, Dposcorp

 
paulWTAMU
Emperor Gerbilius I
Topic Author
Posts: 6257
Joined: Wed Nov 24, 2004 5:14 am
Location: Dallas, Texas

Excel Hell

Thu Jun 08, 2017 11:19 am

I've got a task in Excel I'm trying to figure out how to do.

We use a specialized software to track client interactions; it has very limited baked in reporting abilities, but mostly to get real reports you have to export CVS data and dump it into Excel.


The problem I'm having is two fold; the way it exports cilent request is wonky as hell. If they're different enough, they split up the interaction into two lines with the same interaction ID, but if they're similar they group them together (think, widget A and widget B and widget C) and they all wind up on the same cell in Excel. Compounding this, if the software groups those request that way, it lumps another piece of data (let's call it the suppliers) all together, as CSV, in the same cell in Excel.

So for example,

[Widget A, Widget B, Widget C] would be in one cell, there'd be a few columns with other data (client locations, size, etc), then another cell with [Supplier A, B, C].

It's easy enough to just do a Text to Columns and dump them all into a new column but then formatting gets wonky as hell; I could theoretically manually transpose everything and get them back into new rows to make pivots easier, but this spreadsheet is closing in on 100k rows, and I'm concerned about screwing up formatting and taking forever. Plus I'd like to be able to check on this more often.


My hands are tied with our software unfortunately.
Ugly people have sex all the time. We wouldn't have 6 and a half billion humans if you had to be beautiful to get laid.
 
Turkina
Gerbil Elite
Posts: 607
Joined: Mon Apr 21, 2003 10:02 pm
Location: Philly

Re: Excel Hell

Thu Jun 08, 2017 12:13 pm

Perhaps Im not understanding, but if [WidgetA, WidgetB, WidgetC] exists in the raw CSV then I would expect the Excel CSV importer to create 3 cells ([WidgetA and WidgetB and WidgetC] ). If they have some other formatting (like WidgetA+WidgetB+WidgetC) then maybe try opening the csv file as a textfile and doing a find/replace (replacing all "+" with ","). 100K rows shouldnt be too much for a decent text editor (like notepad++) to handle. Another option might be going the linux (or cygwin) route and using grep/awk/sed command line tools.
 
DPete27
Grand Gerbil Poohbah
Posts: 3776
Joined: Wed Jan 26, 2011 12:50 pm
Location: Wisconsin, USA

Re: Excel Hell

Thu Jun 08, 2017 1:13 pm

yeah, I'm having a hard time figuring out what the text file would look like that you're importing and why you can't seem to get it to import correctly. There are a number of Delimited import options (tab, semicolon, comma, space, other) that you can select from what would tell excel how to divide up the data.
Main: i5-3570K, ASRock Z77 Pro4-M, MSI RX480 8G, 500GB Crucial BX100, 2 TB Samsung EcoGreen F4, 16GB 1600MHz G.Skill @1.25V, EVGA 550-G2, Silverstone PS07B
HTPC: A8-5600K, MSI FM2-A75IA-E53, 4TB Seagate SSHD, 8GB 1866MHz G.Skill, Crosley D-25 Case Mod
 
Topinio
Gerbil Jedi
Posts: 1839
Joined: Mon Jan 12, 2015 9:28 am
Location: London

Re: Excel Hell

Thu Jun 08, 2017 2:15 pm

Do you understand the way your software splits (or not) the client requests, i.e. can you say "if a section A of the output CSV looks like X, then I can break it up by (move this here, that there) to make that section look the same as section B" and/or "if it looks like Y, I leave it alone" ?

If so, then writing a preprocessing script is the way to proceed, and actually this is the reason why I use a Mac, a good chunk of my time involves writing UNIX shell script to preprocess, then Excel workbooks where I write formulae and then array formulae to process, the various text/log/CSV files that come my way as (data).

The coding of the scripts (bash calling the usual grep, awk, sed &c with jot loops aplenty) is to ensure consistentcy in the something.csv which I can then open up in Excel and make arrays of, then write Excel formulae in sheets 2 and 3 (information), then plot (knowledge) ready for use in PowerPoint (wisdom!). FML.

TL;DL No matter how awful the output, if it's internally logical or semi-consistent then it's all fairly straightforward via moderate effort with UNIX scripting and Excel-fu capabilities, and once you've written the preprocessor you're sorted for future reports from that source.

If you're only on Windows, my sympathies and I hope this is similarly tractable, if perhaps more painful, in PowersHell :wink:
Desktop: 750W Snow Silent, X11SAT-F, E3-1270 v5, 32GB ECC, RX 5700 XT, 500GB P1 + 250GB BX100 + 250GB BX100 + 4TB 7E8, XL2730Z + L22e-20
HTPC: X-650, DH67GD, i5-2500K, 4GB, GT 1030, 250GB MX500 + 1.5TB ST1500DL003, KD-43XH9196 + KA220HQ
Laptop: MBP15,2
 
Wonders
Gerbil
Posts: 44
Joined: Thu Dec 18, 2014 11:11 am

Re: Excel Hell

Thu Jun 08, 2017 2:16 pm

Self-proclaimed Excel and CSV reformatting expert here. I understand you're hindered by the export's formatting in some way, but it's not clear exactly *what* the existing formatting is preventing you from doing. That info is key to an elegant solution. As an interesting side-note, the existing formatting as you've described it would be not only helpful, but in fact essential for certain common reporting operations, so it's not inherently "bad", just clearly not suited for what you need to accomplish (which again, I did not see described yet).
 
paulWTAMU
Emperor Gerbilius I
Topic Author
Posts: 6257
Joined: Wed Nov 24, 2004 5:14 am
Location: Dallas, Texas

Re: Excel Hell

Thu Jun 08, 2017 3:59 pm

It exports directly to an Excel spreadsheet; I select "Specialized Export to Excel", select the fields I need, and it spits it out in an Excel document. That's all done within the software and according to their vendor there's not a way to change that at this time but it's "under consideration" for a future patch. A prior software option we used to use did and it was great.

In cells where it's grouping multiple values, it separates them by semicolons. It's not truly CSV, but same idea (I think).
Ugly people have sex all the time. We wouldn't have 6 and a half billion humans if you had to be beautiful to get laid.
 
Topinio
Gerbil Jedi
Posts: 1839
Joined: Mon Jan 12, 2015 9:28 am
Location: London

Re: Excel Hell

Thu Jun 08, 2017 4:53 pm

Is it a CVS but with file extension of .xls/.xlsx ? If not, you could open in Excel, Save As to make a .csv file...
Desktop: 750W Snow Silent, X11SAT-F, E3-1270 v5, 32GB ECC, RX 5700 XT, 500GB P1 + 250GB BX100 + 250GB BX100 + 4TB 7E8, XL2730Z + L22e-20
HTPC: X-650, DH67GD, i5-2500K, 4GB, GT 1030, 250GB MX500 + 1.5TB ST1500DL003, KD-43XH9196 + KA220HQ
Laptop: MBP15,2

Who is online

Users browsing this forum: No registered users and 1 guest
GZIP: On