Joanne Posted March 18, 2019 Share Posted March 18, 2019 Hello, I'm trying to use the hornbill export tool but getting some issues. Which i believe is to do with the spaces in my column names. If my config file looks like ReportID":4, "ReportName":"JBURR Test3", "DeleteReportInstance": true, "DeleteReportLocalFile": true, "Table":{ "TableName":"Requests2", "PrimaryKey":"Request ID", "Mapping":{ "Request ID":"Request ID", "Date Logged":"Date Logged", "Summary":"Summary" } } I get the following error [DATABASE] Connection Successful 2019/03/18 08:20:12 [DATABASE] Query:INSERT INTO Requests2 (Request ID, Date Logged, Summary) VALUES (:Request ID, :Date Logged, :Summary) 2019/03/18 08:20:12 [DATABASE] Binding: 2019/03/18 08:20:12 [DATABASE] :Date Logged = 2019-01-11 20:19:31 2019/03/18 08:20:12 [DATABASE] :Summary = Service is unavailable 2019/03/18 08:20:12 [DATABASE] :Request ID = IN00000002 2019/03/18 08:20:12 [ERROR] [DATABASE] NamedExec Error: could not find name Request in map[string]interface {}{"Request ID":"IN00000002", "Date Logged":"2019-01-11 20:19:31", "Summary":"Service is unavailable"} but if i add [] to accommodate for the spaces "Reports":[ { "ReportID":4, "ReportName":"JBURR Test3", "DeleteReportInstance": true, "DeleteReportLocalFile": true, "Table":{ "TableName":"Requests2", "PrimaryKey":"[Request ID]", "Mapping":{ "[Request ID]":"Request ID", "[Date Logged]":"Date Logged", "[Summary]":"Summary" } } } i get [DATABASE] Connection Successful 2019/03/18 08:21:56 [ERROR] Unable to map any values from the returned record: 2019/03/18 08:21:56 [RECORD] {"Actions Control":"","Activity Stream ID":"urn:buzz:activityStream:e03ec7ac-614f-45a7-8f0e-0798c1a48ad7","Archived":"Yes","BPM Activity Update ID":"","BPM ID":"BPM20190111000001","BPM Stage Expired":"No","BPM UI state":"","BPM authorised":"0","Call Back Date":"","Cancellation Reason":"Created In Error","Catalog":"Apply For A Design","Catalog ID":"29","Category ID":"","Category Name":"","Closed By Team":"","Closed By Team ID":"","Closed By User ID":"","Closed By Username":"","Closure Category ID":"","Closure Category Name":"","Company":"","Company ID":"","Created by":"admin","Custom 21":"","Custom 22":"","Custom 23":"","Custom 24":"","Custom 25":"","Custom 26":"","Custom 27":"","Custom 28":"","Custom 29":"","Custom 30":"","Custom A":"","Custom B":"","Custom C":"","Custom D":"","Custom E":"","Custom F":"","Custom G":"","Custom H":"","Custom I":"","Custom J":"","Custom K":"","Custom L":"","Custom M":"","Custom N":"","Custom O":"","Custom P":"","Custom Q":"","Custom R":"","Custom S":"","Custom T":"","Customer ID":"admin","Customer Name":"System Administrator","Customer Organization Container ID":"","Customer type":"0","Date Closed":"2019-01-13 20:15:54","Date Logged":"2019-01-11 20:19:31","Date Modified":"2019-01-13 20:15:55","Date Placed On Hold":"","Date Resolved":"","Description":"","External Reference Number":"","Feedback Status":"awaiting","Feedback Status ID":"0","First Time Fix":"","Freetext Search Index":"lid_35c613c9-f899-4ccc-bcde-48c6598496a4","Historic Request":"","Image":"","Impact":"","Impact Id":"","Is Analyst Unread":"1","Is Customer Unread":"","Last Date an email was sent from the request details":"","Last Update Activity ID":"urn:buzz:activity:acc43ab9-0dd1-46b6-83b8-b0e6de847257","Locked":"Unlocked","On Hold Until":"","Priority ID":"5","Priority Name":"Priority 2","Profile Code":"","Rating":"","Related Activity ID":"","Reopen Date":"","Reopened By Team":"","Reopened By Team ID":"","Reopened By User ID":"","Reopened By Username":"","Reopened Count":"0","Request ID":"IN00000002","Request Language":"en-GB","Request Owner ID":"","Request Owner Name":"","Request Owner Type":"","Request Prefix":"IN","Request Resolution Timer ID":"","Request Resolve By":"","Request Resolve Seconds":"","Request Resolve Time":"","Request Respond By":"","Request Response Seconds":"","Request Response Time":"","Request Response Timer ID":"","Request Total Onhold Time":"","Request Type":"Incident","Resolution":"","Resolved By Team":"","Resolved By Team ID":"","Resolved By User ID":"","Resolved By Username":"","Service ID":"23","Service Level Agreement ID":"","Service Level Agreement Name":"","Service Level Changed":"","Service Level Date Changed":"","Service Level ID":"","Service Level Name":"","Service Name":"Apply for a Design","Site":"Concept House","Site ID":"1","Social Object Ref":"urn:sys:entity:com.hornbill.servicemanager:Requests:IN00000002","Social Object URN":"urn:sys:user:admin","Source ID":"admin","Source Type":"Self Service","Status":"status.cancelled","Sub-status Id":"","Sub-status Text":"","Summary":"Service is unavailable","Team ID":"IPO/IT/OPS/APPSL2/","Team Name":"L2 Applications","Urgency":"","Within Resolve Time":"","Within Response Time":"","h_itsm_requests Organisation Id":"0"} 2019/03/18 08:21:56 [MAPPINGS] {"[Date Logged]":"Date Logged","[Request ID]":"Request ID","[Summary]":"Summary"} any help would be appreciated Cheers Jo Link to comment Share on other sites More sharing options...
Steve G Posted March 18, 2019 Share Posted March 18, 2019 Hi @Joanne, This has now been fixed, you can now use square brackets to wrap column names for SQL Server column names that include spaces, or back-ticks to wrap column names for MySQL/MariaDB column names that include spaces. MS SQL Server example: MySQL/MariaDB example: Note, the left-hand property in the mapping is the Report column name property, and the right-hand values are the Database column name where the record values will be written in to. The Report column name properties can include spaces and don't need to be wrapped by any [] or `` characters. The latest release ZIPs can be found here: https://github.com/hornbill/goHornbillDataExport/releases/latest You just need to download the ZIP that is relevant to your operating system and architecture. Let me know how you get on with this. Cheers, Steve 1 Link to comment Share on other sites More sharing options...
Joanne Posted March 18, 2019 Author Share Posted March 18, 2019 That worked, thanks for the quick turnaround Link to comment Share on other sites More sharing options...
Dirk Mautes Posted January 24, 2022 Share Posted January 24, 2022 (edited) Hi, I have the same issue, but the square brackets didn't seem to do the trick. Any idea on what I'm doing wrong? My config: { "APIKey": "********************", "InstanceID": "********", "Database":{ "Driver": "mssql", "Server": "********", "Database": "HornbillData", "Authentication": "SQL", "UserName": "xxxx", "Password": "*********, "Port": 1433, "Encrypt": false }, "Reports":[ { "ReportID":178, "ReportName":"************", "DeleteReportInstance": true, "DeleteReportLocalFile": true, "UseXLSX": false, "Table":{ "TableName":"HornbillData", "PrimaryKey":"[Requests->Request ID]", "Mapping":{ "Requests->Activity Stream ID":"[Requests->Activity Stream ID]", "Requests->Archived":"[Requests->Archived]", "Requests->BPM Activity Update ID":"[Requests->BPM Activity Update ID]", "Requests->BPM authorised":"[Requests->BPM authorised]", "Requests->BPM ID":"[Requests->BPM ID]", "Requests->BPM Stage Expired":"[Requests->BPM Stage Expired]", "Requests->BPM UI state":"[Requests->BPM UI state]", "Requests->Call Back Date":"[Requests->Call Back Date]", "Requests->Cancellation Reason":"[Requests->Cancellation Reason]", "Requests->Catalog":"[Requests->Catalog]", "Requests->Catalog ID":"[Requests->Catalog ID]", "Requests->Category ID":"[Requests->Category ID]", "Requests->Category Name":"[Requests->Category Name]", "Requests->Closed By Team":"[Requests->Closed By Team]", "Requests->Closed By Team ID":"[Requests->Closed By Team ID]", "Requests->Closed By User ID":"[Requests->Closed By User ID]", "Requests->Closed By Username":"[Requests->Closed By Username]", "Requests->Closure Category ID":"[Requests->Closure Category ID]", "Requests->Closure Category Name":"[Requests->Closure Category Name]", "Requests->Company":"[Requests->Company]", "Requests->Company ID":"[Requests->Company ID]", "Requests->Created By":"[Requests->Created By]", "Requests->Custom 21":"[Requests->Custom 21]", "Requests->Custom 22":"[Requests->Custom 22]", "Requests->Custom 23":"[Requests->Custom 23]", "Requests->Custom 24":"[Requests->Custom 24]", "Requests->Custom 25":"[Requests->Custom 25]", "Requests->Custom 26":"[Requests->Custom 26]", "Requests->Custom 27":"[Requests->Custom 27]", "Requests->Custom 28":"[Requests->Custom 28]", "Requests->Custom 29":"[Requests->Custom 29]", "Requests->Custom 30":"[Requests->Custom 30]", "Requests->Custom A":"[Requests->Custom A]", "Requests->Custom B":"[Requests->Custom B]", "Requests->Custom C":"[Requests->Custom C]", "Requests->Custom D":"[Requests->Custom D]", "Requests->Custom E":"[Requests->Custom E]", "Requests->Custom F":"[Requests->Custom F]", "Requests->Custom G":"[Requests->Custom G]", "Requests->Custom H":"[Requests->Custom H]", "Requests->Custom I":"[Requests->Custom I]", "Requests->Custom J":"[Requests->Custom J]", "Requests->Custom K":"[Requests->Custom K]", "Requests->Custom L":"[Requests->Custom L]", "Requests->Custom M":"[Requests->Custom M]", "Requests->Custom N":"[Requests->Custom N]", "Requests->Custom O":"[Requests->Custom O]", "Requests->Custom P":"[Requests->Custom P]", "Requests->Custom Q":"[Requests->Custom Q]", "Requests->Custom R":"[Requests->Custom R]", "Requests->Custom S":"[Requests->Custom S]", "Requests->Custom T":"[Requests->Custom T]", "Requests->Customer ID":"[Requests->Customer ID]", "Requests->Customer Name":"[Requests->Customer Name]", "Requests->Customer Organization Container ID":"[Requests->Customer Organization Container ID]", "Requests->Customer type":"[Requests->Customer type]", "Requests->Date Closed":"[Requests->Date Closed]", "Requests->Date Logged":"[Requests->Date Logged]", "Requests->Date Modified":"[Requests->Date Modified]", "Requests->Date Placed On Hold":"[Requests->Date Placed On Hold]", "Requests->Date Resolved":"[Requests->Date Resolved]", "Requests->Description":"[Requests->Description]", "Requests->Due Date":"[Requests->Due Date]", "Requests->External Reference Number":"[Requests->External Reference Number]", "Requests->Feedback Status":"[Requests->Feedback Status]", "Requests->Feedback Status ID":"[Requests->Feedback Status ID]", "Requests->First Time Fix":"[Requests->First Time Fix]", "Requests->Freetext Search Index":"[Requests->Freetext Search Index]", "Requests->Historic Request":"[Requests->Historic Request]", "Requests->Image":"[Requests->Image]", "Requests->Impact":"[Requests->Impact]", "Requests->Impact Id":"[Requests->Impact Id]", "Requests->Is Analyst Unread":"[Requests->Is Analyst Unread]", "Requests->Is Customer Unread":"[Requests->Is Customer Unread]", "Requests->Last Date Email Sent":"[Requests->Last Date Email Sent]", "Requests->Last Update Activity ID":"[Requests->Last Update Activity ID]", "Requests->Last Updated By":"[Requests->Last Updated By]", "Requests->Last Updated By ID":"[Requests->Last Updated By ID]", "Requests->Last Updated By Type":"[Requests->Last Updated By Type]", "Requests->Last Updated By User Type":"[Requests->Last Updated By User Type]", "Requests->Locked":"[Requests->Locked]", "Requests->On Hold Until":"[Requests->On Hold Until]", "Requests->Organisation Id":"[Requests->Organisation Id]", "Requests->Priority ID":"[Requests->Priority ID]", "Requests->Priority Name":"[Requests->Priority Name]", "Requests->Profile Code":"[Requests->Profile Code]", "Requests->Rating":"[Requests->Rating]", "Requests->Related Activity ID":"[Requests->Related Activity ID]", "Requests->Reopen Date":"[Requests->Reopen Date]", "Requests->Reopened By Team":"[Requests->Reopened By Team]", "Requests->Reopened By Team ID":"[Requests->Reopened By Team ID]", "Requests->Reopened By User ID":"[Requests->Reopened By User ID]", "Requests->Reopened By Username":"[Requests->Reopened By Username]", "Requests->Reopened Count":"[Requests->Reopened Count]", "Requests->Request ID":"[Requests->Request ID]", "Requests->Request Language":"[Requests->Request Language]", "Requests->Request Owner ID":"[Requests->Request Owner ID]", "Requests->Request Owner Name":"[Requests->Request Owner Name]", "Requests->Request Owner Type":"[Requests->Request Owner Type]", "Requests->Request Prefix":"[Requests->Request Prefix]", "Requests->Request Resolution Timer ID":"[Requests->Request Resolution Timer ID]", "Requests->Request Resolve By":"[Requests->Request Resolve By]", "Requests->Request Resolve Seconds":"[Requests->Request Resolve Seconds]", "Requests->Request Resolve Time":"[Requests->Request Resolve Time]", "Requests->Request Respond By":"[Requests->Request Respond By]", "Requests->Request Response Seconds":"[Requests->Request Response Seconds]", "Requests->Request Response Time":"[Requests->Request Response Time]", "Requests->Request Response Timer ID":"[Requests->Request Response Timer ID]", "Requests->Request Total Onhold Time":"[Requests->Request Total Onhold Time]", "Requests->Request Type":"[Requests->Request Type]", "Requests->Resolution":"[Requests->Resolution]", "Requests->Resolved By Team":"[Requests->Resolved By Team]", "Requests->Resolved By Team ID":"[Requests->Resolved By Team ID]", "Requests->Resolved By User ID":"[Requests->Resolved By User ID]", "Requests->Resolved By Username":"[Requests->Resolved By Username]", "Requests->Service ID":"[Requests->Service ID]", "Requests->Service Level Agreement ID":"[Requests->Service Level Agreement ID]", "Requests->Service Level Agreement Name":"[Requests->Service Level Agreement Name]", "Requests->Service Level Changed":"[Requests->Service Level Changed]", "Requests->Service Level Date Changed":"[Requests->Service Level Date Changed]", "Requests->Service Level ID":"[Requests->Service Level ID]", "Requests->Service Level Name":"[Requests->Service Level Name]", "Requests->Service Name":"[Requests->Service Name]", "Requests->Site":"[Requests->Site]", "Requests->Site ID":"[Requests->Site ID]", "Requests->Social Object Ref":"[Requests->Social Object Ref]", "Requests->Social Object URN":"[Requests->Social Object URN]", "Requests->Source ID":"[Requests->Source ID]", "Requests->Source Type":"[Requests->Source Type]", "Requests->Status":"[Requests->Status]", "Requests->Sub-status Id":"[Requests->Sub-status Id]", "Requests->Sub-status Text":"[Requests->Sub-status Text]", "Requests->Summary":"[Requests->Summary]", "Requests->Team ID":"[Requests->Team ID]", "Requests->Team Name":"[Requests->Team Name]", "Requests->Urgency":"[Requests->Urgency]", "Requests->Urgency Id":"[Requests->Urgency Id]", "Requests->Within Resolve Time":"[Requests->Within Resolve Time]", "Requests->Within Response Time":"[Requests->Within Response Time]", "owner->E-Mail":"[owner->E-Mail]", "owner->First Name":"[owner->First Name]", "owner->Last Name":"[owner->Last Name]", "owner->User Id":"[owner->User Id]", "RaisedBy->E-Mail":"[RaisedBy->E-Mail]", "RaisedBy->First Name":"[RaisedBy->First Name]", "RaisedBy->Last Name":"[RaisedBy->Last Name]", "RaisedBy->Name":"[RaisedBy->Name]", "RaisedBy->User Id":"[RaisedBy->User Id]", "owner->Name":"[owner->Name]" } } } ] } My log: 2022/01/24 09:52:26 ---- Hornbill Data Export Tool v1.7.1 ---- 2022/01/24 09:52:26 Flag - Configuration File: conf.json 2022/01/24 09:52:26 Flag - Debug: false 2022/01/24 09:52:26 [DEBUG] Loading Config File: C:\hbexport/conf.json 2022/01/24 09:52:26 Instance ID: leica 2022/01/24 09:52:26 [DEBUG] Connecting to Database Server: *********** 2022/01/24 09:52:26 2022/01/24 09:52:26 Running Report: DM LBS all requests raw dump all requests updated since start of last month [178] 2022/01/24 09:52:26 Checking Report Run ID [5379] for completion... 2022/01/24 09:52:29 Checking Report Run ID [5379] for completion... 2022/01/24 09:52:33 Checking Report Run ID [5379] for completion... 2022/01/24 09:52:33 Retrieving CSV Report File DM LBS all requests raw dump all requests updated since start of last month_5379.csv... 2022/01/24 09:52:33 Report File URL: https://mdh-p01-api.hornbill.com/******//dav/reports/178/DM LBS all requests raw dump all requests updated since start of last month_5379.csv 2022/01/24 09:52:33 Retrieved report data from C:\hbexport/reports/D********************************************.csv 2022/01/24 09:52:33 Processing 204 Records from DM LBS all requests raw dump all requests updated since start of last month_5379.csv... 2022/01/24 09:52:33 [DATABASE] Connection Successful 2022/01/24 09:52:33 [ERROR] [DATABASE] NamedExec Error: could not find name Requests in map[string]interface {}{"RaisedBy->E-Mail":"xxx.xxx@xxx.com", "RaisedBy->FirstName":"xxxx", "RaisedBy->LastName":"xxxx", "RaisedBy->Name":"xxx", "RaisedBy->UserId":"xxxx@xxxx", Edited January 24, 2022 by Steve Giller Removed email address from config Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now