I have a 500k string stored in a single cell of my SQL Server table. This string is in JSON format, saved as string.
I am trying to export this single cells to examine it in more detail and create another db holding the keys/values, but when I try to export as json/csv using VSCode, not all of the data is transcribed to the final file.
This is what I see in my saved json file - 455k chars not available:
And similar in my csv file.
I thought this was an issue with VS Code not showing it, so tried reading it in python and parsing it, and found that the data is not present, and the last 455k chars are gone, and that only 65k are available.
I would like to examine and extract all key/values and convert to a different table, as there are 400k entries (average length of 110k char).
I guess it is a double barrel question:
- How can I parse the json column directly into keys/values to a new database?
- How can I extract the data from SQL Server so that I get all of the data in my
.csvfile?
1条答案
按热度按时间s8vozzvw1#
Please use MS SQL inbuilt JSON functionality and create a table in MS SQL using your one JSON cell.
https://learn.microsoft.com/en-us/sql/t-sql/functions/json-functions-transact-sql?view=sql-server-ver16
Then you can analyse the data in MS SQL. If you don't want to use MS SQL, you can query you table, right click the result, click select all, right click again, click copy with headers, open an Excel file, copy the data in there, save the file as CSV.
Then you can push it where you need it.
last option is: When you have used MS SQL JSON functions to normalize the data, you can install Airbyte or Fivetran somewhere and use those tools to move the data for you from one source to one target.