Best answer

When replacing a google sheet with a csv, some of my data changes to dates (and it's a problem).

  • 15 December 2021
  • 3 replies
  • 161 views

  1. I have a csv file which is saved to my hard drive on a regular basis.
  2. The folder where it is saved is linked to my google drive.
  3. When a new file is added to this folder, ZAP triggers for the file.
  4. My next zap takes that file and replaces an existing GoogleSheet.

My problem is that when it replaces the google sheet, some data is irreversibly changed into dates. For example 3-0011 is changed to display 3-2011 and is recorded in the field as 3/1/2011.
I have tried both true/false for the option to turn it into a google document and neither changes the outcome.

The file has 30K+ lines so making formatting changes per line would probably be cost prohibitive.

Any ideas on how this can be achieved utilizing only zap, google sheets, and google drive would be appreciated.

icon

Best answer by Matthew is working 16 December 2021, 16:34

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

3 replies

Userlevel 7
Badge +14

Hi @Matthew is working 

This is likely due to how GSheets is interpreting the format of the data for the column.

Not sure there is going to be a simple way to address this since you are replacing an existing file with a new file.

Check out some of the GSearch results on the topic: https://www.google.com/search?q=gsheets+interperts+numbers+as+dates

Hi @Matthew is working 

This is likely due to how GSheets is interpreting the format of the data for the column.

Not sure there is going to be a simple way to address this since you are replacing an existing file with a new file.

Check out some of the GSearch results on the topic: https://www.google.com/search?q=gsheets+interperts+numbers+as+dates

Thank you for pointing this out, I was aware of this behavior. My first inclination would normally be to add an apostrophe or a star at the beginning of problematic string to prevent this however it’s not possible to modify the data source in this way.
This behavior is also not present when using a .xls file, unfortunately this specific data can only be produced in csv.

My current potential solutions:

  1. Use power automate to resave the file as a .xls file. (I don’t like this because power automate can have hiccups)
  2. Use a lookup table to correct the values from dates back to their original format. (I don’t like this because I have no control over future entries for this data and if the data between dates and data isn’t 1:1 it would break downstream processes unexpectedly and without immediate explanation.
  3. Use an additional zap integrations such as cloud convert to attempt to convert to .xls. (I don’t like this due to security concerns and uncertainty how it will convert this very same data)
  4. I was trying to figure out if I can use python to substitute the commas (,) for (,*) but that doesn’t seem to be possible unless I extract the csv with line item support which would pose problems with 30K lines. (correct me if I am wrong please).

I am hoping someone can point me toward a solution I’m not seeing or maybe make me aware of an advanced setting I might use to resolve this in zapier.

For anyone coming to this question for help: I ended up using cloud convert (option 3) because option 4 wasn’t viable as I have no data in my first column, option 1 would be prone to failure without purchasing premium services, and option 3 would make the data output difficult to reference for other personnel in my organization.

 

If anyone has any additional ideas they would be appreciated as it is not ideal to use a third party service outside of the zap/google environments.