Getdata In Excel

[Mauricette Atencio]

Im starting a new learning path towards getting a job in Business Intelligence. Starting with learning excel for data analysis. I bought 365 online for my chromebook. My excel only has "get data from picture" as a data import option.

1) Using "GetData" from a file. This works if I am using my computer. The moment I use another computer with access to the same Drive, I have to re-link the data from the new computer to the same file. I even changed the names of the multiple computers to the same name, so they have the same "filepath". It is still not working (without re-linking the same workbook when on a different computer).

2) Using "GetData" from a excel link on OneDrive. I tried multiple links, the one that seems to work is "downloading" the file, and copying the download link. This only works until the file is changed and a new link makes the old download link obsolete.

I am creating a report in excel using the SAPGETDATA formula. I'm getting a #VALUE! error on refresh but when I click into the formula itself and click out the formula displays the correct value although if I refresh the workbook again, the #VALUE! error comes back.

I have tried hard coding dimensions, getting rid of any excel formatting, excel fixes related to the number type/text, recreating the formulas from scratch, using a data source instead of a table as the SAC source but nothing seems to work...

I can see you are comparing the value returned by GETDATA() with "-" (unbooked) condition. The return from GETDATA() is not of type string so may be the comparison with "-" (quotes will make it string") is causing issue. I tried to recreate your logic at my end. See below.

I can not face particular this type of error. but to work around this scenario, you will try a combination of AVERAGE along with IF and ISERROR to determine if there is an error in the specified range. This particular scenario requires an array formula:

You almost certainly "did something wrong" or "have a corrupted Excel file". As you attached neither the "file that fails to read" nor the LabVIEW code (meaning something we can examine, can edit, can correct, can test, can run), that's about as much help as we can give.

Hey Bob,

first of all, thank you very much for your fast response. You are totally right.

Attached you find an 7z container with the necessary vi and the excel file.

If you start the vi (there is just one), klick "Datei laden" and wait while the excel file is opened.

After that is finished, you can enter a number in the field "Seriennummer Akku" and press enter. Then the vi will display the values that are stored at the excel file. If you type 330 or above, the error will appear.

With that files and the information, you should be able to duplicate the situation. Im curious to hear from you.

The-Dude

The reason to say "Compress the file" is that Windows make a .zip, which any Windows 10 system can open. Yet you chose to use a proprietary compression, which many Forum uses won't be able to (or simply "won't") open. Oh, well ...

Hey Bob,

sorry for the mess. I didnt know that there are so many differences between 7z and zip.

Attached is the zipped folder. There is everything you need inside. More that the steppes I wrote in my previous post are not necessary to get the error.

Thank you

The-Dude

If you look at your Excel file and displays the formulas you will see that starting on row 330 you have formulas ans conditional formatting. Also the results displayed in column K are not U16 but letters. You also have text in cell G330.

Since you only want to get the cell capacity and internal resistance, my suggestion is to use twice the Excel Get Data.vi. First time row x column 5 (F) and second time row x column 11 (L), both cases have the start and end set to the same value.

I have a folder with 5 excel files with different names but the exact same structure. I am trying to use the directory tool combined with the dynamic input tool to go to my folder, go to a specific sheet called "Raw Data" in each workbook and essentially stack the data in all 5 files on top of each other. I also want to add a column that contains the file path of each workbook.

Now, when you hit run, Alteryx will find the first xlsx file in that directory and pull it in (given the raw data tab), and then repeat this for all files, automatically stacking the data on top of each other.

When using this method, I would also change option 5, and output the filename as a field.

Also keep an eye out for the results pain, this will tell you if any files don't match the required schema, which is defined by the first table.

Also, lets say you have other files in your directory, then this method would require some more specific detail in the filename, such as a filename suffix that only exists on the files you want to bring in, for example

I used to have this open available but no longer see it an option in the drop down. Just curious to see if something has changed, is no longer supported, or maybe I need to update something on my end. I did just reinsall the analyze for excel component but not seeing any changes in excel from that update.

I have an existing excel file that I'd like to bring some data from a dataset into and was hoping to bring it in this way. On a side now I am able to download the .odc file and open that. Is there another way to create that connection in an existing excel file so I can bring the data in?

If you want an alternative you can connect from Excel to the "Analysis Services" instance of your Power BI dataset. There are some subtle differences, but mostly you'll get the same result as with the .odc

I am used of connecting to Power bi from Excel and realized that since the May 2020 release of Power BI desktop I cannot connect to data using the power bi tab, and do not see the connect to power bi dataset option. Have those options been removed?

How do I create that connection in excel? From the Get Data standpoint which option do you select? I can see the connection info in the properties of the odc file that is download, just unsure of how to take those connection properties and create a similar connection in an existing excel file.

I am using "getdatasubsetdownloadlinkfromprocess()" in the linkfield to export an excel on click on the link it navigate to untitled window and there is no instance in monitoring space. has anyone faced this before ?

2. From what I've heard, if you are using downloaddatasubset link and you want filtered data to be exported then you will have to pass the applied filters to input named process variable as process parameter and write logic to apply filters in the process model as well.

While Prateek's reply is a considerable solution for your problem, I suggest you to be mindful while using a!startProcess, especially in this case.

1. When you use a!startProcess to start a process, the fv!processinfo fetches only that data , you get immediately after the process initiates and it would not wait till the process is complete. In order to fetch all the data, you need to activity-chain all the nodes or in this case, till you get the document id. Consider activity chaining all the nodes in the process , only if this process is critical or if it takes less than 5 seconds to execute, as it has adverse effects on the performance.

2. And I suggest instead of sending the datasubset in process parameters, consider using a stored procedure to get the relevant data inside the process created from the above approach.

I am trying to create a Power Query feed into an Excel file that presents all the Issues listed under a given Epic. I have already verified that the REST API URL delivers the content that I am looking for by testing it in the Restless browser plugin, and here is the query:

Without any additional config information, I get an error 400, which I assume is because our Jira server requires authentication. No problem, maybe it needs the same Basic authentication that the Restless plugin needed.

OK, looks like I have managed to solve this myself, but it was tricky. I had previously found this article, -questions/Fetch-data-from-JIRA-to-excel/qaq-p/198786 , which initially didn't look like what I needed.

My main gripe though, and this was consistent with the download of a CSV file and import from an offline file, is that the field to describe what type of Issue each row is (Task, Story, Bug, etc) is a non-printable field, so just comes up as an empty column with title "T". I could perhaps fudge this with a range of aggregated filter queries per each type that I am interested in, but that seems a bit over the top.

I wasn't able to get all the columns and rows to show up. Maybe it was my lack of knowledge looking to pick this up soon. In the mean time I'm working on creating this as a report in Project Automation. That seems more promising.

@Christopher Lebruh When you go to Data ribbon-->Get Data-->From Web, you should get a screen "From Web" with Basic and Advanced choices, I select basic paste the URL and click OK, an then should get another screen that says Anonymous, Basic etc. I choose Basic and entered my credentials

The below steps will allow you to achieve the following: It will allow you to export your Jira data (rows and fields of your choice) to Power BI or Excel using Power Query. It will create a live connection to Jira which can be refreshed from Excel or Power BI. There is no row limits

By any chance do you know how to export the Epic Name, My company does use Better Excel but I would like to set up a excel Power Query that I can fetch a weekly report. I'm able to export Epic Name using Better Excel but that is not the case using excel.

Hi @Daria Pavlova , I unfortunately don't use Google Sheets so I am not able to test it, but according to ChatGPT, there is a way to connect to the Jira API or the link provided above using Script editor in Google sheets

3a8082e126