Right, if the import fails, auto-populate the schema/table fields the same values again. Let me do a bit of investigation on the best way to add an option for this (I think I would like to have it set as an option). It would also be nice to set all column names to lower case, since PostgreSQL is case sensitive. Could you explain this one a bit further in detail? Ready for next build (via right click context menu)Ĭould the file be pulled from its original path each time, so that external edits are included on subsequent retries? For the first draft, I might hide it being a right click. Not having to determine length will be a good boost.Ĭould there be an option to toggle all/none for 'Allow Null'? Obviously I could go through and check each one, but that can be tedious with higher column counts. I'll switch Postgres to default to 'TEXT' which will also improve the performance of item 4 (scanning rows) as I have a time based scan currently (scan rows until finished or until x seconds have passed). Would be nice if SQLPro just used the text type and didn't worry about length.

Just trying to understand the workflow - this would be for if the import fails? The import dialog would stay open, it would just use the same table again? I think this would tie in with item 3 (drop/recreate the table).įor PostgreSQL specifically, the 'text' type is variable unlimited length and has no performance disadvantages compared to character varying(x). If I right click the 'Table' folder within a schema to select Import CSV, could the schema auto-populate.Īfter picking that schema and table name once, could those be preserved on each retry if the import hits an error? Thank you for so many details! I'm going to focus on a few items here at a time: It would also be nice to set all column names to lower case, since PostgreSQL is case sensitive.Could the file be pulled from its original path each time, so that external edits are included on subsequent retries?.Could there be an option to toggle all/none for 'Allow Null'? Obviously I could go through and check each one, but that can be tedious with higher column counts.For PostgreSQL specifically, the 'text' type is variable unlimited length and has no performance disadvantages compared to character varying(x).Would rather wait and only have to try once. If only a subset of rows are scanned to determine type/null, it would be nice to have an option to scan all.I imagine this doesn't apply for all use cases, but could there be on option to rollback/drop the table if any error is encountered?.After picking that schema and table name once, could those be preserved on each retry if the import hits an error?.If I right click the 'Table' folder within a schema to select Import CSV, could the schema auto-populate when naming the new table being created, rather than defaulting to the public schema each time?.I have a few suggestions that I'm hoping are quicker paths than some grand field type parser. However, it's a very frustrating process each time. I know this is a 'nice to have' feature and SQLPro will never be able to 'magically' guess the table structure perfectly because csv is such a messy format.

Because of this, I need to close the csv import window after 11 tries and start over. Instead, it seems to preserve a copy of the original file which it uses on each retry. However, it appears that the edited csv is not loaded on the next retry. Can't fault SQLPro for that, so I edit the file and fix the error. In addition, SQLPro does not drop the table on error, so I'm creating partially imported tables on each retry.Īfter 10 iterations of the above on this file, it encountered a value in a cell that contained a line break (no quotes in this file). Because of this, I need to retry over and over by guessing which of the many 'character varying(1)' fields might have caused the error and lengthen the field, allow null as it hit an issue for each one it didn't catch, change integer to bigint or decimal in some cases, etc.

For larger files, it seems to get this wrong most of the time. SQLPro seems to scan a subset of the rows and tries to guess int vs decimal vs character varying, and also tries to guess the length of the varchar and if the column allows nulls. Uploading a csv with >47k rows and 94 columns into PostgreSQL 12.3 (RDS) and creating a new table.

Sqlpro csv import mapping