Decolumize Turned off - Date Format issue when importing Excel

asked on March 28, 2018 • Show version history

Hi There,

I am attempting to implement functionality detailed in the Workflow process created by Glen Murie in this post here https://answers.laserfiche.com/questions/47431/How-to-configure-Workflow-to-import-Field-data-from-a-CSV-file#119256. This reads off the text stream from the Excel sheet and then inserts it.

The issue I'm having is with decolumnize turned on, the excel is not read line by line but instead it brings in all the first columns first then moves onto the next row. Obviously for this functionality to work we need it to read row by row.

Turning Decolumnize off (and using Import Agent for the import) brings in the excel sheet a bit better but unfortunately it looks like it's bringing in all the date fields first, and incorrectly. it seems to be bringing the dates in as a string of numbers (presumably a code) rather than a date. For the rest of the fields and types, it's all correct and row by row.

Any ideas?

0 0

replied on March 28, 2018

Is this a csv file that is going to change on a regular basis? Is this a process that will read multiple csv files? Just wondering if this is a one time thing or something where the file does not change, could you just set up the csv file as a data source and do a query for the information?

0 0

View 1 previous reply

replied on March 28, 2018

Hi,

The CSV will change fairly regularly and you can't rely on the data structure remaining the same.

What our plan for this was to run through the text stream, use regex to look at the certain cell that will remain the same, grab the next few rows / columns until you hit a blank space, then continue running. Split up fields using regex and you get the correct data.

Our other option is to use SDK, which is totally fine but it's always good to look at a solution that's a bit easier to maintain than a script in the long term.

0 0

replied on March 28, 2018

Have you checked the excel sheet itself to see if the column is formatted as a date instead of general? If it is formatted as a date column does it bring it in right?

0 0

replied on March 29, 2018

it looks like the customers excel is a custom field shortening the date to "10 Feb 2016" rather than a date field or general. For some reason the data stream brings this all in first before any other data.

0 0

replied on March 29, 2018

After you read the text, are you using pattern matching or tokens with regular expressions? Since you know which column you are looking for and not searching a whole string for a specific pattern, could you use tokens and set the token type to date?

0 0

Question

Question

Decolumize Turned off - Date Format issue when importing Excel

Replies

Sign in to reply to this post.