NeoBookDBPro importing CSV into Access - slow "Not Responding"
Quote from Mark Waples on February 25, 2020, 12:34 pmHi,
is there any way of speeding up the import csv action?
I have 80,000 records that I wish to import and the app when importing shows "Not Responding" and I get the blue circle spinning.
The database is a standard Access mbd file
CPU shows around 25% and the memory used is in the region of 95,500k and climbing - which to me may mean a memory leak.
Any guidance would be appreciated.
Thank you
Hi,
is there any way of speeding up the import csv action?
I have 80,000 records that I wish to import and the app when importing shows "Not Responding" and I get the blue circle spinning.
The database is a standard Access mbd file
CPU shows around 25% and the memory used is in the region of 95,500k and climbing - which to me may mean a memory leak.
Any guidance would be appreciated.
Thank you
Quote from dglojnar on February 25, 2020, 3:19 pmHi,
I just try similar import, a litle bit bigger (15.000.000 record, just for fun) and you're right, CSV is VERY slow:-)
But...
Here's the link for very good and fast CSV to MDB converter https://www.doogal.co.uk/CsvToMdb.php
Syntax is trivial - CSVtoMDB CSV_filename.csv
Example: CSVtoMDB test1.csv
Some important things:
- delimiter MUST be comma (,), not (;) !
- data must be NOT quoted with ("") !
Test CSV file (approx. 190 MB in size) was converted in MDB around 15 minutes (don't Forget, it's 15.000.000 records in 14 fields)
Hope this help:-)
Cheers!
Hi,
I just try similar import, a litle bit bigger (15.000.000 record, just for fun) and you're right, CSV is VERY slow:-)
But...
Here's the link for very good and fast CSV to MDB converter https://www.doogal.co.uk/CsvToMdb.php
Syntax is trivial - CSVtoMDB CSV_filename.csv
Example: CSVtoMDB test1.csv
Some important things:
- delimiter MUST be comma (,), not (;) !
- data must be NOT quoted with ("") !
Test CSV file (approx. 190 MB in size) was converted in MDB around 15 minutes (don't Forget, it's 15.000.000 records in 14 fields)
Hope this help:-)
Cheers!
Quote from Mark Waples on February 25, 2020, 4:09 pm@dglojnar
thank you for the heads up - unfortunately I do not require a KEY in the database as it's just for reporting on data and I do not use the functionality of database "per se"
I tried an alternative application and that imports beautifully but it was only a trial version.
I cannot find an sqlite plugin that has an import csv function, otherwise I would give that a try.
:(
thank you for the heads up - unfortunately I do not require a KEY in the database as it's just for reporting on data and I do not use the functionality of database "per se"
I tried an alternative application and that imports beautifully but it was only a trial version.
I cannot find an sqlite plugin that has an import csv function, otherwise I would give that a try.
:(
Quote from dglojnar on February 25, 2020, 4:40 pmYou don't have to define KEY, when you enter:
CSVtoMDB CSVFile.csv (without any further parameters)
the program does not create any key, I was also messed up with this:-)
About SQLite plug-in, I have at home couple of this, give me couple of hours (I'm at work now) .
Also, you can look http://www.alexnolan.net/software/, a Freeware MDB Viewer etc...with Import functions, very good:-)
Cheers!
You don't have to define KEY, when you enter:
CSVtoMDB CSVFile.csv (without any further parameters)
the program does not create any key, I was also messed up with this:-)
About SQLite plug-in, I have at home couple of this, give me couple of hours (I'm at work now) .
Also, you can look http://www.alexnolan.net/software/, a Freeware MDB Viewer etc...with Import functions, very good:-)
Cheers!
Quote from Gaev on February 25, 2020, 5:54 pm@dglojnar
Hope you don't mind my hijacking this topic but ...
Also, you can look http://www.alexnolan.net/software/, a Freeware MDB Viewer etc...with Import functions, very good:-)
... could be a solution for a former client who needs to store about 400,000 records (downloaded from a cloud platform in csv format) ... can you post your thoughts on how well this utility can handle this many records (about 20 fields) ... if need be, the records can be split into multiple Tables (by Region; each region no more than 100,000 records) ... these are archived records and are expected to be accessed very occasionally (mostly search by name or email address fields) ... thank you for sharing your thoughts.
Hope you don't mind my hijacking this topic but ...
Also, you can look http://www.alexnolan.net/software/, a Freeware MDB Viewer etc...with Import functions, very good:-)
... could be a solution for a former client who needs to store about 400,000 records (downloaded from a cloud platform in csv format) ... can you post your thoughts on how well this utility can handle this many records (about 20 fields) ... if need be, the records can be split into multiple Tables (by Region; each region no more than 100,000 records) ... these are archived records and are expected to be accessed very occasionally (mostly search by name or email address fields) ... thank you for sharing your thoughts.
Quote from dglojnar on February 25, 2020, 8:35 pm@gaev
"Hope you don't mind my hijacking this topic but ..."
Not at all, I really appreciate you opinion and contribution to our comunitiý:-)
About MDB Viewer....
I was imported today cca. 190GB CSV file with 14 fields (text fields) using MDB Viewer - no problem at all, time for doing this was about 25-30 minutes (yes, this command line tools is faster...)
After that I was playing a little bit with searching, making filters etc,,,,no problem, very stabile, portable and no dependencies:-)
Scrolling through the grid was smooth and fast, I was really impressed - don't forget - 15 million records in 14 fields, quite a lot, he:-)
Program have all options you need for manipulating MDB files - create, define relationships, duplicate tables, import, export, very good SQL editor, export to many formats, reporting possibilities (actually basic reporting based on query and export as PDF, I didn't try at all...)
Nice GUI, possibilities to create some simple forms...
For your former client and his task I think this is a perfect solution - small portable app (need MDAC, but this is installed with Windows anyway), stabile, fast.
One small warning - this is NOT tool for beginners, presume is that user have some basic/intermediate knowledge and experience with databases and SQL.
Long story short - very good program, I use it for quite a time for bussines and private jobs without any problems.
Of course, with Neobook and DBPro - perfect companion:-)
Installed on Windows 10 64-bit, Windows 7 SP1 32/64 bit (VMware).
For any questions do not hesitate to ask me :-)
Cheers!
"Hope you don't mind my hijacking this topic but ..."
Not at all, I really appreciate you opinion and contribution to our comunitiý:-)
About MDB Viewer....
I was imported today cca. 190GB CSV file with 14 fields (text fields) using MDB Viewer - no problem at all, time for doing this was about 25-30 minutes (yes, this command line tools is faster...)
After that I was playing a little bit with searching, making filters etc,,,,no problem, very stabile, portable and no dependencies:-)
Scrolling through the grid was smooth and fast, I was really impressed - don't forget - 15 million records in 14 fields, quite a lot, he:-)
Program have all options you need for manipulating MDB files - create, define relationships, duplicate tables, import, export, very good SQL editor, export to many formats, reporting possibilities (actually basic reporting based on query and export as PDF, I didn't try at all...)
Nice GUI, possibilities to create some simple forms...
For your former client and his task I think this is a perfect solution - small portable app (need MDAC, but this is installed with Windows anyway), stabile, fast.
One small warning - this is NOT tool for beginners, presume is that user have some basic/intermediate knowledge and experience with databases and SQL.
Long story short - very good program, I use it for quite a time for bussines and private jobs without any problems.
Of course, with Neobook and DBPro - perfect companion:-)
Installed on Windows 10 64-bit, Windows 7 SP1 32/64 bit (VMware).
For any questions do not hesitate to ask me :-)
Cheers!
Quote from Mark Waples on February 25, 2020, 10:56 pmHi @dglojnar
thank you for the heads up on the MDB Viewer - very good - the problem that I have is that the application that I have created needs to be automated as it is used by several persons and none of which are technically skilled enough to perform database imports.
VisualNeoDB needs to be able to handle large amounts of data when importing csv files and it doesn't handle it very well at all :(
I am pretty well stuffed on this one - sadly!
I am staring to look at ways of performing an import via sql queries or even VBscript - desperation has set in!
Mark
Hi @dglojnar
thank you for the heads up on the MDB Viewer - very good - the problem that I have is that the application that I have created needs to be automated as it is used by several persons and none of which are technically skilled enough to perform database imports.
VisualNeoDB needs to be able to handle large amounts of data when importing csv files and it doesn't handle it very well at all :(
I am pretty well stuffed on this one - sadly!
I am staring to look at ways of performing an import via sql queries or even VBscript - desperation has set in!
Mark
Quote from dglojnar on February 26, 2020, 9:43 amHi @Mark Waples, no need for desperation:-)
With SQL command COPY you can easily import CSV file to your database.
As DBPro support Standard SQL commands I think you can make better result then with Import function.
And you can automated complete process...
Anyway, I can try this solution and let you know:-)
Cheers!
Hi @Mark Waples, no need for desperation:-)
With SQL command COPY you can easily import CSV file to your database.
As DBPro support Standard SQL commands I think you can make better result then with Import function.
And you can automated complete process...
Anyway, I can try this solution and let you know:-)
Cheers!
Quote from Mark Waples on February 26, 2020, 12:38 pm@luishp
Can you please take a look at the NeoDBPro plugin "dbpImportFromCSV" to see how this can be improved when handling large imports.
Or can you please offer an alternative solution within VisualNeoWin, as this for me is a show stopper in my application.
Thank you
Can you please take a look at the NeoDBPro plugin "dbpImportFromCSV" to see how this can be improved when handling large imports.
Or can you please offer an alternative solution within VisualNeoWin, as this for me is a show stopper in my application.
Thank you
Quote from luishp on February 26, 2020, 1:15 pm@mark-waples I can't offer you a better solution than the existing one, sorry!
Anyway there are toons of tools to help importing CSV into .mdb databases:https://www.doogal.co.uk/CsvToMdb.php
https://www.convert-db.com/csv-to-ms-access.htm
https://www.zamzar.com/convert/csv-to-mdb/
https://www.fullconvert.com/howto/csv-to-accessHope it helps.
Regards.
@mark-waples I can't offer you a better solution than the existing one, sorry!
Anyway there are toons of tools to help importing CSV into .mdb databases:
https://www.doogal.co.uk/CsvToMdb.php
https://www.convert-db.com/csv-to-ms-access.htm
https://www.zamzar.com/convert/csv-to-mdb/
https://www.fullconvert.com/howto/csv-to-access
Hope it helps.
Regards.
Quote from dglojnar on February 26, 2020, 2:47 pmAs I said before, the best solution IMHO is using Doogal's CSVtoMDB - just add Action to button from within your Neobook app.
As console app, you can hide app from RUN command wizard window, so complete action run on the Background, looks like elegant solution for me.
And don't worry about key, it's a just optional:-)
I tried importing CSV file using SQL, but it's a little bit tricky, CSVtoMDB is far better solution.
Hope this help:-)
Cheers!
As I said before, the best solution IMHO is using Doogal's CSVtoMDB - just add Action to button from within your Neobook app.
As console app, you can hide app from RUN command wizard window, so complete action run on the Background, looks like elegant solution for me.
And don't worry about key, it's a just optional:-)
I tried importing CSV file using SQL, but it's a little bit tricky, CSVtoMDB is far better solution.
Hope this help:-)
Cheers!
Quote from Mark Waples on February 26, 2020, 3:16 pm@dglojnarI will look at that solution again but I have written a VBscript to do the same and that works outside of Neo.If I try and run the VBscript within Neo as a function I get the same issues "Not Responding"I just have to accept that VisualNeoWin cannot handle large amounts of data when importing or performing a function that involves a lot of file processing time which IMHO is very poor!Not happy! :(
Quote from Gaev on February 26, 2020, 7:20 pm@mark-waples
I just have to accept that VisualNeoWin cannot handle large amounts of data when importing
A bit late to the ongoing conversation, but did you consider ...
- chopping the huge CSV file into smaller chunks (could be done using VisualNEOWin commands)
- importing the smaller chunks one at a time
I just have to accept that VisualNeoWin cannot handle large amounts of data when importing
A bit late to the ongoing conversation, but did you consider ...
- chopping the huge CSV file into smaller chunks (could be done using VisualNEOWin commands)
- importing the smaller chunks one at a time
Quote from Mark Waples on February 26, 2020, 9:27 pm@gaev
The more the merrier! :)
You have reminded me of a free file splitter app called GSPLIT - it can run as an application or you can drive it with a commandline and it is extremely fast.
https://www.gdgsoft.com/gsplit
Thank you for the suggestion and reminding me about gsplit.
Mark
The more the merrier! :)
You have reminded me of a free file splitter app called GSPLIT - it can run as an application or you can drive it with a commandline and it is extremely fast.
https://www.gdgsoft.com/gsplit
Thank you for the suggestion and reminding me about gsplit.
Mark
Quote from Mark Waples on February 27, 2020, 4:17 pmHi All
ended up using GSplit to split the files into manageable chunks and then imported them into my database table 1 at a time.
I worked out that I could get away with 3000 records per file before Neo started to splutter and complain.
Thank you all for your suggestions and help.
Mark
Hi All
ended up using GSplit to split the files into manageable chunks and then imported them into my database table 1 at a time.
I worked out that I could get away with 3000 records per file before Neo started to splutter and complain.
Thank you all for your suggestions and help.
Mark