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

Community Tip - Did you get called away in the middle of writing a post? Don't worry you can find your unfinished post later in the Drafts section of your profile page. 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
vinakumar
12-Amethyst
(To:xiaoqw)

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
vinakumar
12-Amethyst
(To:xiaoqw)

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