You can change how COPY represents NULL values. For example, assuming the following table: CREATE TABLE hasbool ( id integer PRIMARY KEY, is_cool boolean, name text NOT NULL ); I can use blanks to represent NULL as in the following psql session: \pset null '∅' Null display is "∅". COPY hasbool FROM STDIN (FORMAT 'csv', NULL ' '); Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself, or an EOF signal. 1,TRUE,joe 2, ,paul . COPY 2 TABLE hasbool; id │ is_cool │ name ════╪═════════╪══════ 1 │ t │ joe 2 │ ∅ │ paul (2 rows)

JSON is a layer of encapsulation with a https://www.ecma-international.org/publications-and-standards/standards/ecma-404/ that the JSON field in your input must respect. CSV is another layer of encapsulation that applies on top, with a syntax that is more of less https://www.rfc-archive.org/getrfc?rfc=4180 . As mentioned in the RFC, programs that read or write CSV do not always conform strictly to all these rules, but concerning the quoting rules, Postgres is conformant. JSON uses double quotes around string literals, so all the attempts in the question to use single quotes around strings are invalid for the JSON parser CSV requires that double quotes are doubled, see rules #5 and #7 of rfc 4180, so all attempts to use double quotes in JSON without doubling them for CSV or without enclosing the entire field in double quotes are invalid for the CSV parser. Each field may or may not be enclosed in double quotes (however some programs, such as Microsoft Excel, do not use double quotes at all). If fields are not enclosed with double quotes, then double quotes may not appear inside the fields If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote Your example slightly edited to conform to both CSV and JSON does work: CREATE TABLE posts( id SERIAL PRIMARY KEY, text TEXT NOT NULL, created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, rubrics jsonb NOT NULL ); COPY posts(text, created_date, rubrics) FROM STDIN WITH CSV; foo,2022-05-17,"[""a"", ""b"", ""c""]" . select * from posts; id | text | created_date | rubrics ----+------+---------------------+----------------- 1 | foo | 2022-05-17 00:00:00 | ["a", "b", "c"] If you wanted a Postgres array of JSON objects in rubrics, that would be more complicated, as expressing the Postgres array as text requires another layer of encapsulation with its https://www.postgresql.org/docs/current/arrays.html#ARRAYS-IO , so the production of valid data to import with COPY would have to do JSON quoting -> postgres array quoting -> CSV quoting But as seen in your example, it seems that you don't need a Postgres array of JSON objects as a column, but rather a single JSON value that contains a JSON array.

Scraping data requires programming. That looks like a mixture of CSV and JSON. I think you will have to write code to parse the lines and do INSERTs. While writing the code, decide which things should be left in JSON (complex and/or slow) to search on versus things that should be broken out into other columns or (more likely) other tables. My request for SHOW CREATE TABLE was so I could see what column definitions you had for the data. I now see that it is not a simple CSV. Also, what version of MySQL/MariaDB are you using? Are you sure about just \n for line termination? It might be \r\n. The ignored first row might be interesting; it contains the names they have for the columns. (They don't have to match your column names in the CREATE TABLE.)

Your UPDATE syntax generates a cross join of the table urls with the split result. Which is essentially a cross join of the table with itself. You need to have some kind of join condition between the target table and the source. The obvious choice would be the primary key column of the table. with split as ( select pk_column, regexp_match(url, '(https|http)://(.+)/' ) as parts from urls ) update urls set protocol = s.parts[1], host = s.parts[2] from split s where urls.pk_column = s.pk_column --<< here I think your attempt to avoid evaluating the regex expression twice by using the CTE makes things slower, rather than faster. I would expect the overhead of joining the table with itself is far bigger than the overhead of evaluating the regex twice. So I think, you should also try: update urls set protocol = (regexp_match(url, '(https|http)://(.+)/' ))[1] host = (regexp_match(url, '(https|http)://(.+)/' ))[2]

So you're storing Zip codes in a numeric Data Type. No problem. This should do the trick: select format( zipcode, '00000' ) as zipcode_fmt from ... Of course, if you want the leading zeroes to be in the field permanently, they you need to change the Data Type. Numbers don't have leading zeroes, but the String Representation of those numbers can. In most DBMSs, you'd add a new field, varchar(5), update that field with the format'ted values, as above, then drop the original field and replace it with the new one. In "Dear Little Access", though, you'll probably have to create a whole new table, with the new field, insert the formatted data into it, drop the original table and replace it with the new one.

First your query contains https://dev.mysql.com/doc/refman/8.0/en/keywords.html Condition and Current_date please avoid using reserved words. and secondly you are using SET twice. Try: LOAD DATA LOCAL INFILE 'C:\Users\Shrey\OneDrive\Desktop\assignments\Samyak\Comorbidity Covid-19.csv' INTO TABLE `comorbidity` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 ROWS (@Current_date_var, @Start_Date_var, State, Condition_Group, `Condition`, Age_group, Covid19_deaths, Number_of_mentions) SET `Current_date` = STR_TO_DATE(@Current_date_var, '%m-%d-%Y') , Start_Date = STR_TO_DATE(@Start_Date_var, '%m-%d-%Y') ; https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1850517047b1e27ca4c4f5ed73e81414 Be aware that STR_TO_DATE(@Current_date_var, '%m-%d-%Y') returns the date in the yyyy-mm-dd format, so you should have a date type column. For example: SELECT STR_TO_DATE('02-21-2022', '%m-%d-%Y'); Returns: STR_TO_DATE('02-21-2022', '%m-%d-%Y') 2022-02-21 https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=481b7be3a0cc6056668870921d30bd7c Example with the updated data: mysql> CREATE TABLE `comorbidity` ( `State` varchar(20) NOT NULL, `Condition_Group` varchar(50) NOT NULL, `Condition` varchar(45) NOT NULL, -> `Current_date` date NOT NULL, -> `Start_Date` date NOT NULL, -> `State` varchar(20) NOT NULL, -> `Condition_Group` varchar(50) NOT NULL, -> `Condition` varchar(45) NOT NULL, -> `Age_group` varchar(15) NOT NULL, -> `Covid19_deaths` int NOT NULL, -> `Number_of_mentions` int NOT NULL -> ) ; Query OK, 0 rows affected (0.57 sec) mysql> show variables like '%secure%'; +--------------------------+-----------------------+ | Variable_name | Value | +--------------------------+-----------------------+ | require_secure_transport | OFF | | secure_file_priv | /var/lib/mysql-files/ | +--------------------------+-----------------------+ 2 rows in set (0.00 sec) mysql> mysql> LOAD DATA INFILE '/var/lib/mysql-files/comorbidity.csv' LINES TERMINATED BY '\n' IGNORE 1 ROWS (@Current_date_var, @Start_Date_var, State, Condition_Group, Condition, Age_group, Covid19_deaths, Number_of_mentions) SET Current_date = STR_TO_DATE(@Current_date_var, '%m-%d-%Y') , Start_Date = STR_TO_DATE(@Start_Date_var, '%m-%d-%Y') ; -> IGNORE INTO TABLE `comorbidity` -> FIELDS TERMINATED BY ',' -> LINES TERMINATED BY '\n' -> IGNORE 1 ROWS -> (@Current_date_var, @Start_Date_var, State, Condition_Group, `Condition`, Age_group, Covid19_deaths, Number_of_mentions) -> SET `Current_date` = STR_TO_DATE(@Current_date_var, '%m-%d-%Y') , -> Start_Date = STR_TO_DATE(@Start_Date_var, '%m-%d-%Y') -> ; Query OK, 3 rows affected (0.04 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql> mysql> select * from comorbidity; +--------------+------------+---------------+----------------------+-----------+-----------+----------------+--------------------+ | Current_date | Start_Date | State | Condition_Group | Condition | Age_group | Covid19_deaths | Number_of_mentions | +--------------+------------+---------------+----------------------+-----------+-----------+----------------+--------------------+ | 2022-02-06 | 2020-01-01 | United States | Respiratory diseases | Influenza | 0-24 | 1167 | 1127 | | 2022-02-06 | 2020-01-01 | United States | Respiratory disease | pneumonia | 25-51 | 2254 | 5458 | | 2022-02-06 | 2020-02-02 | United States | Respiratory disease | pneumonia | 52-76 | 5545 | 2222 | +--------------+------------+---------------+----------------------+-----------+-----------+----------------+--------------------+ 3 rows in set (0.00 sec)

Almost every "Table-per-Thing" design is a Bad Idea. You almost always wind up having to join across [all] those tables and having lots of Tables in there makes life very difficult. And, if you ever have to change the table structure, say, to add a new column, you have to run the alter table lots and lots of times. Tedious. You should be looking to load all of these CSVs into a single table, keyed on zipcode, address and timestamp (or some combination(s) thereof, depending on how you need to retrieve this data), plus the data field. create table all_data ( zipcode ... , address ... , timestamp ... , data_value , primary key ( zipcode, address, timestamp ) , index( timestamp, zipcode, address ) ); You don't say what's in that data field, but if it's a big chunk of stuff that you might want to query parts of, consider breaking those parts out into individual fields as well. Yes, you can have "complex" data in columns these days, but there's [still] a cost (performance hit) to doing so. I'd suggest using an intermediate table to do the loading, and do some checks on the data as it comes in, before it gets loaded into the table proper. Even if you had a million such these files, you'd still only be looking at around 8 billion records - and that's nothing to a decently-sized PostgreSQL cluster.

" is the default quotation character in a CSV file. It quotes fields that contains delimiters and cannot be unbalanced Use the QUOTE option to specify a different quote character: COPY num(adsh,tag,version,coreg,ddate,qtrs,uom,value,footnote) FROM 'sample.txt' (QUOTE E'\u0007', DELIMITER E'\t', FORMAT 'csv', HEADER); This assumes that the BEL character (ASCII 7) does not appear in your data.

copy t_jsonb to STDOUT (FORMAT CSV, QUOTE ' ', header true);

create an awk script, eg. /tmp/Max888.awk, containing BEGIN { FS="," C["crate_id"] = 1 C["owner_id"] = 2 C["created_at"] = 3 C["created_by"] = 4 C["owner_kind"] = 5 } NR == 1 { # determine the New Order for (N=1;N 1 { OFS="," # print $1,$2,$3,$4,$5 print $NCO[NO[1]],$NCO[NO[2]],$NCO[NO[3]],$NCO[NO[4]],$NCO[NO[5]] } If your CSV data is contained in the file /tmp/test.dat, and your database in /tmp/test.sq3, you can run sqlite3 -csv /tmp/test.sq3 '.import "|awk -f /tmp/Max888.awk /tmp/test.dat" crate_owners'

As the error message says, the string "id" is not proper to be a UUID. Either remove the header line from the file, or tell \copy that the file has a header line.

That's how I'm realised. with open('projects2.csv', 'w') as csvfile: writer = csv.writer(csvfile) writer.writerow(('Объект', 'Цена', 'Телефон')) writer.writerows( (obekt['title'], obekt['price'], obekt['tel']) for obekt in obekts )

Using a familiar Pandas for you:Reference Excel:In [95]: pd.read_excel(r'D:\temp\.data\a.xlsx') Out[95]: a b c 0 1 2 3 1 11 22 33 2 111 222 333 3 1111 2222 3333 Let's say we're interested in the first column.aIn [96]: pd.read_excel(r'D:\temp\.data\a.xlsx', usecols=[0]).T.to_csv(r'd:/temp/out.csv', header=None, index=False) out.csv:1,11,111,1111 Horse:In [97]: pd.read_excel(r'D:\temp\.data\a.xlsx', usecols=[0]) Out[97]: a 0 1 1 11 2 111 3 1111 In [98]: pd.read_excel(r'D:\temp.data\a.xlsx', usecols=[0]).T Out[98]: 0 1 2 3 a 1 11 111 1111

Add one line in DataFrame easily using df.loc[len(df)] = [...]:In [9]: df Out[9]: ID Name First name Middle name Age Floor Weight Growth Family status 0 1 Name Yrij V 29 2 80 185 married In [10]: df.loc[len(df)] = [61, 'Василий', 'Флешков', 'Капустович', 512,'муж', 111, 121, 'х' ] In [11]: df Out[11]: ID Name First name Middle name Age Floor Weight Growth Family status 0 1 Name Yrij V 29 2 80 185 married 1 61 Василий Флешков Капустович 512 муж 111 121 х but it is much more efficient to build a set of lines and immediately create a ready DataFrame or create a new one and add it to the existing:In [12]: d = [[11, 'Bond', 'James', 'Jnr', 35, 'M', 85, 190, ''], ...: [12, 'Kong', 'King', '', 12, 'M', 2000, 600, ''] ...: ] ...: In [13]: new = pd.DataFrame(d, columns=df.columns) In [14]: new Out[14]: ID Name First name Middle name Age Floor Weight Growth Family status 0 11 Bond James Jnr 35 M 85 190 1 12 Kong King 12 M 2000 600 In [15]: df = df.append(new) In [16]: df Out[16]: ID Name First name Middle name Age Floor Weight Growth Family status 0 1 Name Yrij V 29 2 80 185 married 1 61 Василий Флешков Капустович 512 муж 111 121 х 0 11 Bond James Jnr 35 M 85 190 1 12 Kong King 12 M 2000 600 Well, actually, adding DataFrame to existing CSV file:df.to_csv(r'/path/to/file.csv', mode='a', header=None, index=False) PS I would not consider the CSV file as an OBD file as a maximum as a time storage facility (e.g. for transfer (export/import) to another OBD. Try MySQL / SQLite or HDF (the fast file repository is convenient for processing and retrieval of Pandas)

Well, if there's no minus in the original text, something like that.#include <vector> #include <string> #include <iostream> #include <iomanip> #include <fstream> using namespace std; int main(int argc, const char * argv[]) { ifstream in("input.txt"); ofstream out("output.txt"); string s; while(getline(in,s)) { out << "text "; size_t pos = s.find_first_of("-0123456789"); for(int i = pos, e = s.length(); i < e; ++i ) { if (s[i] == ' ' || s[i] == ',') { while(s[i] == ' ' || s[i] == ',') ++i; out << ' '; } out << s[i] ; } out << endl; } } Worked on a 7000-string test file on my car in about 0.4 seconds.The second option is an experiment -#include <stdio.h> #include <string.h> int main(int argc, const char * argv[]) { FILE * in = fopen("input.txt","rt"); FILE * out = fopen("output.txt","wt"); char buf[1024]; while(fgets(buf,1024,in)) { fputs("text ",out); char * s = strpbrk(buf,"-0123456789"); for(; *s; ++s ) { if (*s == ' ' || *s == ',') { while(*s == ' ' || *s == ',') ++s; fputc(' ',out); } fputc(*s,out); } } fclose(out); fclose(in); } Both are compiled by Visual C++ 2015 64bit. Test file is 700. Average time for 10 launches. The first option is 0.34c, second one is 0.10c.But! Replacement string to work with style C lines:int main(int argc, const char * argv[]) { ifstream in("input.txt"); ofstream out("output.txt"); char buf[1024]; while(in.getline(buf,1024)) { out << "text "; const char * s = strpbrk(buf,"-0123456789"); for(; *s; ++s ) { if (*s == ' ' || *s == ',') { while(*s == ' ' || *s == ',') ++s; out.write(" ",1); } out.write(s,1); } out.write("\n",1); } } It's time to work under the same conditions - 0.19s.Findings:

Let's take care of the cycle.foreach ($_POST as list($filename, $name, $author, $material, $scale, $date, $museum, $city, $notation)) { // ... } It means you're moving the elements of the mass. $_POSTand expecting that each of the elements is also the size and meaning of this body, you're setting the variables. $filename, $name...♪ However, I have a crystal ball telling me that your $_POST looks like:array( 'filename' => array('file1', 'file2', /* ... */), 'name' => array('name1', 'name2', /* ... */), // ... ) So you're at first. list($filename, $name, $author...) set values from $_POST['filename'] (in best case), then $_POST['name']etc.Extract the mass $_POST Function print_r() And think about it. I need you to. writeCompletedI'd do something like that:$fields = array( 'filename', 'name', 'author', 'material', 'scale', 'date', 'museum', 'city', 'notation' ); for ($rowIndex = 0; ; $rowIndex += 1) { $row = array(); foreach ($fields as $field) { if (isset($_POST[$field][$rowIndex])) { $row[] = $_POST[$field][$rowIndex]; } else { break 2; } } fputcsv($csv, $row); }

Unfortunately, no, virtually impossible. In very few cases, PDF can keep some structured information on the tables - if I don't change my memory, it is possible, for example, to keep the AI file as PDF without killing the structure (text/tables), but usually pdf is rented in a press-readable vector+text (which makes it difficult to edit and when copying from PDF, there is no general decision OCI).

Try using NumPy:import numpy as np a = np.asarray(f) usecols = np.argwhere(a==1).reshape(-1,) + 1 I don't know.In [30]: f = [1, 1, 0, 0, 1, 0, 1, 1] In [31]: a = np.asarray(f) In [32]: a Out[32]: array([1, 1, 0, 0, 1, 0, 1, 1]) In [33]: np.argwhere(a==1).reshape(-1,) Out[33]: array([0, 1, 4, 6, 7], dtype=int64) or following the logic of your program.if f[0]==1: s.append(1)] (i.e. with a change per unit):In [34]: np.argwhere(a==1).reshape(-1,) + 1 Out[34]: array([1, 2, 5, 7, 8], dtype=int64) PS usecols shall contain either the number of columns in the form of whole numbers (from scratch) or the names of the poles in the form of lines. ♪ ♪

Use the method. http://pandas.pydata.org/pandas-docs/stable/indexing.html#the-query-method-experimental :data = pd.read_csv('file.csv', sep=';', decimal=',', quotechar="'").query('seller != buyer') If you need to keep it back in CSV:data.to_csv('output.csv', index=False) PS you don't need to transpose DataFrame to filter it.PPS if you use Pandas, try not to use for loop - it's not very effective.Here's a working example. Considering that your CSV uses a quadrant quote ':CSV file - D:\temp\buyer_seller.csv:'id';'seller';'buyer';'timestamp' 1;seller-1;buyer-1;2016-01-01 2;seller-2;buyer-2;2016-01-02 3;same-1;same-1;2016-01-11 4;same-2;same-2;2016-01-22 Code:In [21]: pd.read_csv(r'D:\temp\buyer_seller.csv', sep=';') Out[21]: 'id' 'seller' 'buyer' 'timestamp' 0 1 seller-1 buyer-1 2016-01-01 1 2 seller-2 buyer-2 2016-01-02 2 3 same-1 same-1 2016-01-11 3 4 same-2 same-2 2016-01-22 In [22]: pd.read_csv(r'D:\temp\buyer_seller.csv', sep=';', quotechar="'") Out[22]: id seller buyer timestamp 0 1 seller-1 buyer-1 2016-01-01 1 2 seller-2 buyer-2 2016-01-02 2 3 same-1 same-1 2016-01-11 3 4 same-2 same-2 2016-01-22 In [23]: pd.read_csv(r'D:\temp\buyer_seller.csv', sep=';', quotechar="'").query('seller != buyer') Out[23]: id seller buyer timestamp 0 1 seller-1 buyer-1 2016-01-01 1 2 seller-2 buyer-2 2016-01-02 Alternatively, you can just get rid of the rubber in the names of the poles/colon:In [27]: df = pd.read_csv(r'D:\temp\buyer_seller.csv', sep=';') In [28]: df.columns.tolist() Out[28]: ["'id'", "'seller'", "'buyer'", "'timestamp'"] In [30]: df.columns = df.columns.str.replace("'", '') In [31]: df.columns.tolist() Out[31]: ['id', 'seller', 'buyer', 'timestamp']

UPDATE:D:\temp\price-bedrooms-addiction.png: This is a working example:import numpy as np import pandas as pd import matplotlib.pyplot as plt import matplotlib matplotlib.style.use('ggplot') т.к. вы не предоставили примера CSV воспользуемся случайными данными... df = pd.DataFrame(np.random.randint(0,100,(5, 20)),columns=['p{}'.format(i) for i in range(1,21)]) df['price'] = np.random.randint(105, 107, 5) for c in df.columns.difference(['price']).tolist(): df.set_index(c)['price'].plot.bar(stacked=True) plt.savefig('d:/temp/price-{}-addiction.png'.format(c)) Results:D:\temp\price-p1-addiction.png: D:\temp\price-p2-addiction.png: D:\temp\price-p3-addiction.png: etc.If you want to see price dependence on all parameters on the same schedule:In [98]: df.set_index('price').plot.bar(rot=0, stacked=True, figsize=(16, 12)) Out[98]: <matplotlib.axes._subplots.AxesSubplot at 0x932eb70>