# Creating CSV or otherwise consolidating separate records using Excel **URL:** \ **Category:** Factual Questions\ **Created:** [April 16, 2013, 9:10pm UTC](https://boards.straightdope.com/t/creating-csv-or-otherwise-consolidating-separate-records-using-excel/655874 "2013-04-16T21:10:30Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![Stoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/stoid/32/272_2.png) [@Stoid](https://boards.straightdope.com/u/Stoid)\ **Post date:** [April 16, 2013, 9:10pm UTC](https://boards.straightdope.com/t/creating-csv-or-otherwise-consolidating-separate-records-using-excel/655874/1 "2013-04-16T21:10:30Z")
I’m on a Mac, first of all. If I have a series of records that look like this: > [@](#): > > 7-zip (POSIX) > Description : claims to be a good compressor > Package : p7zip > Version : 4.57-3p > Section : Archiving > Maintainer : Jay Freeman (saurik) > Architecture : iphoneos-arm > Installed-Size : 4328 > Action Menu > Description : Adds actions to the action menu > Package : actionmenu > Version : 1.2.12 > Section : System > Author : Ryan Petrich > Depends : mobilesubstrate, firmware (\>= 3), preferenceloader (\>= 2.0.5), firmware (\<\< 6.2), cydia (\>= 1.1.1) > Conflicts : actionmenu-pluspack (\<\< 1.2) > Architecture : iphoneos-arm > Installed-Size : 456 > Depiction : [Action Menu](http://rpetri.ch/cydia/actionmenu/) > Action Menu Plus Pack > Description : Additional actions for Action Menu! > Package : actionmenu-pluspack > Version : 1.2.8 > Section : System > Author : Ryan Petrich > Depends : actionmenu (\>= 1.2.8), mobilesubstrate > Architecture : iphoneos-arm > Installed-Size : 224 > Depiction : [AM Plus Pack](http://rpetri.ch/cydia/actionmenu-pluspack/) > Activator > Description : Centralized gestures, button and shortcut management for iOS > Package : libactivator > Version : 1.7.4 > Section : System > Author : Ryan Petrich > Maintainer : Ryan Petrich > Replaces : com.booleanmagic.overboard (\<= 1.1), com.ashman.lockinfo (\<= 2.0.0-6), com.clezz.quickdo, com.clezz.quickdoipad, com.fuyuchi.missionboardpro (\<= 1.3) > Depends : mobilesubstrate (\>= 0.9.3228), preferenceloader (\>= 2.0.4), firmware (\>= 3.0) > Conflicts : com.booleanmagic.overboard (\<= 1.1), com.ashman.lockinfo (\<= 2.0.0-6), sbsettings (\<= 3.0.6), com.clezz.quickdo, com.clezz.quickdoipad, com.clezz.clezzqd, com.fuyuchi.missionboardpro (\<= 1.3) > Architecture : iphoneos-arm > Installed-Size : 2288 > Depiction : [Activator](http://rpetri.ch/cydia/activator/) > Homepage : [Activator](http://rpetri.ch/cydia/activator/) > > App Stat > Description : App usage stats; frequency, use-time & recent use > Package : com.clezz.appstat > Version : 1.0.2-3 > Section : Tweaks > Author : Ma Jun > Maintainer : BigBoss > Depends : mobilesubstrate, firmware (\>= 3.2) > Architecture : iphoneos-arm > Installed-Size : 118 > Depiction : [App Stat - TheBigBoss.org - iPhone software, apps, games, accesories, ringtones, themes, reviews](http://moreinfo.thebigboss.org/moreinfo/depiction.php?file=appstatData) > Homepage : [App Stat - TheBigBoss.org - iPhone software, apps, games, accesories, ringtones, themes, reviews](http://moreinfo.thebigboss.org/moreinfo/depiction.php?file=appstatData) Is there a relatively painless way that Excel can turn it into something more easily handled by using the field names just once and pulling all the information into the columns underneath those names? I don’t even necessarily want all the data, so I can leave off the inconsistent fields. I know this can be done somehow, I just don’t know how and I don’t know how convoluted and timesucky it is. ---
**Author:** ![mnemosyne](https://avatars.discourse-cdn.com/v4/letter/m/c4cdca/32.png) [@mnemosyne](https://boards.straightdope.com/u/mnemosyne)\ **Post date:** [April 16, 2013, 11:29pm UTC](https://boards.straightdope.com/t/creating-csv-or-otherwise-consolidating-separate-records-using-excel/655874/2 "2013-04-16T23:29:02Z")
As a first step, you can dump the text of each record into a single cell and then separate it using the colon as a [delimiter](http://www.makeuseof.com/tag/how-to-convert-delimited-text-files-into-excel-spreadsheets/). It’s pretty straightforward and will get your field names isolated. On this data, you’ll end up with two columns, A and B. Then it gets a little bit more difficult (this would be best using macros) but now you can place all your Field Names from column A across the top row (Copy, Paste Special\>Transpose) and then one-by-one transpose your data from column B into subsequent rows. If necessary, there are ways to strip extra spaces out of the data as well, if that becomes a problem for you. If this is data that will be manipulated a lot, and more records will be created, consider using a database (like MS Access), since it is better at this sort of thing. There’s a learning curve, but once it’s up and running it really is a much better tool than Excel. ---
**Author:** ![Kinthalis](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kinthalis/32/16084_2.png) [@Kinthalis](https://boards.straightdope.com/u/Kinthalis)\ **Post date:** [April 16, 2013, 11:59pm UTC](https://boards.straightdope.com/t/creating-csv-or-otherwise-consolidating-separate-records-using-excel/655874/3 "2013-04-16T23:59:33Z")
Second the advice to switch to a relational db if this is a large record set that is being manipulated a lot. Once in a db, you cna easily output to XML, CSV, anything you want. ---
**Author:** ![Mr\_Downtown](https://avatars.discourse-cdn.com/v4/letter/m/8e8cbc/32.png) [@Mr\_Downtown](https://boards.straightdope.com/u/Mr_Downtown)\ **Post date:** [April 17, 2013, 4:20am UTC](https://boards.straightdope.com/t/creating-csv-or-otherwise-consolidating-separate-records-using-excel/655874/4 "2013-04-17T04:20:09Z")
I might do it somewhat differently. First I’d make sure there each product had the same number of lines in the same order, even if it required putting in _N/A._ Still in a text editor, replace every double return (beginning of new record) with some distinctive character like ^. Then replace all returns with tabs, and go back to replace every ^ with a return. Now you should have each record on one long line, with the headings more or less lined up. Now replace the colons with even more tabs. Then bring this into Excel. You can copy and paste, or let the Import Wizard guess that tabs are delimiters. Look through to make sure things are lining up properly. Use Insert (shift cells right) to make sure of it. Finally, put in the column headings you want, and delete the columns saying _Version:_ a hundred times down the column.