New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Import CSV into table through GUI #68

Closed
tonyduan opened this Issue Sep 9, 2015 · 72 comments

Comments

Projects
None yet
@tonyduan

tonyduan commented Sep 9, 2015

I've been doing this though PGAdmin for years and would love if this could be supported in Postico.

@aziz

This comment has been minimized.

aziz commented Sep 9, 2015

👍

1 similar comment
@mhkeller

This comment has been minimized.

mhkeller commented Sep 22, 2015

+1

@jakob jakob added the enhancement label Sep 28, 2015

@soldnermike

This comment has been minimized.

soldnermike commented Oct 10, 2015

Please!

@JonasClark

This comment has been minimized.

JonasClark commented Oct 10, 2015

+1

3 similar comments
@wellsoliver

This comment has been minimized.

wellsoliver commented Oct 14, 2015

+1

@SergioEnrique

This comment has been minimized.

SergioEnrique commented Oct 19, 2015

+1

@robertsmolarek

This comment has been minimized.

robertsmolarek commented Oct 22, 2015

+1

@J5Dev

This comment has been minimized.

J5Dev commented Oct 23, 2015

Would also really love to see this, or an export to an insert statement.

@diddeb

This comment has been minimized.

diddeb commented Nov 2, 2015

Eh, +1 and 👍 for this...

@switz

This comment has been minimized.

switz commented Nov 11, 2015

+1

15 similar comments
@trapias

This comment has been minimized.

trapias commented Nov 11, 2015

+1

@astewart-twist

This comment has been minimized.

astewart-twist commented Dec 3, 2015

+1

@kylebeckles

This comment has been minimized.

kylebeckles commented Dec 11, 2015

+1

@gerbsen

This comment has been minimized.

gerbsen commented Jan 4, 2016

+1

@crypticsymbols

This comment has been minimized.

crypticsymbols commented Jan 5, 2016

+1

@tmbull

This comment has been minimized.

tmbull commented Jan 12, 2016

👍

@trentniemeyer

This comment has been minimized.

trentniemeyer commented Mar 21, 2016

+1

@kylesziv

This comment has been minimized.

kylesziv commented Apr 28, 2016

+1

@bza

This comment has been minimized.

bza commented May 17, 2016

+1

@tpanum

This comment has been minimized.

tpanum commented Jun 20, 2016

+1

@meandkareem

This comment has been minimized.

meandkareem commented Jul 14, 2016

+1

@remizorrr

This comment has been minimized.

remizorrr commented Aug 1, 2016

+1

@keitaj

This comment has been minimized.

keitaj commented Aug 4, 2016

👍

@Ricardo-C-Oliveira

This comment has been minimized.

Ricardo-C-Oliveira commented Aug 29, 2016

+1

@Dacascas

This comment has been minimized.

Dacascas commented Sep 5, 2016

+1

@atMari

This comment has been minimized.

atMari commented Feb 3, 2017

+1

2 similar comments
@cronin4392

This comment has been minimized.

cronin4392 commented Feb 22, 2017

+1

@dominijk

This comment has been minimized.

dominijk commented Mar 18, 2017

+1

@jakob

This comment has been minimized.

Owner

jakob commented Nov 28, 2017

@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?
@postico-bot

This comment has been minimized.

postico-bot commented Nov 28, 2017

We have been working on this issue!

  • Added automatic column matching (if names are equal) #68 #305
  • Disable “Import…” in context menu if import is already loaded (chrispysoft)

Please download Build 2056, or check out the list of recent builds.

We would love to hear your thoughts!

@nhaglind

This comment has been minimized.

nhaglind commented Nov 28, 2017

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.

data-type-create-table

@gerbsen

This comment has been minimized.

gerbsen commented Nov 29, 2017

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 :)

@postico-bot

This comment has been minimized.

postico-bot commented Dec 7, 2017

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.

@skozz

This comment has been minimized.

skozz commented Dec 11, 2017

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:

captura de pantalla 2017-12-11 a las 12 49 54

captura de pantalla 2017-12-11 a las 12 49 58

My schema:
captura de pantalla 2017-12-11 a las 12 50 39

What I'm doing wrong? Or is a bug?

Thank you guys!

@postico-bot

This comment has been minimized.

postico-bot commented Dec 11, 2017

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.

@chrispysoft

This comment has been minimized.

Collaborator

chrispysoft commented Dec 11, 2017

@skozz Thanks for your bug report! We've just fixed this - now you can import negative numeric values (e.g. "-0.001").

@skozz

This comment has been minimized.

skozz commented Dec 11, 2017

@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.

@beweinreich

This comment has been minimized.

beweinreich commented Dec 11, 2017

Worked perfect for me with a simple 3 column table 👍 Was able to define each column with the associated data.

@jakob

This comment has been minimized.

Owner

jakob commented Dec 13, 2017

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!

@skozz

This comment has been minimized.

skozz commented Dec 13, 2017

@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 👍

@doanerock

This comment has been minimized.

doanerock commented Dec 29, 2017

Please bring back Database import/export.

@chrispysoft

This comment has been minimized.

Collaborator

chrispysoft commented Jan 16, 2018

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

@postico-bot

This comment has been minimized.

postico-bot commented Jan 16, 2018

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.

@skozz

This comment has been minimized.

skozz commented Jan 17, 2018

@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:
    captura de pantalla 2018-01-17 a las 11 25 29

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.

@jakob

This comment has been minimized.

Owner

jakob commented Jan 17, 2018

@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.

@skozz

This comment has been minimized.

skozz commented Jan 17, 2018

@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?

@doanerock

This comment has been minimized.

doanerock commented Jan 17, 2018

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.

@jonahsmith

This comment has been minimized.

jonahsmith commented Mar 7, 2018

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.

screen shot 2018-03-07 at 8 40 46 am

  • 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.

demo

  • 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.

@nhaglind

This comment has been minimized.

nhaglind commented Mar 8, 2018

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.

@chrispysoft

This comment has been minimized.

Collaborator

chrispysoft commented Mar 15, 2018

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.

@chrispysoft

This comment has been minimized.

Collaborator

chrispysoft commented Mar 20, 2018

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.

@chrispysoft

This comment has been minimized.

Collaborator

chrispysoft commented Apr 24, 2018

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.

@dnaidionov

This comment has been minimized.

dnaidionov commented May 3, 2018

Hi! It's a great feature, and really well executed. However, in 1.3.5 it disappeared from the menu. Is this a bug?
image
image

@jakob

This comment has been minimized.

Owner

jakob commented May 3, 2018

@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/

@jesperronn

This comment has been minimized.

jesperronn commented May 16, 2018

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 :) 👍

@jakob

This comment has been minimized.

Owner

jakob commented Jun 11, 2018

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!

@jakob jakob closed this Jun 11, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment