uploading json file

[Mikis]

Hello,

I'm trying to convert a few thousand json files to a table. I expected this would be easy, but I can't figure it out.

I watched a few tutorials on youtube and tried to find help on this forum, but nothing seems to work for me.

I think my json files have an unusual format or something?

I attached an example file.

What I would ideally like to have is this kind of format (used two first records as example):

created_at	id	id_str	text	display_text_range	...
Tue May 15 11:26:40	996351151313338368	996351151313338368	@RPG_90ROME yaudah bayar berapa nih...	[12,35]	...
Tue May 15 11:26:40	996351151078424577	996351151078424577	RT @EO_ASC: Nah buat yang udah parkir...		...

Any help?

Thanks,

Mikis

[BenMoss · Accepted solution]

I'm not sure why it wasn't reading your .json correctly.

I have ammended your workflow to read the file in as an undelimited .csv file before using the json parse and cross-tab tools to bring the data into the structure you desire.

Check out the solution attached.

Ben

[Mikis]

Brilliant.

That /0 (a 'zero' right) delimiter, what does it mean exactly?

Haven't seen that one before.

Thanks!

Mikis

[williamsbk]

---

@Mikis

That /0 (a 'zero' right) delimiter, what does it mean exactly?

---

Between the { } you have \r\n\n, which is odd as I've normally seen \r\n (Windows) or \n (Linux)

\r is Carriage Return.

\n is Line Feed

When you look at this in the Preview of the file you will see it as:

{   }

[Null]

{   }

What the \0 (Null) does is break the lines apart.

Then the Multi Row tool then looks to see if "JSON_Name" is null to increment the RecordID.

Cross then puts it back together.

[MAAbdullahAlMubarah]

Doesn't work for me, could you please practicing it on the attached file.

[williamsbk]

What you have is an file where each line is a JSON object and you will need to process it as such.

[MAAbdullahAlMubarah]

Can you explain more please?

[williamsbk]

A true JSON file will look like

{
  "tweets": [
            { // tweet 1},
            { // tweet 2},
            { // tweet 3}
      ]
}

You have

{ // tweet 1}
{ // tweet 2}
{ // tweet 3}

You don't have an JSON object that contains the tweets as JSON objects, you have a file of JSON objects.

That structural difference means your processing will be different.