[Good By Excel] python script to generate sql to convert csv to table

Goodbye excel

I hate the application called Excel.

You can read 10,000 lines of csv into Excel, filter it, drag a function to copy it, and all the troublesome things are done. There are times when it takes a long time to load, and it doesn't work on a mac, so on the day when it freezes, I'm not motivated and will leave early.

You're going crazy! !! !!

If you are an engineer, you want to finish everything from data processing / extraction from csv import to ftp server upload from export with just a command! Excel is an evil way of evil! Reliable database! !! !!

The point is, if you have a script that creates a create table statement from csv and batches up to load data, Excel is goodbye without even saying good by. Such a python script is today's theme.

Commentary

Since python has a pip that guesses the type from csv called csv_to_table and creates a create table statement with the header part of the first line as the column name, we created a script that adds the function to create up to the load data statement this time. I did.

I wrote how to use it on github, so please execute it as it is w

https://github.com/ShusukeOtani/table_stmt_generator_from_csv

gitclone


git clone https://github.com/ShusukeOtani/table_stmt_generator_from_csv

Install pip and pyenv and run it with python2.7.1.

Dependent pip installation


pip install -r requirements.txt

Prepare -d, -t, and -p as optional arguments. -d is an option to determine the type of database. -d m is mysql, -d p is postgre. The default value is mysql. -t is a bool whether to make it a Temp table. If you cut it, make it a permament table.

You can specify the primary key with -p. If -p id, the column id will be PK.

I will write an example. First of all, there is no primary key, permament table, option is not required for mysql, so simply specify the file path of csv.

exmaple1


python execute.py ./example/test.csv

result1


DROP TABLE IF EXISTS table_stmt_generator_from; CREATE TABLE table_stmt_generator_from (
	id smallint,
	pref text
);LOAD DATA LOCAL INFILE '/Users/200302/table_stmt_generator_from_csv/example/test.csv' INTO TABLE table_stmt_generator_from FIELDS TERMINATED BY ','  IGNORE 1 LINES;

If you want to use id column for postgre, temp table and pk, add the following option.

example2


`python execute.py -d p -t -p id ./example/test.csv  

result2


DROP TABLE IF EXISTS test; CREATE TEMPORARY TABLE test (
	id smallint,
	pref text
, PRIMARY KEY(id));COPY test FROM '/Users/200302/table_stmt_generator_from_csv/example/test.csv' CSV HEADER;

Later talk

One of the jobs of advertising is to create a feed. We create an optimized case list feed for vendors such as Criteo and Indeed. You can't join when you go to see multiple DBs. I wrote this script to solve this problem. In actual operation, this script is used to make an application that performs the following series.

  1. Issue a select statement in each DB.
  2. Export the result as csv.
  3. Use this script to import csv into your local DB and generate a table.
  4. JOIN in the local DB to generate the final feed data.
  5. Upload the feed data to the ftp server.

Recommended Posts

[Good By Excel] python script to generate sql to convert csv to table
[Python] Convert PDF text to CSV page by page (2/24 postscript)
How to save a table scraped by python to csv
[python] Copy script to generate copy log
[Python] How to convert db file to csv
[Python] Convert csv file delimiters to tab delimiters
Convert Excel data to JSON with python
[Python] Script useful for Excel / csv processing
I want to convert a table converted to PDF in Python back to CSV
Python script that outputs all records of Oracle table to CSV file
Convert "number" of excel date to python datetime
Python script to convert latitude / longitude to mesh code
[Python] Continued-Convert PDF text to CSV page by page
(Miscellaneous notes) Data update pattern from CSV data acquisition / processing to Excel by Python
How to convert JSON file to CSV file with Python Pandas
Convert json to excel
How to convert Json file to CSV format or EXCEL format
Python script to create a JSON file from a CSV file
A python script that converts Oracle Database data to csv
Convert Excel file to text in Python for diff purposes
[python] Convert date to string
Convert numpy int64 to python int
[Python] Convert list to Pandas [Pandas]
Convert Scratch project to Python
Convert SDF to CSV quickly
Convert python 3.x code to python 2.x
[Python] Convert CSV file uploaded to S3 to JSON file with AWS Lambda
After calling the Shell file on Python, convert CSV to Parquet.
Function to convert Excel column to number
Convert PDF to Documents by OCR
Convert markdown to PDF in Python
[Python] Write to csv file with Python
Save pandas.DataFrame to Excel by sheet
Output to csv file with Python
Workflow to convert formula (image) to python
Convert list to DataFrame with python
python sql statement extracted by time
Python> list> Convert double list to single list
Handle Excel CSV files with Python
[Python] Convert natural numbers to ordinal numbers
Convert decimal numbers to n-ary numbers [python]
Python> tuple> Convert double tuple to single tuple
I made a plugin to generate Markdown table from csv in Vim
Convert XML document stored in XML database (BaseX) to CSV format (using Python)
Preprocessing with Python. Convert Nico Nico Douga tag search results to CSV format