1

I have an Excel file containing 120,000 lines of employee data in the following format:

SO21169646 question first example

and for JSON input would like the results laid out so:

SO21169646 question second example

The language doesn’t matter (PHP, JavaScript, VBA, Python…) but how might I achieve such conversion?

Community
  • 1
  • 1
BetaCoder
  • 302
  • 1
  • 13
  • 2
    I hope those aren't real names and addresses. – Steven Rumbalski Jan 16 '14 at 18:18
  • 2
    It looks like you want us to write some code for you. While many users are willing to produce code for a coder in distress, they usually only help when the poster has already tried to solve the problem on their own. A good way to demonstrate this effort is to include the code you've written so far, example input (if there is any), the expected output, and the output you actually get (console output, stack traces, compiler errors - whatever is applicable). The more detail you provide, the more answers you are likely to receive. – Martijn Pieters Jan 16 '14 at 18:19
  • 1
    @Steven Rumbalski - Yup Thats all Dummy Info generated from fakenamegenerator.com to depict the Op – BetaCoder Jan 16 '14 at 18:19
  • 2
    How about Excel's export-to-CSV and then manipulating the result in R (or similar)? – hd1 Jan 16 '14 at 18:21
  • Start by finding a library capable of reading Excel files – Mark Baker Jan 16 '14 at 18:22
  • @hd1 Yes export to csv becomes a bit complicated to manipulate – BetaCoder Jan 16 '14 at 18:23
  • 1
    Thanks to @pnuts for Response and edit , Finally after applying the formula and excel2csv conversion,i converted the csv to json using this http://shancarter.github.io/mr-data-converter/ – BetaCoder Jan 16 '14 at 20:19
  • 1
    This question appears to be off-topic because it appears to be a request for someone else to write the code, and doesn't identify a specific question about programming. – Adi Inbar Jan 17 '14 at 02:06
  • I programmed Php arrays,ended with bunch of errors popping regularly , I Thought to ask for a shortcut,oops @AdiInbar before posting, you should see the below formula is the solution – BetaCoder Jan 17 '14 at 02:12
  • I didn't actually post that as a comment, it was a custom close vote reason that was added as a comment automatically. I encountered this question in the Close Votes review queue. While you may have received an answer, I still agree with Martijn Pieters' nomination for closing. Questions that ask for code without showing an attempt to solve the problem and identifying a specific issue are considered off-topic at SO. That's not intended as a put-down, it's just not the type of question that fits SO's format. – Adi Inbar Jan 17 '14 at 02:40

1 Answers1

3
=IF(NOT(ISBLANK(A5)),C6&", "&C7&", "&C8&", "&C9)

in D4 copied down (120,000 rows), replaced with values and deletion of ColumnC and any row containing - (or blank) in Column A might do it.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • That Generates - 7 Terrick Rd, EGLETON, LE15 0PH, UK , how do i do it for all my data in excel file ? All are containing the same spacing and exact format as depicted above – BetaCoder Jan 16 '14 at 18:29
  • =IF(NOT(ISBLANK(A12)),C13&", "&C14&", "&C15&", "&C16) Generates it for second row , BUT 1lakh records i cant manually do it any automation script or known method , Thanks by the way for the hope – BetaCoder Jan 16 '14 at 18:32
  • WOW , I am almost near without any script , thanks but the two issues here- data is 1.duplicated,2.deleting the C row makes the formula applied row #REF , here is the pic - http://tinypics.in/FF – BetaCoder Jan 16 '14 at 18:48
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/45397/discussion-between-betacoder-and-pnuts) – BetaCoder Jan 16 '14 at 18:52
  • How do i avoid generation of extra "EGLETON, LE15 0PH, UK" generated due to A6 not being the blank and How do i remove the FALSE tags generated , almost near but cant make it to final.Any pragma to not generate content for - A4 matches ? – BetaCoder Jan 16 '14 at 18:56
  • Oops manually deleting of "FALSE" and extra generated matches of partial address from A4... and extra deletiong for 1lakh + records is really a messy straining task any automation or query or a shortcut – BetaCoder Jan 16 '14 at 19:01
  • 1
    WOW WOW WOW , MR.DataScientist That works , Thanks a TON , You guided the 1lakh + format within mints , Thanks a Ton it worked :) – BetaCoder Jan 16 '14 at 19:28