Convert TSV to JSON Tool

Convert TSV to JSON using Excel

TSV files store data in tab separated values format. Data in TSV files is textual and tabled.

JSON is also used to store structured data but the structure of the stored data could be more complex than a basic table. Thus, converting from TSV to JSON is relatively straightforward since you will be converting data to a format which can store more complex structures.

For conversion we can assume that a JSON file will represent CSV data as an array, where each entry in an array will be a CSV line. We will also assume that first line of CSV file contains field names and other lines contain data entries for these fields. Thus each entry in JSON line array will contain mapped field names to values of the specific CSV line.

Let’s proceed with an example of a TSV file.

Name Age Gender John 30 Male Melissa 25 Female Alan 42 Male Chelsey 40 Female

As a first step open this TSV file in Microsoft Excel. You should get the following representation.

	A	B	C
1	Name	Age	Gender
2	John	30	Male
3	Melissa	25	Female
4	Alan	42	Male
5	Chelsey	40	Female

Now place the following formula in the column D starting from the second row: =CONCAT("{""",A1, """:""", A2, """,""", B1, """:""", B2, """,""", C1, """:""", C2, """},")

You should get {"Name":"John", "Age":"30", "Gender":"Male"}, line as a result of this formula. Copy this formula to all the other rows in column D (to quickly copy it, select the D2 cell and click on a small rectangular section at the right bottom conrner of the cell border). Once you copy you will get following lines in column D. {"Name":"John","Age":"30","Gender":"Male"}, {"Name":"Melissa","Age":"25","Gender":"Female"}, {"Name":"Alan","Age":"42","Gender":"Male"}, {"Name":"Chelsey","Age":"40","Gender":"Female"},

To make it fully compliant JSON file add { at the beginning and } at the end of the file and remove the comma at the last line. { {"Name":"John","Age":"30","Gender":"Male"}, {"Name":"Melissa","Age":"25","Gender":"Female"}, {"Name":"Alan","Age":"42","Gender":"Male"}, {"Name":"Chelsey","Age":"40","Gender":"Female"}, }

To make JSON look nicer you can put it through any online JSON beautifier and get JSON like the one below:

{
   {
      "Name":"John",
      "Age":"30",
      "Gender":"Male"
   }{
      "John":"Melissa",
      "30":"25",
      "Male":"Female"
   }{
      "Melissa":"Alan",
      "25":"42",
      "Female":"Male"
   }{
      "Alan":"Chelsey",
      "42":"40",
      "Male":"Female"
   }
}