3 ms·

I wonder if you or the other Postgres users here could give me some tips. What would be the best way to use the data from a huge Excel file into my web apps?

I wonder if you or the other Postgres users here could give me some tips. What would be the best way to use the data from a huge Excel file into my web apps? Currently I'm converting it to CSV and use `BULK INSERT` with MSSQL. I know that with MSSQL I can also use Excel files as "external database" but AFAIK, Excel files are not indexed and are super slow when used as an "external database". I wouldn't mind switching to Postgres. I actually prefer Postgres.

michelpp 7y ago
THe process is similar, you would convert to csv and then use the \copy command to bulk insert the data. In pg 12, the copy command has grown a lot of very useful features like WHERE filtering, so if you get the chance go for 12: https://www.cybertec-postgresql.com/en/tech-preview-improving-copy-and-bulkloading-in-postgresql-12/ https://www.cybertec-postgresql.com/en/tech-preview-improvin...
deleted 7y ago
[deleted]
truth_seeker 7y ago
This link covers it in detail: http://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/ http://www.postgresqltutorial.com/import-csv-file-into-posgr...
tempguy9999 7y ago
As an MSSQL guy who rarely connects it to excel, if your workflow allows it consider pulling the entirety of the excel sheet into a table in the db and working on it just from there. What's huge for excel isn't for MSSQL. As a decades-long user of MSSQL guy who is getting sick of being rpeatedly done over by MS, could you explain why you prefer postgres? That would be incredibly interesting, TIA
- sixdimensional 7y ago
  Not OP but, in my mind (having used both extensively), Postgres is basically the closest I feel you can get to MSSQL in the open source world, in terms of completeness of a fully functional relational database that scales - not to mention Postgres has tons of features that MSSQL has been playing catch up with for years. I like both of them personally. Postgres didn't used to have as strong of a showing on Windows, and not as fancy tools as Management Studio/Query Analyzer/Azure Data Studio, but I think today Postgres is absolutely viable to replace SQL Server if you want to switch to open source.
- brunoqc 7y ago
  For the excel sheet I was wondering if there was a better way since I want to update all the date many times per day to be up-to-date. I thought maybe I was wasting some ressources. > could you explain why you prefer postgres? I don't have many reasons to be honest. Both MSSQL and Postgres are fine I guess but I like that Postgres is open source and so I can use it in personal projects for fun. I guess the thing I hate the most about MSSQL is that, at work, we never have the latest version, because why pay again. And every single time I search how to do something I must use the less optimal way since the new way only works on the latest version. At least with Postgres I can always run the latest version.
  - tempguy9999 7y ago
    I can't help you really but decide where the master data lives - in the spreadsheet or in the DB? Try and move it into the DB, so it needn't touch the spreadsheet unless you want to only look at it in the spreadsheet, never alter it there. DBs handle and manipulate data better far better than excel. Excel does data presentation better. A decent DB, well looked after and backed up, is the safest place for any quantity of data. Spreadsheets are not - excel is not reliable once you start filling it up and doing complex updates. So that's my advice. Of course your needs and workflow may not fit that at all well, so YMMV! Thanks for the opinion on mssql vs postgres.

michelpp 7y ago

THe process is similar, you would convert to csv and then use the \copy command to bulk insert the data. In pg 12, the copy command has grown a lot of very useful features like WHERE filtering, so if you get the chance go for 12: https://www.cybertec-postgresql.com/en/tech-preview-improving-copy-and-bulkloading-in-postgresql-12/ https://www.cybertec-postgresql.com/en/tech-preview-improvin...

deleted 7y ago

[deleted]

truth_seeker 7y ago

This link covers it in detail: http://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/ http://www.postgresqltutorial.com/import-csv-file-into-posgr...

tempguy9999 7y ago

As an MSSQL guy who rarely connects it to excel, if your workflow allows it consider pulling the entirety of the excel sheet into a table in the db and working on it just from there. What's huge for excel isn't for MSSQL. As a decades-long user of MSSQL guy who is getting sick of being rpeatedly done over by MS, could you explain why you prefer postgres? That would be incredibly interesting, TIA

sixdimensional 7y ago
Not OP but, in my mind (having used both extensively), Postgres is basically the closest I feel you can get to MSSQL in the open source world, in terms of completeness of a fully functional relational database that scales - not to mention Postgres has tons of features that MSSQL has been playing catch up with for years. I like both of them personally. Postgres didn't used to have as strong of a showing on Windows, and not as fancy tools as Management Studio/Query Analyzer/Azure Data Studio, but I think today Postgres is absolutely viable to replace SQL Server if you want to switch to open source.
brunoqc 7y ago
For the excel sheet I was wondering if there was a better way since I want to update all the date many times per day to be up-to-date. I thought maybe I was wasting some ressources. > could you explain why you prefer postgres? I don't have many reasons to be honest. Both MSSQL and Postgres are fine I guess but I like that Postgres is open source and so I can use it in personal projects for fun. I guess the thing I hate the most about MSSQL is that, at work, we never have the latest version, because why pay again. And every single time I search how to do something I must use the less optimal way since the new way only works on the latest version. At least with Postgres I can always run the latest version.
- tempguy9999 7y ago
  I can't help you really but decide where the master data lives - in the spreadsheet or in the DB? Try and move it into the DB, so it needn't touch the spreadsheet unless you want to only look at it in the spreadsheet, never alter it there. DBs handle and manipulate data better far better than excel. Excel does data presentation better. A decent DB, well looked after and backed up, is the safest place for any quantity of data. Spreadsheets are not - excel is not reliable once you start filling it up and doing complex updates. So that's my advice. Of course your needs and workflow may not fit that at all well, so YMMV! Thanks for the opinion on mssql vs postgres.