cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Have a PTC product question you need answered fast? Chances are someone has asked it before. Learn about the community search. X

How to convert date time when import csv file

xiaoqw
15-Moonstone

How to convert date time when import csv file

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);
//
//}
//

 

xiaoqw_0-1578542503651.png

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

ACCEPTED SOLUTION

Accepted Solutions

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

View solution in original post

2 REPLIES 2

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
23-Emerald II
(To:xiaoqw)

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

Announcements


Top Tags