Databases
StackOverflow2013
Postsdbo
POCreate SQL table with correct column types from CSV
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCreate SQL table with correct column types from CSV
    primarykey
    data
    text
    <p>I've looked a a number of questions on this site and cannot find an answer to the question: How to create <strong>multiple NEW tables</strong> in a database (in my case I am using PostgreSQL) <strong>from multiple CSV source files</strong>, where the new database table columns accurately reflect the data within the CSV columns?</p> <p>I can write the CREATE TABLE syntax just fine, and I can read the rows/values of a CSV file(s), but does a method already exist to inspect the CSV file(s) and accurately determine the column type? Before I build my own, I wanted to check if this already existed.</p> <p>If it doesn't exist already, my idea would be to use Python, CSV module, and psycopg2 module to build a python script that would:</p> <ol> <li>Read the CSV file(s).</li> <li>Based upon a subset of records (10-100 rows?), iteratively inspect each column of each row to automatically determine the right column type of the data in the CSV. Therefore, if row 1, column A had a value of 12345 (int), but row 2 of column A had a value of ABC (varchar), the system would automatically determine it should be a format varchar(5) based upon the combination of the data it found in the first two passes. This process could go on as many times as the user felt necessary to determine the likely type and size of the column.</li> <li>Build the CREATE TABLE query as defined by the column inspection of the CSV.</li> <li>Execute the create table query.</li> <li>Load the data into the new table.</li> </ol> <p>Does a tool like this already exist within either SQL, PostgreSQL, Python, or is there another application I should be be using to accomplish this (similar to pgAdmin3)?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRyanDalton
    1. USRyanDalton
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCreate SQL table with correct column types from CSV
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POCreate SQL table with correct column types from CSV
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POCreate SQL table with correct column types from CSV
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAre you trying to automate the creation of tables like this to account for multiple CSV sources? Each source will have its own table? Otherwise I'd think the best data inspection device would be the Mark I Eyeball.
      singulars
      1. POCreate SQL table with correct column types from CSV
      1. USAdrian J. Moreno
    2. COKungFoo is right. The only other option in SQL would be the import/export wizard and using the "Suggest Types" button, it will take a sample and do its best to figure out what columns should be what.
      singulars
      1. POCreate SQL table with correct column types from CSV
      1. USKris Gruttemeyer
    3. COMost programs that deal with the import of data into a database have suggest-types implementation of this. If you're going to write this yourself, I suggest looking into how excel/access/tableau/etc do this. (tableau looks at the first 16 lines of a file to determine the type.)
      singulars
      1. POCreate SQL table with correct column types from CSV
      1. USkreativitea
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload