Comments (14)
omarlarus
commented on May 27, 2024
Hi, some questions:
- neo4j-etl version
- neo4j-desktop version (if you are using it)
- operating system
Paste your command line and a sample of CSV file you think has the problem, please.
Quoting and special characters can give problems.
from neo4j-etl.
pratikid
commented on May 27, 2024
Neo4j Version : 3.3.3
Neo4j-etl Version : 1.2.0-RC1
Operating System : Windows 7 Enterprise
Command Line:
bin\neo4j-etl export --rdbms:url jdbc:mysql://localhost:3306 --rdbms:user root --rdbms:password "" --rdbms:schema copy_table --using cypher:direct --neo4j:url bolt://localhost:7686 --neo4j:user neo4j --neo4j:password "" --import-tool C:\neo4j\bin --csv-directory /tmp/neo4j --options-file /tmp/query/options.json --quote "'" --force --debug
INFO: Creating Neo4j store from CSV...
org.neo4j.driver.v1.exceptions.ClientException: Invalid input 'c': expected '\', ''', '"', 'b', 'f', 'n', 'r', 't', UTF16 or UTF32 (line 2, column 32 (offset: 54))
"LOAD CSV FROM 'file:///csv-010\copy_table/NODE_copytable.countries_0aa4d656-c187-43ac-bd32-d19356187dad.csv' AS row FIELDTERMINATOR ','"
^
at org.neo4j.driver.internal.net.SocketResponseHandler.handleFailureMessage(SocketResponseHandler.java:76)
at org.neo4j.driver.internal.messaging.PackStreamMessageFormatV1$Reader.unpackFailureMessage(PackStreamMessageFormatV1.java:470)
at org.neo4j.driver.internal.messaging.PackStreamMessageFormatV1$Reader.read(PackStreamMessageFormatV1.java:431)
at org.neo4j.driver.internal.net.SocketClient.receiveOne(SocketClient.java:196)
at org.neo4j.driver.internal.net.SocketConnection.receiveOne(SocketConnection.java:217)
at org.neo4j.driver.internal.net.ConcurrencyGuardingConnection.receiveOne(ConcurrencyGuardingConnection.java:165)
at org.neo4j.driver.internal.net.pooling.PooledSocketConnection.receiveOne(PooledSocketConnection.java:183)
at org.neo4j.driver.internal.InternalStatementResult.receiveOne(InternalStatementResult.java:335)
at org.neo4j.driver.internal.InternalStatementResult.consume(InternalStatementResult.java:291)
at org.neo4j.etl.util.CypherBoltRunner.execute(CypherBoltRunner.java:64)
at org.neo4j.etl.commands.rdbms.importer.BoltDriverImportFromRdbms.doLoadCsv(BoltDriverImportFromRdbms.java:29)
at org.neo4j.etl.commands.rdbms.importer.AbstractLoadCsvImportFromRdbms.extractAndLoad(AbstractLoadCsvImportFromRdbms.java:58)
at org.neo4j.etl.commands.rdbms.importer.BoltDriverImportFromRdbms.extractAndLoad(BoltDriverImportFromRdbms.java:16)
at org.neo4j.etl.cli.rdbms.ImportFromRdbmsCli.run(ImportFromRdbmsCli.java:281)
at org.neo4j.etl.util.CliRunner.run(CliRunner.java:42)
at org.neo4j.etl.util.CliRunner.run(CliRunner.java:35)
at org.neo4j.etl.NeoIntegrationCli.main(NeoIntegrationCli.java:43)
from neo4j-etl.
albertodelazzari
commented on May 27, 2024
Hi, this should be fixed in the next release.
from neo4j-etl.
Zaky7
commented on May 27, 2024
Neo4j Version : 3.3.4
Neo4j-etl Version : 1.2.0
Operating System : Mac OS X
I am running below command
./neo4j-etl export --rdbms:url jdbc:mysql://localhost:3306/openmrs --rdbms:user root --rdbms:password asdf --rdbms:schema openmrs --neo4j:user neo4j --neo4j:password qwas --import-tool /usr/local/Cellar/neo4j/3.3.4/libexec/bin --csv-directory /usr/local/Cellar/neo4j/3.3.4/libexec/import --mapping-file mappingNew.json --options-file options.json --using cypher:direct --quote '",' --debug --force
I am getting following error while importing. This is due to escape characters in a CSV file
org.neo4j.driver.v1.exceptions.DatabaseException: At /usr/local/Cellar/neo4j/3.3.4/libexec/import/csv-008/openmrs/NODE_openmrs.raxacoremessage_8e1dbe64-8a43-4c39-9513-1cd0aa175ab2.csv:44188 - there's a field starting with a quote and whereas it ends that quote there seems to be characters in that field after that ending quote. That isn't supported. This is what I read: 'ghjg
","dd'
at org.neo4j.driver.internal.net.SocketResponseHandler.handleFailureMessage(SocketResponseHandler.java:83)
at org.neo4j.driver.internal.messaging.PackStreamMessageFormatV1$Reader.unpackFailureMessage(PackStreamMessageFormatV1.java:470)
at org.neo4j.driver.internal.messaging.PackStreamMessageFormatV1$Reader.read(PackStreamMessageFormatV1.java:431)
at org.neo4j.driver.internal.net.SocketClient.receiveOne(SocketClient.java:196)
at org.neo4j.driver.internal.net.SocketConnection.receiveOne(SocketConnection.java:217)
at org.neo4j.driver.internal.net.ConcurrencyGuardingConnection.receiveOne(ConcurrencyGuardingConnection.java:165)
at org.neo4j.driver.internal.net.pooling.PooledSocketConnection.receiveOne(PooledSocketConnection.java:183)
at org.neo4j.driver.internal.InternalStatementResult.receiveOne(InternalStatementResult.java:335)
at org.neo4j.driver.internal.InternalStatementResult.consume(InternalStatementResult.java:291)
at org.neo4j.etl.util.CypherBoltRunner.execute(CypherBoltRunner.java:64)
at org.neo4j.etl.commands.rdbms.importer.BoltDriverImportFromRdbms.doLoadCsv(BoltDriverImportFromRdbms.java:29)
at org.neo4j.etl.commands.rdbms.importer.AbstractLoadCsvImportFromRdbms.extractAndLoad(AbstractLoadCsvImportFromRdbms.java:58)
at org.neo4j.etl.commands.rdbms.importer.BoltDriverImportFromRdbms.extractAndLoad(BoltDriverImportFromRdbms.java:16)
at org.neo4j.etl.cli.rdbms.ImportFromRdbmsCli.run(ImportFromRdbmsCli.java:150)
at org.neo4j.etl.util.CliRunner.run(CliRunner.java:42)
at org.neo4j.etl.util.CliRunner.run(CliRunner.java:35)
at org.neo4j.etl.NeoIntegrationCli.main(NeoIntegrationCli.java:43)
from neo4j-etl.
AngeloBusato
commented on May 27, 2024
Hi @Zaky7, can you paste your csv, mapping and options files please?
from neo4j-etl.
Zaky7
commented on May 27, 2024
Sorry @AngeloBusato I am not allowed to paste the mapping.json and data CSV files. I can give you the snippet of part where the error is coming. ETL tool is working in offline mode for importing data but not in online mode.
from neo4j-etl.
AngeloBusato
commented on May 27, 2024
@Zaky7 can you explain why you use --quote '",'?
CSV are automatically quoted according to the standard.
from neo4j-etl.
Zaky7
commented on May 27, 2024
@AngeloBusato , I have tried running it without the --quote flag. It is giving me same error. ETL tool is not escaping the special character in my data.
This is snippet of line in csv file where it is giving the error
"3658","3658","2013-07-06 09:36:42.0",,,,,,"ddasfd","aca3d75d-2364-4166-ab80-357986b5e179","1",,"Deprecated Outpatient",,"2013-07-06 09:30:03.0","2014-04-25 06:06:06.0",,"Obs"
"3659","3659","2013-07-06 09:36:42.0",,,,,,"[{""attrs"":{""clearBeforeDraw"":true,""visible"":true,""listening"":true,""opacity"":1,""x"":0,""y"":0,""scale"":{""x"":1,""y"":1},""rotation"":0,""offset"":{""x"":0,""y"":0},""draggable"":false,""dragOnTop"":true,""id"":""textLayer""},""nodeType"":""Layer"",""children"":[{""attrs"":{""fontFamily"":""Helvetica"",""text"":""Sat Jul 06 2013 15:06:03"",""fontSize"":16,""align"":""left"",""verticalAlign"":""top"",""fontStyle"":""normal"",""padding"":0,""width"":""auto"",""height"":""auto"",""lineHeight"":1,""fillEnabled"":true,""strokeEnabled"":true,""shadowEnabled"":true,""dashArrayEnabled"":true,""fillPriority"":""color"",""visible"":true,""listening"":true,""opacity"":1,""x"":100,""y"":25,""scale"":{""x"":1,""y"":1},""rotation"":0,""offset"":{""x"":0,""y"":0},""draggable"":false,""dragOnTop"":true,""gid"":null,""storeId"":null,""storeUuid"":""date"",""fill"":""#000""},""nodeType"":""Shape"",""shapeType"":""Text""},{""attrs"":{""fontFamily"":""Helvetica"",""fontSize"":16,""align"":""left"",""verticalAlign"":""top"",""fontStyle"":""normal"",""padding"":0,""width"":""auto"",""height"":""auto"",""lineHeight"":1,""fillEnabled"":true,""strokeEnabled"":true,""shadowEnabled"":true,""dashArrayEnabled"":true,""fillPriority"":""color"",""visible"":true,""listening"":true,""opacity"":1,""x"":500,""y"":680,""scale"":{""x"":1,""y"":1},""rotation"":0,""offset"":{""x"":0,""y"":0},""draggable"":false,""dragOnTop"":true,""gid"":null,""storeId"":null,""fill"":""#000
This is the output I am getting from ETL error log
org.neo4j.driver.v1.exceptions.DatabaseException: At /usr/local/Cellar/neo4j/3.3.4/libexec/import/csv-015/openmrs/NODE_openmrs.obs_762fb334-709a-4731-a776-99516602649c.csv:505 - there's a field starting with a quote and whereas it ends that quote there seems to be characters in that field after that ending quote. That isn't supported. This is what I read: '[{"attrs":{"clearBeforeDraw":true,"visible":true,"listening":true,"opacity":1,"x":0,"y":0,"scale":{"x":1,"y":1},"rotation":0,"offset":{"x":0,"y":0},"draggable":false,"dragOnTop":true,"id":"textLayer"},"nodeType":"Layer","children":[{"attrs":{"fontFamily":"Helvetica","text":"Sat Jul 06 2013 15:06:03","fontSize":16,"align":"left","verticalAlign":"top","fontStyle":"normal","padding":0,"width":"auto","height":"auto","lineHeight":1,"fillEnabled":true,"strokeEnabled":true,"shadowEnabled":true,"dashArrayEnabled":true,"fillPriority":"color","visible":true,"listening":true,"opacity":1,"x":100,"y":25,"scale":{"x":1,"y":1},"rotation":0,"offset":{"x":0,"y":0},"draggable":false,"dragOnTop":true,"gid":null,"storeId":null,"storeUuid":"date","fill":"#000"},"nodeType":"Shape","shapeType":"Text"},{"attrs":{"fontFamily":"Helvetica","fontSize":16,"align":"left","verticalAlign":"top","fontStyle":"normal","padding":0,"width":"auto","height":"auto","lineHeight":1,"fillEnabled":true,"strokeEnabled":true,"shadowEnabled":true,"dashArrayEnabled":true,"fillPriority":"color","visible":true,"listening":true,"opacity":1,"x":500,"y":680,"scale":{"x":1,"y":1},"rotation":0,"offset":{"x":0,"y":0},"draggable":false,"dragOnTop":true,"gid":null,"storeId":null,"fill":"#000"},"nodeType":"Shape","shapeType":"Text"},{"attrs":{"fontFamily":"Helvetica","text":"Patient stable\nstoma"":"offset"":{""x"":0,""y"":0},""draggable"":false,""dragOnTop"":true,""gid"":null,""storeId"":null,""fill"":""#000""},""nodeType"":""Shape"",""shapeType"":""Text""},{""attrs"":{""fontFamily"":""Helvetica"",""text"":""Patient stable\nstoma\"":'
at org.neo4j.driver.internal.net.SocketResponseHandler.handleFailureMessage(SocketResponseHandler.java:83)
at org.neo4j.driver.internal.messaging.PackStreamMessageFormatV1$Reader.unpackFailureMessage(PackStreamMessageFormatV1.java:470)
at org.neo4j.driver.internal.messaging.PackStreamMessageFormatV1$Reader.read(PackStreamMessageFormatV1.java:431)
at org.neo4j.driver.internal.net.SocketClient.receiveOne(SocketClient.java:196)
at org.neo4j.driver.internal.net.SocketConnection.receiveOne(SocketConnection.java:217)
at org.neo4j.driver.internal.net.ConcurrencyGuardingConnection.receiveOne(ConcurrencyGuardingConnection.java:165)
at org.neo4j.driver.internal.net.pooling.PooledSocketConnection.receiveOne(PooledSocketConnection.java:183)
at org.neo4j.driver.internal.InternalStatementResult.receiveOne(InternalStatementResult.java:335)
at org.neo4j.driver.internal.InternalStatementResult.consume(InternalStatementResult.java:291)
at org.neo4j.etl.util.CypherBoltRunner.execute(CypherBoltRunner.java:64)
at org.neo4j.etl.commands.rdbms.importer.BoltDriverImportFromRdbms.doLoadCsv(BoltDriverImportFromRdbms.java:29)
at org.neo4j.etl.commands.rdbms.importer.AbstractLoadCsvImportFromRdbms.extractAndLoad(AbstractLoadCsvImportFromRdbms.java:58)
at org.neo4j.etl.commands.rdbms.importer.BoltDriverImportFromRdbms.extractAndLoad(BoltDriverImportFromRdbms.java:16)
at org.neo4j.etl.cli.rdbms.ImportFromRdbmsCli.run(ImportFromRdbmsCli.java:150)
at org.neo4j.etl.util.CliRunner.run(CliRunner.java:42)
at org.neo4j.etl.util.CliRunner.run(CliRunner.java:35)
at org.neo4j.etl.NeoIntegrationCli.main(NeoIntegrationCli.java:43)
It seems 'java' command is not available.
from neo4j-etl.
albertodelazzari
commented on May 27, 2024
@Zaky7 the issue seems to be not related to CSV escaping but cypher queries generation. To better understand the issue and try to replicate it we need, at least, the full CSV row with the escaped JSON. Or maybe you can paste here the following chars (in the escaped JSON) after the string "Patient stable\nstoma". We want to verify that the escaped JSON is escaped correctly when it is written on the CSV file.
Thanks
from neo4j-etl.
Zaky7
commented on May 27, 2024
@albertodelazzari I have created a dummy database neocheck for replicating this issue
Here is the csv file
"1","1","p/a soft\","Ob"
"2","2","p/a soft\","Ob"
"3","3","dsfsad
dfsafsd
dsafds
dsafasd
dsfads
","Ob"
"4","4","dsaf
dfsadf
/dffas
dfsa","Ob"
When I am running this command
./neo4j-etl export --rdbms:url jdbc:mysql://localhost:3306/neocheck --rdbms:user root --rdbms:password qwas --rdbms:schema neocheck --using bulk:neo4j-import --neo4j:user neo4j --neo4j:password qwas --import-tool $NEO4J_HOME/bin --destination $NEO4J_HOME/data/databases/graph.db/ --csv-directory $NEO4J_HOME/import --options-file options.json --debug --force
It is giving me the following error
Error in input data
Caused by:ERROR in input
data source: BufferedCharSeeker[source:/Users/zakirsaifi/Desktop/Office/Task/ETL/neo4j-community-3.3.5/import/csv-001/neocheck/NODE_neocheck.OBS_6427d0d8-cdc5-47b9-9d39-c271c2646ed2.csv, position:4194376, line:0]
in field: valueText:string:3
for header: [:ID(neocheck.obs), id:long, valueText:string, :LABEL]
raw field value: 1
original error: At /Users/zakirsaifi/Desktop/Office/Task/ETL/neo4j-community-3.3.5/import/csv-001/neocheck/NODE_neocheck.OBS_6427d0d8-cdc5-47b9-9d39-c271c2646ed2.csv:0 - there's a field starting with a quote and whereas it ends that quote there seems to be characters in that field after that ending quote. That isn't supported. This is what I read: 'p/a soft","OO'
WARNING Import failed. The store files in /Users/zakirsaifi/Desktop/Office/Task/ETL/neo4j-community-3.3.5/data/databases/graph.db are left as they are, although they are likely in an unusable state. Starting a database on these store files will likely fail or observe inconsistent records so start at your own risk or delete the store manually', DurationMillis: 1731 }]
at org.neo4j.etl.process.ProcessHandle.await(ProcessHandle.java:84)
at org.neo4j.etl.neo4j.importcsv.ImportFromCsvCommand.execute(ImportFromCsvCommand.java:29)
at org.neo4j.etl.commands.rdbms.importer.Neo4jImportImportFromRdbms.doImport(Neo4jImportImportFromRdbms.java:63)
at org.neo4j.etl.commands.rdbms.importer.Neo4jImportImportFromRdbms.extractAndLoad(Neo4jImportImportFromRdbms.java:41)
at org.neo4j.etl.cli.rdbms.ImportFromRdbmsCli.run(ImportFromRdbmsCli.java:150)
at org.neo4j.etl.util.CliRunner.run(CliRunner.java:42)
at org.neo4j.etl.util.CliRunner.run(CliRunner.java:35)
at org.neo4j.etl.NeoIntegrationCli.main(NeoIntegrationCli.java:43)
It seems 'java' command is not available.
Please check your JAVA_HOME environment variable.
from neo4j-etl.
mguarnaccia
commented on May 27, 2024
Hi,
@Zaky7 : could you post the content of options file "options.json"?
You might be missing an option for handling multiline fields: {"multiline-fields":"true"}
from neo4j-etl.
Zaky7
commented on May 27, 2024
@mguarnaccia I am using this option already in my option.json
from neo4j-etl.
albertodelazzari
commented on May 27, 2024
@Zaky7 the following CSV file can't be a valid one because it doesn't respect the RFC 4180 format for CSV files. If the ETL creates a CSV file like this from your relational database then we should understand why this happens but in this case, it would be better to have a sample table.
Thanks
"1","1","p/a soft\","Ob"
"2","2","p/a soft\","Ob"
"3","3","dsfsad
dfsafsd
dsafds
dsafasd
dsfads
","Ob"
"4","4","dsaf
dfsadf
/dffas
dfsa","Ob"
from neo4j-etl.
Zaky7
commented on May 27, 2024
@albertodelazzari Thanks for your quick reply. This is sql file for the my dummy database.
https://drive.google.com/file/d/1y_KrA0QpZOZ0WMKxTWcFyy_Z2B1Mqz-p/view?usp=sharing
from neo4j-etl.
Related Issues (20)
- Mapping error when host contains backslash
- Neo4j ETL tool struggles with large datamodels
- SQLITE driver HOT 4
- Password is displayed in plain text when you enable "logs" HOT 1
- Better support for the neo4j+s URI scheme (Aura's default) when specifying a connection
- [PostgreSQL] hanging after "An illegal reflective access operation has occurred" HOT 2
- Error while loading MySQL data into Neo4j using ETL HOT 2
- Load Data Model - "Start Mapping" spins forever, never completes HOT 1
- Import data fails with Null Pointer Exception HOT 1
- Neo4j ETL Tool not working on Neo4j Desktop 1.4.9
- How to change the relation’s "start node" and "end node"?
- can't mapping mssql (sqlserver 13) HOT 1
- Can't map postgres to Neo4j graph using Neo4j ETL tool? HOT 1
- Bulk import fails using Neo4j Desktop on Windows due to `InvalidPathException`
- How to import data from views? HOT 2
- Certificate Errors preventing Neo4j ETL Graph App from starting HOT 1
- Error importing data from postgres to neo4J ETL tool HOT 1
- Too many slashes in file URI '////'
- Cannot drive neo4j-etl with my JAVA 8 environment
- Importing / Saving the visualized graph to json/neo4j friendly format
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from neo4j-etl.