importing csv into mysql via cron

Question

I am a complete noob when it comes to mysql databases.

What i want to achieve - is this - i have a sap b1 database and i am going to be exporting data from the sql server out to a csv - from there i will send this csv through to my web server.

Now what i want to do is to load up the csv into a mysql database on a scheduled basis (daily) via a cron job.

Here is the data that i will likely have in multiple csvs:

orders
invoices
credits
payments

Would i create a database for each or have them all within one database within phpmyadmin?
Also - let's take orders for example - would i create two tables - one for the order header information and another for order lines?

An example of the invoices csv would be the following format:

customernumber
customername
invoicenumber
purchaseordernumber
documentdate
freightamount
productcode
productname
barcode
quantity
price ex tax
price inc tax
RRP price
tax amount
doc total inc tax

Once in the tables - i will then go about developing a secure website/ application for my company that will be used by internal staff as well as customers.

Any advice would be appreciated.
Regards
Rick

Possible duplicate of [PHP regular backup of mysql data](http://stackoverflow.com/questions/38916163/php-regular-backup-of-mysql-data) — e4c5, Sep 24 '16 at 08:00
something in this mess http://stackoverflow.com/questions/tagged/cron+mysql — Drew, Sep 24 '16 at 08:24

score 0 · Answer 1 · answered Sep 24 '16 at 04:16

0

One way to look at CSV files is that each is a table:

Header1,Header2,Header3
Value1,Value2,Value3
...,...,...

->

Header1 | Header2 | Header3
---------------------------
Value1  | Value2  | Value3 
...     | ...     | ...

In mysql, a single database can have many tables. So for your example, you may want to have a single database with a table for each CSV file.

answered Sep 24 '16 at 04:16

asifrc

5,781
2
18
22

Thank you for the reply. OK - one CSV = one table – Rick Sep 24 '16 at 06:16
Do you have an example of the script required to import a CSV into a table? let's say for example - code, name, qty is the CSV - I would then set up a table with those exact headers in that order. – Rick Sep 24 '16 at 06:17
The internet has more examples than I can offer. Try searching "csv mysql" :) – asifrc Sep 24 '16 at 06:20

importing csv into mysql via cron

1 Answers1