How to convert date time when import csv file

[xiaoqw]

Hi, 

When I upload csv file into system, date time format is not matched. below I attached file I want to upload. this coding I used to import csv file

//try
//{
logger.info("FIle Name = "+FilePath);

var csvInfotable = Resources["CSVParserFunctions"].ReadCSVFile({
path: FilePath /* STRING */,
columnMappings: undefined /* STRING */,
hasHeader: true /* BOOLEAN */,
longitudeField: undefined /* NUMBER */,
dateFormat: undefined /* STRING */,
fileRepository: "SystemRepository" /* THINGNAME */,
latitudeField: undefined /* NUMBER */,
fieldDelimiter: undefined /* STRING */,
stringDelimiter: undefined /* STRING */,
dataShape: "GF_Datashape_EngineeringIm" /* DATASHAPENAME */
});
logger.info("successful");
var rowcsv;
var tableLengthCSV = csvInfotable.rows.length;
logger.info("tableLengthCSV = "+tableLengthCSV);
//add equal
for (var i=0; i < tableLengthCSV; i++)
{
logger.info("Processing the row :"+i);
rowcsv = csvInfotable.rows[i];
var date = rowcsv.timestamp;
var formattedDate = parseDate(date, "YYYY-mm-dd HH:MM:SS");
var query = {
filters: {
type: "And",
filters: [{
type: "EQ",
fieldName: "source",
value: rowcsv.source 
},{
type: "EQ",
fieldName: "tags",
value: rowcsv.tags 
},{
type: "EQ",
fieldName: "sourceType",
value: rowcsv.sourceType 
},{
type: "EQ",
fieldName: "location",
value: rowcsv.location 
},{
type: "EQ",
fieldName: "key",
value: rowcsv.key 
},{
type: "EQ",
fieldName: "timestamp",
value:formattedDate
},{
type: "EQ",
fieldName: "ID",
value: rowcsv.ID 
},{
type: "EQ",
fieldName: "Category",
value: rowcsv.Category 
}]
}
}; 
logger.info("not successful");
// EXECUTE THE query 
var Datatable = me.QueryDataTableEntries({ 
query: query /* QUERY */
});
//if( Datatable.length >0 ) 

logger.info("Datatable.rows.length "+Datatable.rows.length);
if( Datatable.rows.length >0 ) 
{
logger.info("Record is available already");
//Datatable.Date = rowcsv.Date;
Datatable.Year = rowcsv.Year;
Datatable.Month = rowcsv.Month;
Datatable.Budget = rowcsv.Budget;
Datatable.Category = rowcsv.Category;
Datatable.Value = rowcsv.Value;
me.UpdateDataTableEntries({
values: Datatable /* INFOTABLE */

}); 
}
else
{
logger.info("Working on the else part");
var params = {
infoTableName : "MyInfoTable",
dataShapeName : "GF_Datashape_EngineeringImport"
};

me.AddDataTableEntries({ values: me.CreateValuesWithData({ values: {
id: generateGUID(),
Year:rowcsv.Year,
Month:rowcsv.Month,
Budget:rowcsv.Budget,
Category:rowcsv.Category,
Value:rowcsv.Value
} }) });

}
}
//
// 
//After update and insert csv file


//}
//catch(err)
//{
// logger.error(err);
//
//}
//

this is data shape I created and its name is GF_Datashape_EngineeringIm.

attached file also includes my error when I uploading csv file into system

how to fix this issue? thank you

[vinakumar · Accepted solution]

Hi,

Would you please try specifying the columnMappings field and see if it helps to resolve the issue.

columnMappings - STRING:

• A semi-colon (;) separated string defining the mappings between the CSV columns and the Data Shape columns
  • Columns that map to a Data Shape field should contain the corresponding field name from the data shape
  • Columns that should be ignored should simply be skipped by inserting another semicolon (Field1;;Field2;Field3)
    • Location data (longitude/latitude)
      • If present will be populated using separate parameters
      • Only one of the two data columns needs to be included in the mapping
      • The other can be skipped

[slangley]

Hi @xiaoqw.

If the previous response answered your question, please mark it as the Accepted Solution for the benefit of others with the same question.

Regards.

--Sharon