Create new batch of records method best practice?


(TahorSuiJuris) #1

When creating a new batch of records, using Excel then exporting as CSV which is much shorter than creating MySQL then exporting is this a best practice?

Should there not be a column for auto increment index? As in the Northwind import there existed indexes and foreign keys.

I need to create three tables, PURCHASE connects to SALES, and a third SUMMARY table that populates with data from the first two with calculated columns.

  1. purchaseid (integer auto incremented)
  2. purchaseinv (varchar 20)
  3. purchaseymd (date)
  4. purchasebank (varchar 20)
  5. purchasetier (varchar 20)
  6. purchasepoints (integer 20)
  7. purchasecost (decimal 6,6)
  8. purchasetotal (decimal 13,6)

LOAD CSV WITH HEADERS FROM "path/filename.csv" AS ROW
CREATE (p:Purchase)
SET p = row,
p.purchaseid = toInteger(row.purchaseid),
p.purchaseinv = toInteger(row.purchaseinv),
p.purchaseymd = toDate(row.purchaseymd),
p.purchasebank = toVarchar(row.purchasebank),
p.purchasetier = toVarchar(row.purchasetier),
p.purchasepoints = toInteger(row.purchasepoints),
p.purchasecost = toDecimal(row,purchasecost),
p.purchasetotal = (row.purchasepoints * row.purchasecost)

How would I set the number of digits and decimals?
Would one modify each individual field attributes using SET?

CREATE INDEX ON :Purchase(purchaseid)



(M. David Allen) #2

No - this isn't typically done with neo4j. Every node and relationship already gets a long integer id, which you can access by calling id(myNode), so it's a bit already built-in.

Really this just depends on what's most convenient for you. If you want to query a relational database directly for records, you can do this with apoc.load.jdbc, if it's better to keep the data fresh in another system. If you prefer to dump to CSV it's just a question of what's quickest. That being said sometimes it's better to go from the database directly if you have one, because for example your values are already numbers there, and when you dump them to CSV and reload from that text format, you'll do this stuff in cypher as you're doing now where you turn text back into numbers and so on.

You don't create tables in Neo4j. ;) You create nodes for each of those entity types and link them by relationships.

The error that you show in your screenshot is because you need to give LOAD CSV a URL path, and not a file path. Try this instead:

LOAD CSV WITH HEADERS FROM "file:///c/path/to/file.csv"

(TahorSuiJuris) #3

@david.allen Thank you, such a relief to receive a reply. Shall make the correction. I had begun to create a new MySQL with an HTML front end to display the data. The desire is to complete the relational transactions in Neo4j, as I have three options:

Create the generated calculations in MySQL with a front end HTML to display, also to include CRUD.
Create the generated calculations on the front end deriving the fields from MySQL.
Create the generated calculations in Neo4j and display in HTML front end.

As I understand, Neo4j is much faster and more stable for relational transactions than MySQL for FIFO calculations.


(TahorSuiJuris) #4

Q. Is there a tutorial on how to connect to Neo4j using FTP to use the fields in my HTML front end?


(M. David Allen) #5

I'm sorry I don't understand that question. FTP is an old file transfer technology that isn't typically related at all to HTML front-ends.

Perhaps you should start by describing what you have to work with and what you're trying to make happen.


(TahorSuiJuris) #6

@david.allen Thank you, shall do. Tomorrow evening is the earliest I can resume my education of Neo4j. Looking forward to your thoughts on data connectivity to a front end GUI. Shall communicate back after tomorrow.