Advice on Automated Query and output of Txt file

[stevieb]

A littl ebit of advice really.

I have worked a little with mySQL and PHP.

But i am thinking is this the right way to go....I have the Postcode PAF database, and have number of Postcodes of there Origin and Destination.

What would be the best way to either write a bit of software to input into the database to generate the OSGR from the PA database and then output this as a complete new table either in CSV or DBf format?

Would PHP/SQL be the way to go or go stright off and use C++/Visual Basic linked to an access database??


Thanks

Steve

[JamieBeeston]

depends where your skillset lies.

I am sure the php/mysql way would be slower (but potentially easier to run on different platforms)

If you know C, knock summin up, if you know PHP, knock summin up.. if performance on PHP/MySQL becomes an issue, get a Coder in.

J

[stevieb]

JamieBeeston said:

depends where your skillset lies.

I am sure the php/mysql way would be slower (but potentially easier to run on different platforms)

If you know C, knock summin up, if you know PHP, knock summin up.. if performance on PHP/MySQL becomes an issue, get a Coder in.

J

This is not really my core skill set... i can muddle through on both but the codes are not going to be as efficent as a experience coder..

What would be the most forgiving in terms of errors etc

steve

[JamieBeeston]

I'm no coder either, but i'd wager the PHP side will be easier, simply as you can edit as you go without having to edit compile, run, test, recompile etc..

not my forté tho... so I'll happily defer.

J

[pdV6]

Might be a licensing issue with ripping PAF content into another db?

[jamieboy]

Agree with pdv6's licensing concerns.

If it were me, I'd probably knock up a quick bit of VBScript with an ADO connection to both the input and the output databases, depending on the current database format and how you want to output it.

Assuming you're using a Windows box, VBScript offers all the edit-test-edit benefits of PHP, but you don't need to run it on a webserver. Or (genuine question) can you get a command-line version of PHP?

edit - my solution was probably more suitable for a one-off, but reading the original post I guess that's not what you're after. In which case I'd got with either PHP/mySQL, or ASP/mySQL, or ASP/JET, or whatever.

Edited by jamieboy on Monday 10th July 16:09

[stevieb]

pdV6 said:

Might be a licensing issue with ripping PAF content into another db?

bugger never thought of that...

[stevieb]

jamieboy said:

Agree with pdv6's licensing concerns.

If it were me, I'd probably knock up a quick bit of VBScript with an ADO connection to both the input and the output databases, depending on the current database format and how you want to output it.

Assuming you're using a Windows box, VBScript offers all the edit-test-edit benefits of PHP, but you don't need to run it on a webserver. Or (genuine question) can you get a command-line version of PHP?

edit - my solution was probably more suitable for a one-off, but reading the original post I guess that's not what you're after. In which case I'd got with either PHP/mySQL, or ASP/mySQL, or ASP/JET, or whatever.

Edited by jamieboy on Monday 10th July 16:09

The current dataset is in text file format (CSV), which is why i am asking which database would be best suited to importing the data sql or access. 3.2 million rows of data.

how similar is VB script to VB???

[jamieboy]

stevieb said:

The current dataset is in text file format (CSV), which is why i am asking which database would be best suited to importing the data sql or access. 3.2 million rows of data.

how similar is VB script to VB???

It's pretty similar in syntax, just a bit more restricted in what you can do.

For this task, you could open the text file using the FileSystemObject and do a readline to get your data, parse it into the fields you require, and then use an ADO connection to squirt it into whatever database you choose.

Access (I think) is limited by a total database size of 2GB, rather than by number of rows of data. It's impossible to say without knowing the size of each field, but I'd say 3.2 million rows would be at the upper end of what Access would physically support. It'd certainly be at the top end of what it would practically support (in performance terms) so you may be better with mySQL or MS SQL Server Express, both of which are free.

[pdV6]

stevieb said:

The current dataset is in text file format (CSV), which is why i am asking which database would be best suited to importing the data sql or access. 3.2 million rows of data.

With that amount of data I'd forget Access.

[TheExcession]

pdV6 said:

stevieb said:

The current dataset is in text file format (CSV), which is why i am asking which database would be best suited to importing the data sql or access. 3.2 million rows of data.

With that amount of data I'd forget Access.

I wouldn't necessarily 'Forget Access'... a DB index is a DB index at the end of the day.

My concern with Access would be the number of cuncurrent connections it can support.

Still, I'd much rather go for a 'proper' DB solution, MySQL or Oracle, but Access can be very worth while for just proving concept and getting something working. And, once created on access it's not normally difficult to port to a 'proper' DB platform.

Ex