question Re: NiFI: how can i convert string to date in Support Questions

NiFI: how can i convert string to date

Acbx — Mon, 17 May 2021 20:00:06 GMT

Hello

I trying to put some data from my CSV to my database but i got some error.

Settings UpdateRecord

Settings about CSVRecordSetWriter

My csv file:

Region	Country	ItemType	SalesChannel	OrderPriority	OrderDate	OrderID	ShipDate	UnitsSold	UnitPrice	UnitCost	TotalRevenue	TotalCost	TotalProfit
Sub-Saharan Africa	Chad	Office Supplies	Online	L	1/27/2011	292494523	02/12/2011	4484	651.21	524.96	2920025.64	2353920.64	566105.00
Europe	Latvia	Beverages	Online	C	12/28/2015	361825549	1/23/2016	1075	47.45	31.79	51008.75	34174.25	16834.50
Middle East and North Africa	Pakistan	Vegetables	Offline	C	1/13/2011	141515767	02/01/2011	6515	154.06	90.93	1003700.90	592408.95	411291.95
Sub-Saharan Africa	Democratic Republic of the Congo	Household	Online	C	09/11/2012	500364005	10/06/2012	7683	668.27	502.54	5134318.41	3861014.82	1273303.59
Europe	Czech Republic	Beverages	Online	C	10/27/2015	127481591	12/05/2015	3491	47.45	31.79	165647.95	110978.89	54669.06

Re: NiFI: how can i convert string to date

_mark_ — Fri, 21 May 2021 21:05:06 GMT

Hi,

Check your "Date Format" setting in the CSVReader service where you can specify a format string to interpreting text as a date. At a guess either "M/dd/yyyy" or M/dd/yyyy" would work based on the data sample provided.

More information on date format strings here: https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

As an aside, I don't think the CSVRecordSetWriter settings are much use here as you're terminating all outbound PutDatabaseRecord relationships.

Hope that helps.

Re: NiFI: how can i convert string to date

_mark_ — Fri, 21 May 2021 21:23:56 GMT

Hi,

I've just noticed your date strings in the sample aren't consistent which will make things difficult I think.

For example in line 1 your OrderDate format is "M/dd/yyyy" (single digit month) and in ship date it seems to be "MM/dd/yyyy" (double digit month). For the CSV to work correctly all of your date fields would need to adhere to the same format I believe.

You can do a couple of things to resolve this:

Fix it in the source. The best option in my opinion, if possible.
Clean the data in NiFi before it arrives at the PutDatabaseRecord processor.

Unfortunately the second is a touch beyond my current level of expertise, but if I was you I would explore either ReplaceText processor with the appropriately regex expression, or a an UpdateRecord processor with a SQL like statement to update this field in the flowfile - note though this would would require a separate CSVReader service that reads your data as strings.

Good luck.