Introducing the all-new TIBCO Community site!

For current users, please click "Sign In" to reset your password and access the enhanced features. If you're a first-time visitor, we extend a warm welcome—click "Sign Up" to become a part of the TIBCO Community!

If you're seeking alternative community sites, explore ibi, Jaspersoft, and Spotfire.

Jump to content

How can I read and format a CSV file with headers in Iron Python


. . 17

Recommended Posts

Dear all.

I was able to read a CSV file with headers using Iron Python, but I don't know how to format it. Please tell me how to do it.

The file to be read is the following CSV file.

The first three lines are the header and the fourth and subsequent lines are the data.

(sample2.csv)

202101121900,A10100,2021/01/12,19:00:00 ,ABC1230

Number,p,0,4,1

Small,28,Tall,23,Large,5,Huge,56

1,10.21,219.92,3278,M

2,10.22,319.71,6036,M

3,10.46,419.56,7135,L

4,10.08,519.34,2282,M

5,10.16,618.75,2176,M

When I read this in Iron Python and output it to a table, it looks like this.

 

 

 

1

2

3

4

5

6

7

8

 

 

202101121900

A10100

2021/1/12

19:00:00

ABC1230

 

 

 

 

 

Number

p

0

4

1

 

 

 

 

 

Small

28

Tall

23

Large

5

Huge

56

 

 

1

10.21

219.92

3278

M

 

 

 

 

 

2

10.22

319.71

6036

M

 

 

 

 

 

3

10.46

419.56

7135

L

 

 

 

 

 

4

10.08

519.34

2282

M

 

 

 

 

 

5

10.16

618.75

2176

M

 

 

 

 

 

 

I would like to format the data to look like the following.

The goal is to read many files in this format and combine them into one data table.

I have attached a sample file and would appreciate your advice.

 

 

 

1

202101121900

A10100

2021/1/12

19:00:00

ABC1230

10.21

219.92

3278

M

 

 

2

202101121900

A10100

2021/1/12

19:00:00

ABC1230

10.22

319.71

6036

M

 

 

3

202101121900

A10100

2021/1/12

19:00:00

ABC1230

10.46

419.56

7135

L

 

 

4

202101121900

A10100

2021/1/12

19:00:00

ABC1230

10.08

519.34

2282

M

 

 

5

202101121900

A10100

2021/1/12

19:00:00

ABC1230

10.16

618.75

2176

M

Link to comment
Share on other sites

The first thing I can't figure out is how to extract the header section elements one by one.

The second is how to combine the header and data parts to create a record with one row each, and output this to the data table (additional output). With the current method, we have to create new data for each output.The following script was adapted from # https://community.tibco.com/questions/script-import-contents-text-file-or-csv-file.(Iron Python script)

from Spotfire.Dxp.Data import *

Dxp.Data.Import import *filePath=r "C:sample2.csv"

#specify any settings for the file

settings= TextDataReaderSettings()

settings.Separator=","

dataSource=TextFileDataSource(filePath,settings)DataManager=Document.

#datasource name and datasource to add table

DataManager.Tables.Add("myNewDataTable",dataSource)Document.Properties['file']= dataSource.DocumentTitle

#Document.Properties['file']= "TEST" 

Link to comment
Share on other sites

To skip the first three lines: Spotfire wants an object of type Array of integer with the list of lines. Note it starts from 0.

 

from System import Array

linesToSkip=[0,1,2]

linesArray = Array[int](linesToSkip)

....

#after creating the settings object

settings.AddIgnoreRows(linesArray)

 

I don't understand the second requirement though.

Link to comment
Share on other sites

Dear Gaia.Thank you for your suggestion.

I have confirmed that I can extract the data part by the method you taught me.

I really need the information in the header section, so I will continue to investigate.

If I can't do it with IronPython script, I'm thinking of using TERR. (I'm more familiar with R.)Best regards

Link to comment
Share on other sites

This matter was resolved by reviewing the data aggregation method. Instead of doing the data processing within Spotfire, we decided to create a special program to do the data aggregation before loading it into Spotfire. Thank you very much for your suggestion.
Link to comment
Share on other sites

×
×
  • Create New...