Error ошибка лишние данные после содержимого последнего столбца

An empty table won’t do. You need table that matches the structure of input data. Something like:

CREATE TABLE raw_data (
  col1 int
, col2 int
  ...
);

You don’t need to declare tab as DELIMITER since that’s the default:

COPY raw_data FROM '/home/Projects/TestData/raw_data.txt';

800 columns you say? That many columns would typically indicate a problem with your design. Anyway, there are ways to half-automate the CREATE TABLE script.

Automation

Assuming simplified raw data

1   2   3   4  -- first row contains "column names"
1   1   0   1  -- tab separated
1   0   0   1
1   0   1   1

Define a different DELIMITER (one that does not occur in the import data at all), and import to a temporary staging table with a single text column:

CREATE TEMP TABLE tmp_data (raw text);

COPY tmp_data FROM '/home/Projects/TestData/raw_data.txt' WITH (DELIMITER '§');

This query creates the CREATE TABLE script:

SELECT 'CREATE TABLE tbl (col' || replace (raw, E't', ' bool, col') || ' bool)'
FROM   (SELECT raw FROM tmp_data LIMIT 1) t;

A more generic & safer query:

SELECT 'CREATE TABLE tbl('
    ||  string_agg(quote_ident('col' || col), ' bool, ' ORDER  BY ord)
    || ' bool);'
FROM  (SELECT raw FROM tmp_data LIMIT 1) t
     , unnest(string_to_array(t.raw, E't')) WITH ORDINALITY c(col, ord);

Returns:

CREATE TABLE tbl (col1 bool, col2 bool, col3 bool, col4 bool);

Execute after verifying validity — or execute dynamically if you trust the result:

DO
$$BEGIN
EXECUTE (
   SELECT 'CREATE TABLE tbl (col' || replace(raw, ' ', ' bool, col') || ' bool)'
   FROM  (SELECT raw FROM tmp_data LIMIT 1) t
   );
END$$;

Then INSERT the data with this query:

INSERT INTO tbl
SELECT (('(' || replace(replace(replace(
                  raw
                , '1',   't')
                , '0',   'f')
                , E't', ',')
             || ')')::tbl).*
FROM   (SELECT raw FROM tmp_data OFFSET 1) t;

Or simpler with translate():

INSERT INTO tbl
SELECT (('(' || translate(raw, E'10t', 'tf,') || ')')::tbl).*
FROM   (SELECT raw FROM tmp_data OFFSET 1) t;

The string is converted into a row literal, cast to the newly created table row type and decomposed with (row).*.

All done.

You could put all of that into a plpgsql function, but you’d need to safeguard against SQL injection. (There are a number of related solutions here on SO. Try a search.

db<>fiddle here
Old SQL Fiddle

An empty table won’t do. You need table that matches the structure of input data. Something like:

CREATE TABLE raw_data (
  col1 int
, col2 int
  ...
);

You don’t need to declare tab as DELIMITER since that’s the default:

COPY raw_data FROM '/home/Projects/TestData/raw_data.txt';

800 columns you say? That many columns would typically indicate a problem with your design. Anyway, there are ways to half-automate the CREATE TABLE script.

Automation

Assuming simplified raw data

1   2   3   4  -- first row contains "column names"
1   1   0   1  -- tab separated
1   0   0   1
1   0   1   1

Define a different DELIMITER (one that does not occur in the import data at all), and import to a temporary staging table with a single text column:

CREATE TEMP TABLE tmp_data (raw text);

COPY tmp_data FROM '/home/Projects/TestData/raw_data.txt' WITH (DELIMITER '§');

This query creates the CREATE TABLE script:

SELECT 'CREATE TABLE tbl (col' || replace (raw, E't', ' bool, col') || ' bool)'
FROM   (SELECT raw FROM tmp_data LIMIT 1) t;

A more generic & safer query:

SELECT 'CREATE TABLE tbl('
    ||  string_agg(quote_ident('col' || col), ' bool, ' ORDER  BY ord)
    || ' bool);'
FROM  (SELECT raw FROM tmp_data LIMIT 1) t
     , unnest(string_to_array(t.raw, E't')) WITH ORDINALITY c(col, ord);

Returns:

CREATE TABLE tbl (col1 bool, col2 bool, col3 bool, col4 bool);

Execute after verifying validity — or execute dynamically if you trust the result:

DO
$$BEGIN
EXECUTE (
   SELECT 'CREATE TABLE tbl (col' || replace(raw, ' ', ' bool, col') || ' bool)'
   FROM  (SELECT raw FROM tmp_data LIMIT 1) t
   );
END$$;

Then INSERT the data with this query:

INSERT INTO tbl
SELECT (('(' || replace(replace(replace(
                  raw
                , '1',   't')
                , '0',   'f')
                , E't', ',')
             || ')')::tbl).*
FROM   (SELECT raw FROM tmp_data OFFSET 1) t;

Or simpler with translate():

INSERT INTO tbl
SELECT (('(' || translate(raw, E'10t', 'tf,') || ')')::tbl).*
FROM   (SELECT raw FROM tmp_data OFFSET 1) t;

The string is converted into a row literal, cast to the newly created table row type and decomposed with (row).*.

All done.

You could put all of that into a plpgsql function, but you’d need to safeguard against SQL injection. (There are a number of related solutions here on SO. Try a search.

db<>fiddle here
Old SQL Fiddle

A reproducible exampleAlthough the OP has not provided an example of the JSON response, I have been able to find one in the https://coinmarketcap.com/api/documentation/v1/#operation/getV1CryptocurrencyListingsLatest you’re trying to consult. I reproduce below an example of that JSON so that the answer is reproducible. But I hit him as a single line so he doesn’t take too much.json_response = ‘{«data»: [{«id»: 1,»name»: «Bitcoin»,»symbol»: «BTC»,»slug»: «bitcoin»,»cmc_rank»: 5,»num_market_pairs»: 500,»circulating_supply»: 16950100,»total_supply»: 16950100,»max_supply»: 21000000,»last_updated»: «2018-06-02T22:51:28.209Z»,»date_added»: «2013-04-28T00:00:00.000Z»,»tags»: [«mineable»],»platform»: null,»quote»: {«USD»: {«price»: 9283.92,»volume_24h»: 7155680000,»volume_change_24h»: -0.152774,»percent_change_1h»: -0.152774,»percent_change_24h»: 0.518894,»percent_change_7d»: 0.986573,»market_cap»: 852164659250.2758,»market_cap_dominance»: 51,»fully_diluted_market_cap»: 952835089431.14,»last_updated»: «2018-08-09T22:53:32.000Z»},»BTC»: {«price»: 1,»volume_24h»: 772012,»volume_change_24h»: 0,»percent_change_1h»: 0,»percent_change_24h»: 0,»percent_change_7d»: 0,»market_cap»: 17024600,»market_cap_dominance»: 12,»fully_diluted_market_cap»: 952835089431.14,»last_updated»: «2018-08-09T22:53:32.000Z»}}},{«id»: 1027,»name»: «Ethereum»,»symbol»: «ETH»,»slug»: «ethereum»,»num_market_pairs»: 6360,»circulating_supply»: 16950100,»total_supply»: 16950100,»max_supply»: 21000000,»last_updated»: «2018-06-02T22:51:28.209Z»,»date_added»: «2013-04-28T00:00:00.000Z»,»tags»: [«mineable»],»platform»: null,»quote»: {«USD»: {«price»: 1283.92,»volume_24h»: 7155680000,»volume_change_24h»: -0.152774,»percent_change_1h»: -0.152774,»percent_change_24h»: 0.518894,»percent_change_7d»: 0.986573,»market_cap»: 158055024432,»market_cap_dominance»: 51,»fully_diluted_market_cap»: 952835089431.14,»last_updated»: «2018-08-09T22:53:32.000Z»},»ETH»: {«price»: 1,»volume_24h»: 772012,»volume_change_24h»: -0.152774,»percent_change_1h»: 0,»percent_change_24h»: 0,»percent_change_7d»: 0,»market_cap»: 17024600,»market_cap_dominance»: 12,»fully_diluted_market_cap»: 952835089431.14,»last_updated»: «2018-08-09T22:53:32.000Z»}}}],»status»: {«timestamp»: «2018-06-02T22:51:28.209Z»,»error_code»: 0,»error_message»: «»,»elapsed»: 10,»credit_count»: 1}}’
Suppose this JSON has been received in response to a request requests. Instead of that request, for the answer to be reproducible, I will use json.loads() to load the previous string in a Python dictionary:import json
data = json.loads(json_response)
From here you can reproduce the problem posed by the OP, which is basically that if you try to convert these data into a dataframe like this:coins=data[‘data’]
m=pd.DataFrame(coins)
z=m[[‘symbol’,’quote’]]
Or maybe. z a dataframe containing nesting dictionaries within a column: symbol quote
0 BTC {‘USD’: {‘price’: 9283.92, ‘volume_24h’: 71556…
1 ETH {‘USD’: {‘price’: 1283.92, ‘volume_24h’: 71556…
A solutionIf the dataframe is created in this other way:m = pd.json_normalize(data[«data»])
the result is that all internal dictionaries «dispack» giving rise to a multitude of new columns: id name … quote.ETH.fully_diluted_market_cap quote.ETH.last_updated
0 1 Bitcoin … NaN NaN
1 1027 Ethereum … 9.528351e+11 2018-08-09T22:53:32.000Z
Specifically, for example, column names like quote.USD.price which contains the price converted to USD, or quote.USD.percent_change_1hetc. These were code names in subdivisionaries.We can extract only the columns of interest, and rename them. For example:x = m[[«symbol», «quote.USD.price», «quote.USD.percent_change_1h»]]
x.columns=[«symbol», «price», «percent_change_1h»]
and x We’d already have the dataframe with the: symbol price percent_change_1h
0 BTC 9283.92 -0.152774
1 ETH 1283.92 -0.152774