Generate data table activity (keep empty cells within table range)

Help Studio
, ,
1

Hello community,

I am stuck with a problem.

I have datatable generated from text file (csv file) that I want to read and if there is an empty cell, my formatting gets destroyed because some columns have a mismatch then. For example if I read row 2 in the screenshot then the value of column currency is assigned to column reference which I want to avoid.

How can I avoid this?

Example in screenshot

1
1301×141 1.94 KB

2

@SenorChang

Ideally that would not be the case..can you check the input csv format properly is it having empty values with two commas surrounded

Cheers

3

The affected empty cell is also empty in the csv file. If that helps: the csv is generated from an online banking portal

4

@SenorChang

Can you show a screenshot or so..because ideally as mentioned if empty it would detect empty itself

Cheers

5

The image shows a pixelated and blurred text document with the German word "Gutschrift" highlighted in red. (Captioned by AI)
The image shows a pixelated and blurred text document with the German word "Gutschrift" highlighted in red. (Captioned by AI)707×152 11.2 KB

There is no blank space or anything

6

@SenorChang

If you give delimiter as semi colon then ideally it would take as blank

Cheers

7

The image shows a "Generate Data Table Wizard" interface with various options for configuring column types, data formatting, and separators, alongside input and preview sections. (Captioned by AI)
The image shows a "Generate Data Table Wizard" interface with various options for configuring column types, data formatting, and separators, alongside input and preview sections. (Captioned by AI)919×425 11.1 KB

I assume it is a missing setting in the wizard.

8

Why are you using Generate Data Table instead of Read CSV?

9

@SenorChang

Try csv parsing format option

Cheers

10

I tried but the problem is that I have several lines at the beginning of the file that are not part of the actual table I want to work with. Therefore, I read as text and remove those lines separately.

11

I tried but that options does not work at all. It does not separate the columns at all (instead all column values are in one item).

12

Read the text, remove those lines, write new text file, Read CSV…

13

How would that work anyway? If you read the text and store it as a string how would you know until which line to cut the text? And immediately when you convert the string to DT you loose the empty cells…

I solved it myself in the meantime!

Here is my solution in case someone else will hit that topic in the future…

  1. ‘Read text file’ activity to read the content
  2. Take the string and do a replace to replace “;;” with “; ;” (space between the semicolons)
  3. ‘Generate datatable from text’ activity to generate the DT

In this way I don’t loose empty cells within the table.

@Anil_G I found an older thread where you gave the hint with the blank between the separators so I tried to get that in my string. So thank you. :wink:

Cheers

1 Like
14

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.