Import CSV into table through GUI
Closed this issue · 73 comments
I've been doing this though PGAdmin for years and would love if this could be supported in Postico.
👍
+1
Please!
+1
+1
+1
+1
Would also really love to see this, or an export to an insert statement.
Eh, +1 and 👍 for this...
+1
+1
+1
+1
+1
+1
👍
+1
+1
+1
+1
+1
+1
👍
+1
+1
+1
+1
+1
+1
+1
+1
+2
+1
+1
+1
a little bit more context. I have local databases for development and remote databases for staging or production. It would be amazing to be able to easily transfer data from one database to another with Postico. This feature seams to be the only thing needed to be able to do this as we can already export the data.
Of course, some kind of dialog window that would allow custom data transfer between two databases would be amazing but short of that, the ability to import CSV already exported by Postico should suffice.
+1
+1 Please. Please.
+1
+1
Hi! We just released the first public development build of the all new CSV import feature. Now you can easily import CSV files to you databases in a quick and intuitive way using a modern UI. Please let us know how it works for you!
You can download build 2043 from
https://eggerapps.at/postico/builds/
😊
We have been working on this issue!
Close OverlayView after successful import #68 #305 (chrispysoft)
Please download Build 2044, or check out the list of recent builds.
We would love to hear your thoughts!
We have been working on this issue!
Fixed UI bug in OverlayView, improved error reporting #68 #305 (chrispysoft)
Please download Build 2048, or check out the list of recent builds.
We would love to hear your thoughts!
We have been working on this issue!
Please download Build 2052, or check out the list of recent builds.
We would love to hear your thoughts!
Amazing work on this! Really enjoy the intuitive UI design. The ability to either select by drop down or drag columns around is great. Nice execution on the preview of what's being imported and how while including errors and allowing those to be fixed without having to do another import.
Well done, this seems very thought out. It's quite performant too (been doing tests of 100k rows).
Minor suggestions and maybe move these to a more appropriate feature thread regarding CSV imports as some aren't issues but it's related.
- Enable ctrl/shift + click to select multiple files for import
- column matching (auto mapping if names are similar)
- (super nice to have but likely difficult to implement) create table from import*
@nhaglind This is the best kind of feedback! Made all of us very happy.
- Column name matching is something we want to add and we've already experimented with, but we're not sure yet how to handle the case when only some column names match.
- CREATE TABLE from import would be really convenient, but it needs a more complex UI, so we've decided to focus on importing into existing tables for now
- Could you explain your use case for selecting multiple CSV files for import? Do you have data split up into multiple files? Or do you want to import data for multiple tables at once?
We have been working on this issue!
Please download Build 2056, or check out the list of recent builds.
We would love to hear your thoughts!
Yay!
Column name matching is something we want to add and we've already experimented with, but we're not sure yet how to handle the case when only some column names match.
A potential solution that might work, is after you've found matching columns is to loop through the remaining columns that don't. That way at least some of the work is done if not all.
Could you explain your use case for selecting multiple CSV files for import? Do you have data split up into multiple files? Or do you want to import data for multiple tables at once?
Sure, like you mentioned the use case is sometimes CSVs are too big to work or for whatever reason split up into multiple files but retain the same structure (all have headers, all have the same columns). Or you're just compiling datasets in a folder and this way you can group them at once.
CREATE TABLE from import would be really convenient, but it needs a more complex UI, so we've decided to focus on importing into existing tables for now
Agreed, this might be a bit of an undertaking as you would likely have to come up with a nice interface for picking data types as well as potential automated guessing. Though, the way you handled import I think you could tweak that slightly for CREATE. I attached a quick sketch mockup (sorry if's a little rough) of something that maybe could utilize that white space at the bottom.
Again, great work guys.
FYI the panda library can do imports from csv AND create the table, might worth a look:
df = pd.read_csv(DATA_DIR + "some.csv", comment="#", skipinitialspace=True, delimiter="\t", encoding = 'utf8', na_values=['x'])
engine.execute("DROP TABLE IF EXISTS some_table CASCADE;")
sql = df.to_sql('some_table', engine);
Otherwise, pretty cool :)
We have been working on this issue!
Committed by jakob:
Merged autocomplete and CSV import branches.This is the first public build that includes both the new Autocomplete feature (#52) and the CSV Import feature (#68 #305)
Download Build 2105, or check out the list of recent builds.
Guys, really good job, but I think I just found a bug, I'm importing (after export it with Postico) coordinates and I'm receiving the following error with latitudes:
My schema:
What I'm doing wrong? Or is a bug?
Thank you guys!
We have been working on this issue!
Committed by chrispysoft:
Fixed importing signed numeric values with plus prefix (e.g. “+1.0”)
(#68)
Download Build 2108, or check out the list of recent builds.
@skozz Thanks for your bug report! We've just fixed this - now you can import negative numeric values (e.g. "-0.001").
@chrispysoft that was really fast! I'm importing right now and is working! there is another error, but I'm debugging to be sure that is a bug before reporting.
Thank you for your great job.
Worked perfect for me with a simple 3 column table 👍 Was able to define each column with the associated data.
Thanks for your feedback everyone!
It looks like the import feature basically works, and most of you seem to like it. However, we've also had some other people test the feature, and discovered that some people didn't find it very intuitive. They had trouble understanding what they should do (eg. change CSV settings, or rearrange columns).
The top priority for Postico is that it should be easy to use, so we've decided not to release the CSV import feature in its current state.
We're going to try a different approach now. I'll let you know as soon as we have something to test!
@jakob it's a pity but you're right. Anyways, your current exporter/importer (build 2108) really saved my neck this week.
Let us know when you need more testing for the new approach 👍
Please bring back Database import/export.
Hi! I just uploaded a brand new development build of the CSV import feature. We completely redesigned the UI and hope that it is more clear now.
Please let us know how it works for you!
You can download build 2256 from
https://eggerapps-downloads.s3.eu-west-1.amazonaws.com/postico-2256.zip
We have been working on this issue!
jakob: Load Nib after model is set up to fix crash on 10.12 #68
Download Build B2258, or check out the list of recent builds.
@chrispysoft thank you! I just tested And here my output:
- I have exported, locally, a 20GB table successfully unless than 1h. That's really nice.
- I'm importing that file into a remote DB, after 4 hours have imported 5,9GB of 20GB. I think that's normal because is a huge file and the database is remotely, but I'm sharing the info just in case.
- Sometimes my machine goes hight of memory because of the Postico process when importing. Here an example:
But just sometimes during the process, is not blocked so I think that is not a big deal. But just in case.
Thank you very much, this feature is simplifying my life a lot.
@skozz Thanks a lot for the feedback. Postico really shouldn't use so much RAM. Does the CSV file have long lines (>1MB), or is it just very many short lines? I'm curious about finding a way to reproduce this issue.
@jakob I just checked the file and each row is between 7k and 20kb. The hight memory issue was random, I would say that another reason could be that is a remote database so probably there is a kind of lag o reconnections?
I have had issues similar to what you are describing just using scp to copy large files to remote servers. Try copying the same file using scp to the server and see if you have similar issues.
Just adding my two cents here: I am running B2258 and think this feature is really well executed. I find the interface is really intuitive and absolutely love that you can map CSV columns to database columns; that's the right way to do this. I also like that you can change the CSV settings "on the fly" while viewing the preview, to make sure you've selected the right ones. Some specific comments:
- You may be aware of this already, but on systems showing the scrollbars, the drop down column selectors are obscured.
- With particularly wide tables, if you scroll to the right and select a new target column, the view snaps back to the left-most position.
- I personally don't see a need for so many preview rows. Two or three would be sufficient for me, and more seems a bit unnecessary and takes up a lot of vertical space. (Of course, I have no real problem with seeing more, just a comment.)
Let me know if I can clarify any of these comments.
Same issues with the above post. Also wondering if we could allow for shift+click to import multiple CSVs at once (same format and same headers). This is shaping up to be a really useful tool.
Hi!
We have a new development build of the CSV import feature. Please let us know your experience!
- support for wide rows (thanks to @jonahsmith )
- changing popups won't scroll the table view
- faster loading of preview data
Download Build B2472, or check out the list of recent builds.
Hi! 😃 We've just fixed some bugs in the development build of the new CSV import feature:
- faster preview data loading and validation
- smart table column autosizing
- support for long header names and many columns
- tested with slow connections
- present errors as sheets
Download Build B2498, or check out the list of recent builds.
Hi! We have a new development build of the CSV import feature:
- fixed UI bug when using separators other than comma
- automatically match source/target columns by order or name
- reset matched columns
- apply matched columns of previous imports
- display correct number of imported columns
- save and restore last csv setting (encoding, quote character, etc.)
Please let us know how it works for you!
Download Build B2627, or check out the list of recent builds.
Hi! It's a great feature, and really well executed. However, in 1.3.5 it disappeared from the menu. Is this a bug?
@dnaidionov CSV import is not officially released yet. We still need to finish testing it, and it will probably be in Postico 1.4.
If you want to use it now, you have to use the prerelease version:
https://eggerapps.at/postico/builds/
This worked fine in my usecase. Tried build 2627.
Suggestion: Default "do not import" for id column.
One thing: All columns are marked "do not import". I would like a better default:
- For all columns named
id, then "do not import" - For all other columns, import into their names
However, even in the state I tried it, I think it could be merged into master :) 👍
CSV Import is finally released! You can download Postico 1.4 from our website right now, and it'll hit the App Store within a few days.
@jesperronn We now have a way to match columns by name. But I don't want to do any special magic for id columns, since that would make Postico less predictable. Yes, it would sometimes be convenient, but if you accidentally miss that the id column needs to be selected, you might lose data....
Thanks everyone for your suggestion! It was a long journey to get this feature done, but now it's done, and I can finally close this issue.
If anyone has any further suggestions / ideas, please open a new issue!
Importing table by table with csv functionality is not really feasible for dbs with many tables. It would be great to see a sql dump export to a .sql file and to be able to read the dump with an import functionality.