Excel Fix First Row

[Ermelindo Klatt]

Iam used to hiding and unhiding columns, but now my excel sheet refuses to show me the first column! I have tried "select all + right click + unhide" and just "selecting the first visible column + right click + unhide" ...

When I add Excel tables to a project, AGP doesn't recognize the first line as fields like ArcMap. Instead it gives me default names and treats the field names as first row. How do I fix this? I'd rather not lose flexibility so importing the tables into a gdb would be rather suboptimal.

The field names are in the image. The table works in ArcMap. Using the Excel to table tool also generates a valid table that uses the correct field names. I tried saving the sheet in multiple encoding formats, utf-8 among them, but the result is the same.

sorry... I though it was the field1 etc line, it is the line underneath. I was more interested in the excel table version, but from what is beneath that (aka from excel) you are using underscores or they were replaced, they aren't excessively long for a geodatabase table etc

Some new findings: when I copy the data into a new workbook (copy paste, values only), the attribute fields are recognized in AGP. Manually doing anything in a sheet (adding a field, or a formula) seems to break something. Once a workbook is busted (= contains any table that causes problems), adding a new sheet and copying data into it, from within itself or another workbook, results in sheets with the same problem.

The problem is happening because of how the cell is being stored, or in this case the absence of a cell value reference in the xlsx underlying table structure. To support a 64bit application along side potential 32bit Microsoft Office installs AGP utilizes a gdal driver to read excel files. This driver is not happy when the first row read appears to contain 4 cell values (number1, text1, text2, text3), but a subsequent row appears to only contain 3 cell values (1, value1, value2). This forms a jagged edge table and the driver gives up on trying to maintain the user defined headers.

1. Ensure the last column has no "missing" cell values, this is harder then I thought, as I cant actually narrow down the workflow that removes only the value reference, and not the entire cell reference.

I have similar issues using this data with BAO, and I believe that there is a bug report on it that was submitted in 2012. From what I understand the architecture of BAO and ArcGIS Pro are similar. Please investigate if this is part of the existing bug report with Mac/Excel, or requires a new bug report.

Ive attached some pics to help.

In the message box I have output the first data row plus first free row, 1 and 6.

Im looking to write cell to first free row, which is row 6.

Attached the error.

In " Find first/last data row" , I have already configured it for first free row.

In the msg box it states the first free row as 6.

Ive tried to do as you suggested but unsure where to place the -1.

Ive attached the xaml, the excel is is as per pic, column A with 1,2,3,4,5.

I couldnt get this to work in my main automation so I created this simple flow hoping to find out what should be a very easy task lol.

In the write cell action im not seeing how to choose column ?

Lets say I got first free row on column A which is row 6.

In write cell, how to specify column B ?

Something like Column B & row = first free row which is B6.

I have an excel sheet that is sometimes sent to me with a blank first row (2nd row with column names) and other times with a normal format of the first row having the column names. These are the only two ways the file is provided to me (for now, this vendor loves to surprise me). I need this report to remain automated, so I would like to find a way to detect if the first row is blank and if it is then use the proc import range option to correctly start at row two for the column names. If the first row contains the column names then run normally.

I just cannot work out a tidy way of detecting if the first row is blank, I was considering using %IF and %DATATYPE macros, but I haven't tried to do something quite like this before in SAS and not finding something similar enough to adapt for my uses. I'm sure I'm overthinking this too. I would be grateful for any suggestions!

I need someone that is very good with excel functions to help me out with something. I know this is probably pretty simple, but for some reason I cannot think of how to do it. I have an excel file (Client List) that I need to create a column in the file to reflect a new client ID. We want to make the client id to the first 4 digits of their last name and the first 2 digits of their 1st name so it would look something like this. I would like to insert a formula in the New client ID column to pull the new client ID from the client name cell. Any question feel free to ask.

Your first name formula should be =right not left, but it did the trick by splitting the names. However the last name did pull the comma after the last name as well, do you know what would be pulling the comma?

The problem I am finding is that the first names are not pulling into their own cell correctly. Also we have some that are setup like this and it is screwing with the concatenate formula. Smith, Michael R. & Suzan F. or Smith Jim & Sally. I am not sure how we can tackle these besides edit them manually. Ideas?

In this scenario, the name of the first sheet in the Excel workbook may appear in a language that's different than the Display Language set in Microsoft Office. For example, if you have Microsoft 365 installed with German, English, and Polish, the name of the worksheet created is displayed in German, despite having English (or Polish) set as the Display Language.

When you create a workbook through by right-clicking in a folder and then selecting > New > Microsoft Excel Worksheet, Windows Explorer creates a copy of a workbook named Excel12.xlsx from thea system folder called SHELLNEW in the active folder. The original Excel12.xlsx is created in the SHELLNEW folder at the Office installation, with the name of its default sheet in a language that may differ from the Display Language.

If you add a sheet by clicking "+" in the Excel Worksheet, the sheet name appears according to the Display Language. The behavior that's described in the Symptoms section only applies to the first sheet.

I am looking for the best way in FME to find the first blank row in an Excel Spreadsheet. I have a way, but there are likely better ways. For now I am using the AttributeFilter to give me all of the rows that have data and then using a counter to give me the first blank row. If you know of a better way I would like to streamline this:

I am needing it to write one new row of data to the first blank row in that same spreadsheet and then save it. In this case, row 127. Next time the workspace would be run it would be row 128 etc. etc. So the row number is dynamic each time the workspace it run.

By default the Excel writer will append to an existing file if the writer parameter "Overwrite existing file" (in the navigator) is set to No (which is its default setting), so I think you should give that a try. Probably will want to set the writer mode parameter to INSERT, just in case.

[[[BSW Excel First Blank Row Insert]]].fmwtIn case others have the same issue, I have found a solution, though I still do not think it is the cleanest way of finding the first blank row. If anyone comes up with a better mousetrap I'd like to try it. The workspace is attached. All you would need to do is edit the StatsCalcs to Group By what you need it to and set up the Writer to the columns you need. Hope that helps someone else!

Actually, even with a clean sheet it likes to overwrite row 127 over and over. Thought it was caching, but then it continued to do the same thing after saving, closing and rerunning. My solution seems to work so I will probably stick with it though I like @jlbaker2779 's idea for a not so clean spreadsheet.

I am already successfully retrieving data from a FastField form and inserting it into a customers existing Excel file. The data is then stored in the Excel file against a prefined job no which already exists in column A. Is there a method of inserting the retrieved data whilst leaving the job number in column A?

I tried your suggestion, the search is working and finding the first cell containing FILL, however the next step is still inserting data after the end of the list. Do I need to reference the search results somewhere when writing the new data?

3a8082e126