restructure Excel files without program Code

[Simon_Werner]

Hi there,

Hello,

I'm new here and would like to apologise if my question seems a little illogical.

I am looking for a tool for our company in the area of factory planning, with which unstructured Excel data must be converted into a structured, predefined form. I am analysing whether the raw data (machine, process and product information) of our customers can be converted into a structured format by an AI. This is necessary in order to be able to read in the data with the in-house software (VBA code is currently still being written for this). The target formatting is therefore known (import excel). However, the raw Excel data is different in every new project.

The reorganisation affects, among other things, the arrangement of rows and columns, but also the generation of formulas.

Is there a way to solve this problem with SAS?

Many thanky in advance.

Best regards

[ballardw · Accepted solution]

---

@Simon_Werner wrote:

Hi there,

Hello,

I'm new here and would like to apologise if my question seems a little illogical.

I am looking for a tool for our company in the area of factory planning, with which unstructured Excel data must be converted into a structured, predefined form. I am analysing whether the raw data (machine, process and product information) of our customers can be converted into a structured format by an AI. This is necessary in order to be able to read in the data with the in-house software (VBA code is currently still being written for this). The target formatting is therefore known (import excel). However, the raw Excel data is different in every new project.

The reorganisation affects, among other things, the arrangement of rows and columns, but also the generation of formulas.

Is there a way to solve this problem with SAS?

Many thanky in advance.

Best regards

---

I highlighted a bit of your question above. That section without details on how things change makes this question hard to answer with certainty.

SAS has many tools to restructure data but will likely have effort similar to your current VBA code depending on the particular flavors of "different Excel".

IF your current processes create spreadsheets with output where a single column has different types of values: character, numeric , date or numbers that mean different things depending on the row such as age, height and weight , then the effort will be considerable and whoever designed and approved such data needs serious reevaluation.

If the columns represent similar values but each column is a different time period (and keeps expanding or changing the periods) that is another class of sub-optimal data structures for use in other systems like SAS.

For use with SAS it would be preferable to generate file in a text format instead of Excel as that file format can place severe restrictions on access data "nicely". One of the most common topics on this forum is how to get data from Excel to SAS, often related to consistency of variable properties. Note  that "import Excel" is difficult in general because Excel imposes zero constraints on what goes into any given column, row or cell. Poor structure can further complicate the process.

[Reeza]

SAS won't use AI, it will be IF/THEN logic essentially. 

To use AI, try something like Azure Document Intelligence

https://learn.microsoft.com/en-us/azure/ai-services/document-intelligence/overview?view=doc-intel-4....

---

@Simon_Werner wrote:

Hi there,

Hello,

I'm new here and would like to apologise if my question seems a little illogical.

I am looking for a tool for our company in the area of factory planning, with which unstructured Excel data must be converted into a structured, predefined form. I am analysing whether the raw data (machine, process and product information) of our customers can be converted into a structured format by an AI. This is necessary in order to be able to read in the data with the in-house software (VBA code is currently still being written for this). The target formatting is therefore known (import excel). However, the raw Excel data is different in every new project.

The reorganisation affects, among other things, the arrangement of rows and columns, but also the generation of formulas.

Is there a way to solve this problem with SAS?

Many thanky in advance.

Best regards

---