Ignoring empty lines and blank cells in Import feature, losing lines and format

Brad Jones

I'm not sure if this is a known or new issue, but I just discovered it today, and it's not cool.

While importing a spreadsheet that has 17 columns and 350 rows, I found that the file I imported into SmartSheet was not like the original.

I identified two problems:

Starting with the worst - if the first cell in a line is blank in Excel, SS just ignores the row and shortens the page by one line (the blank one it considered unimportant). There was information in the other cells of the line, just not the first. Bye bye dataBeyond losing information, this creates a problem for those of us who occaisionally have to export our files to compare them to offline originals. It also is problematic in that my file was prematurely cut off. When importing, there were three blank leading cells in the Excel sheet, so the SS import feature decided that those blank lines must signify the end of the file. Nope! There was a significant amount of data discarded from a file that was well within the import size limits.

The second problem, which is a time loser, is that SS ignores formatting on cells that are empty. So, if I have a row that is formatted with a light blue fill, but some of the cells are empty, SS ignores the formatting on those cells and when opening the sheet online I see a choppy colored line where filled cells are blue, but empty cells are white. This is a post-processing nightmare, if you happen to want uploaded spreadsheet will look like the original.

PLEASE have a look to these. These are glaring flaws, and the loss of imported data is just not acceptable.

Thank you,

Brad

Brad Jones

OK, that's weird. I uploaded the file again, and after three tries and an hour spent testing different ways to upload the file by populating the first column, now it works.

Really strange. I tested it many times, and it kept truncating the rows with empty first cells. Just plain odd...

Brad Jones

As for now, the only workaround I have found for SS not importing formatting for empty cells is to fill the cells with something small, like a " . "

It works, but is not a great solution, since I am wanting to later compare this sheet to an offline backup. It's not great to add useless data to the sheet just to make cell formatting work in SS.

Brad Jones

Ohh, and another formatting issue:

Inserting columns does not copy formatting into the new columns. So, if I want to insert a column into a table which already has line formatting, the cell fill formatting of the sheet does not populate into the new column. So, I have to manually add the formatting back to each rowTotal bummer.

J. Craig Williams

Brad,

A few questions

You mention comparing to an Excel backup.

Are you maintaining the data in both spreadsheets after the initial import?

Is syncing an option to solve your problem instead of importing?

If you are on an Enterprise version, Live Data might be an option.

http://help.smartsheet.com/customer/en/portal/articles/1910037-using-the-smartsheet-live-data-connector-enterprise-only-

I'm also in the process of testing AppSheet, which allows spreadsheets from various on-line platforms (Smartsheet, Excel in Dropbox, Google Sheet, etc..) to talk to one-another. Initial testing positive (though the learning curve is steeper than expected)

Craig

Travis

Hi Brad, I just tested everything you mentioned.

Each time I tested, rows where the first column was blank, were imported without issues. Also, rows where there are blank rows above it, were also imported.

I tried 5 times with different set ups and never had any data missing from the imported file.

It sounds like this is working for you now, if you see this behavior again, please email the file to[email protected]and explain your steps and they can help troubleshoot.

For the formatting, I believe I understand where the issue is coming from. When you format a row in Excel, rather than selecting the cells and changing the background color, single left click on the row number, then apply the formatting - this will apply the format to the entire column and not just the selected cells. If you do this, when you import the file into Smartsheet, the entire row will be formatted (even on blank cells) and when you add new columns to your sheet, the new column will also be formatted.

Brad Jones

Travis,

Thank you for your reply and work on this.

I was not super clear on my second post. When the problem happened the first three times I could repeat it. Then an hour later the functionality was working again. So, the data issue was ephemeral, and is now not producible.

As for the formatting, I will have to use the workaround/technique you mentioned. It is not always practical or preferred to format every cell in a row with the same color (i.e. if you have more than one table on a spreadsheet). I typically avoid this in Excel as it is a practice that can quickly get you into trouble, but if that is how SS works then I will try to work the same way to avoid complications.

Thanks again for the tips and advice.

Brad Jones

To Craig,

You are correct, I am trying to maintain the data in both places. Getting your company to adopt new technologies is an inherently slow process, so the original spreadsheet will be used by one department while the online SS sheets will be used by the site team.

In fact syncing data would be an absolutely perfect solution to my problem, but it is not apparently available in SS. The live data connector, from what I have gathered, is a one-way process. Actually the only thing I need is an ODBC connection between my SS and my companies DB, but the direction I need is IN to SS and not OUT of SS. Sigh…

My current plan is to just export the SS and do an Excel compare with a lovely spreadsheet compare add-in I got for Excel. The only problem I’m stuck with now is that SS does not handle friendly hyperlinks well, and it will import them but not export them. But that’s a topic for a different thread.https://community.smartsheet.com/discussion/hyperlinks-reformatted

Best of luck with AppSheet. What I saw of it from the videos looked very promising.

Brad Jones

oops. I meant this one.
https://community.smartsheet.com/discussion/cannot-parse-hyperlink-function-unfriendly-hyperlink-import

Two different issues related to hyperlinks.

J. Craig Williams

Brad,

I think this a certainly possible.

A colleague and I are also investigating the API in case an existing third party app won't do. Would love to have a customer to pay for that, but R&D does not always work that way.

Craig