Load CSV file to database gives error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=' at line 1" I am using same CSV file that was exported using e

I'm not sure on your issue with this node (it tends to be finicky), but if you can save the files in excel format I always prefer utilizing fn!readexcelsheetpaging(), cast the results as the CDT type you need to save and utilize the Write to Data Store Entity service.  Much more flexible and robust.

I'm not sure on your issue with this node (it tends to be finicky), but if you can save the files in excel format I always prefer utilizing fn!readexcelsheetpaging(), cast the results as the CDT type you need to save and utilize the Write to Data Store Entity service.  Much more flexible and robust.

Hello Chris,

Are you able to provide an example of how to best use the readexcelsheetpaging() function? 

I am getting returned Reaction Tree

a!writeToDataStoreEntity(
dataStoreEntity: cons!AIC_Demographic_DSE,
valueToStore: fn!readexcelsheetpaging(
excelDocument: ri!importdata,
sheetNumber: 0,
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: 1000,
)
),
)

My goal is to read (or import/upload) either a .xlsx or .csv file to a staging table then after processing the data (type conversions etc.) store the data into a CDT.

Kerlin said:

Hello Chris,

Are you able to provide an example of how to best use the readexcelsheetpaging() function? 

I am getting returned Reaction Tree

a!writeToDataStoreEntity(
dataStoreEntity: cons!AIC_Demographic_DSE,
valueToStore: fn!readexcelsheetpaging(
excelDocument: ri!importdata,
sheetNumber: 0,
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: 1000,
)
),
)

My goal is to read (or import/upload) either a .xlsx or .csv file to a staging table then after processing the data (type conversions etc.) store the data into a CDT.

Hi Kerlin, note the Reaction Tree error is likely due to where you are calling the a!writeToDataStoreEntity() function, this needs to be in a saveInto to be called on user interaction - if it is elsewhere on your interface, this error will be shown - so we will need the surrounding interface code to determine that.

However for the excel reading, I always suggest putting the fn!readexcelsheet() function within it's own expression rule - then you can test it independently to confirm the output is acceptable for the data store write.

My excel import rules typically look like this:

a!localVariables(
  local!data: readexcelsheetpaging(
    ri!doc,
    0,
    a!pagingInfo(1,cons!EXCEL_MAX_ROWS) /* this function does not take a -1 batch */
  ),

  if(
    local!data.totalCount=0,
    {},
    reject(
      rule!APN_isEmpty,
      a!flatten(
        a!forEach(
          items: remove(local!data.data,1),
          expression: {
            if(
              rule!APN_isEmpty(index(fv!item.values,1,"")),
              null,
              'type!{urn:com:gdit:types}CDT_TYPE_HERE'(
                field1: index(fv!item.values,1,""),
                field2: index(fv!item.values,2,""),
                field3: index(fv!item.values,3,"")
              )
            )
          }
        )
      )
    )
  )
)

Date values are the only thing I typically need to convert from excel into the CDT date or dateTime type - I use a helper expression such as this to wrap the index() calls above, to perform the conversion:

a!localVariables(
  local!split: split(ri!dateVal,"-"),

  if(
    or(
      rule!APN_isEmpty(local!split),
      count(local!split)<>3,
    ),
    null,
    date(
      index(local!split,1,year(today())),
      index(local!split,2,month(today())),
      index(local!split,3,day(today()))
    )
  )
)

Note, this function does not work on CSV files, it is for Excel only.