CSV import converting text to date format

James Brian Hughes

I'm working on a project where we will be pulling hundreds of rows of information out of a bunch (hundreds) of Excel files, and storing that info from each Excel file into it's own SmartSheet. I already have the process down to pull the info we need from the Excel and stores it into a CSV file, so then the CSV file can be uploaded into SmartSheet for us to run reports across the information.

One column in particular, looks like this:

Sometimes (but not always which is weird) the data in the column above is getting converted into date format. So in the example above, that is how it looks in the CSV file, but after import, it looks like this:

So there are multiple entries with 02.01.00 on the same file (there is a separate indexing column) and that same entry would exist across every file created. And it's not consistent as to why it is getting converted to a date. I've checked the source file, and the column is formatted the same across them all. And there are the same columns in each CSV file getting created (it's a macro running to create the CSV files so they are created consistently).

Any help or insight would be greatly appreciated.

Krissia B.

Hello @James Brian Hughes

Upon reviewing and testing this on my end, I was not able to replicate the same issue you are having. I was able to import a csv file into Smartsheet and the numbers didnt convert into dates. Could you provide us additional details on this? Screenshots (please block out sensitive data) as well as the the type of column being used. Are you the only user experiencing this?

 

See my screenshots below of the results I had.

Excel CSV File

 

 Import Settings

Imported into Smartsheet

 

Cheers,

Krissia

James Brian Hughes

Hi Krissia,

Thank you for responding.  It is sporadic for me as well, which is why I haven't been able to pin-point why it is happening this way.  Seems to be for me if it happens with a certain CSV file, if I try to re-import it does the same thing. Attaching a sample CSV file I am using in a ZIP file.

1526v2.zip

Will respond again with screenshots of how my import is looking as I use this file.

James Brian Hughes

Here is the import screen using that file:

James Brian Hughes

This is what the smartsheet looks like after import, highlighted the fields that changed to date format automatically:

James Brian Hughes

And to show, I have a CSV file generated from a different source Excel file (using the same macro that created the one I attached above) and imported it and his is how it appears.

So it's random, and does not always seem to change that format to a date, but I can't pinpoint why. And I don't think it has to do with having text in the 3rd column, as I can scroll down in the original one I attached, and there is text in column 3 but still a date in column 2. Same for this one I show above, I scroll and find where column 3 is blank but column 2 stayed as text.

James Brian Hughes

https://community.smartsheet.com/discussion/comment/298513#Comment_298513

@Krissia B wondering if you saw my follow up comments above.

Brett Wyrick

@James Brian Hughes - I just uploaded your CSV (thanks for that) and I ran into the same issue. I thought maybe if I added an extra column and used a TEXT() formula in Excel that it would fix it, but it didn't.

So then I went to the thought, "Maybe it's because it's a CSV". I've had strange things happen with CSV's in the past. So I saved your file as a ".XLSX" in Excel. Once it was uploaded to Smartsheet, voila, no issues.

I noticed that on the import for the CSV file, the "Column Type" for ITEM SEQ# was "Date". When I did the import as XLSX, the "Column Type" for ITEM SEQ# is "Text/Number". ¯\(ツ)/¯

I can't directly tell you the root cause of this, though - looks like there's something wonky about text formatting when importing a CSV file to Smartsheet.

Brett Wyrick

If it's not possible to convert to XLSX, you could run this formula in a "Helper Column" (i.e., a column just used for formatting) in Smartsheet.

=IF([ITEM SEQ#]@row = "", "", SUBSTITUTE([ITEM SEQ#]@row, "/", "."))

You could make it a Column Formula to make it easier.

Hope this helps!

James Brian Hughes

Thanks @Brett Wyrick.  I built a macro that is gathering the info from a much larger excel file and creating the CSV file.  So I could modify it to save as an XLSX instead.  I've been working on this for a little bit, so I'll have to go back and see why I was using a CSV file instead of the native excel.  I thought there was some other limitation I was running into that I'm not remembering at the moment.

I want to avoid having to use another formula to format the data if possible because once I can get this working, we will eventually have hundreds of excel files to run this macro on and then import, as well as periodically having to upload new versions when changes are made to the source excel files.  So I need to keep the process as simple and straightforward so it's just the run and upload.

Brett Wyrick

@James Brian Hughes That totally makes sense regarding adding a formula column particularly for formatting. You want to ensure this is scalable!

If you ever want to get fancy with it, there are ways to auto-import Excel files to Smartsheet to create new sheets so you don't have to go through the import process manually each time.

This sort of workflow usually goes something like:

• If a new Excel file is added to a particular folder in Google Drive (or SharePoint, or Dropbox, Box.net, or is emailed to a particular address, etc.),
• then import the file to Smartsheet to create a Smartsheet Sheet.

I help companies do stuff like this (and various other quality-of-life improvements) to get their processes automated and less tedious. I have both a "I'll show you how to do it" model and a "I'll do the whole thing for you" model, if you'd be interested.

I wish you the best with this endeavor - as someone who does some Excel macros myself, I know these things are never a walk in the park.

-------

If this answered your question, please mark "Yes" below. This helps the community find solutions more quickly, and all the random Googlers out there.