CSV file help...please!

Ignition
1

Here is an example of a csv file that I am trying to import into Ignition. This is a nightmare! So many escape characters...I have tried to convert to xml with Excel with no luck. I have tried different Python scripts...I am lost.

Example of the csv file:

PartNumber,CRPartNumber,Description,UOM,Vendor,MAX,ActualQTY,,,PartNumber,OnOrder,OrderQTY,PlacedOnOrderDate,PartialOrder
124105,S-15828,"Adhesive, 3M Polystyrene Foam Insulation 78 Spray",EA,ULine,10,19,,,124105,1,19,2/28/2025,0
123457,123457,"NON STOCK - Welding, Wire, Stainless Steel, 316 - .035, Hard Core, 35LB Spools",EA,Matheson,0,10,,,123457,0,0,,
124025,S-16419,"Alcohol, Rubbing, House hold use sized",EA,ULine,14,36,,,124025,0,0,,0
123458,PFM1411580,"NON RESTOCK - Anchor, Reusable, 10 X KH-EZ 5/8 x 4""""",BX,Zoro,1,50,,,123458,0,0,,
124268,PFM1411600,"Anchor, Reusable, 10 X KH-EZ 5/8"" x 5""""""",BX,Zoro,3,4,,,124268,0,0,,0
124073,3091A35,"Drill Bit, Metal Cutting -  M16 - 1/2"" Shank - 3091A35",EA,McMaster Carr,2,23,,,124073,0,0,,
124070,PFM1411640,"Anchor, Reusable, 10 X KH-EZ 5/8"" x 6""""""",BX,Zoro,1,6,,,124070,0,0,,
124190,8305A24,"Tap, Hand, M18X2.5 Taper""",EA,McMaster Carr,1,26,,,124190,0,0,,
124071,PFM1411460,"NON RESTOCK - Anchor, Reusable, 25 X KH-EZ 1/2"" x 5""""""",BX,Zoro,0,0,,,124071,0,0,,

Any advice would be GREATLY appreciated.

2

You didn't mention Vision or Perspective so here's a script for Perspective.

  • Create a view and drop a Table component onto it.
  • Drop a button on the view.
  • Right-click the button → Configure Events → onActionPerformed → + → Script.
  • Paste in the code below.
def runAction(self, event):
	# Import Python's built-in csv library.
	import csv
	 
	# Ask the user to find the CSV in the local file system.
	path = "\path\to\file.csv"    # This must be available to the gateway.
	
	file = open(path)
	csvData = csv.reader(file)
	 
	# Create a List of strings to use as a header for the dataset. Note that the number
	# of headers must match the number of columns in the CSV, otherwise an error will occur.
	# The simplest approach would be to use next() to read the first line in the file, and
	# store that at the header.
	header = csvData.next()
	 
	# Create a dataset with the header and the rest of our CSV.
	dataset = system.dataset.toDataSet(header ,list(csvData))
	 
	# Store it into the table.
	self.getSibling("Table").props.data = dataset
	 
	# Close the file
	file.close()

Put Designer into Preview mode, press the button and it should populate the table.

There's something wonky with your original data. e.g., The last line has the field,

"NON RESTOCK - Anchor, Reusable, 25 X KH-EZ 1/2"" x 5""""""",
                                     Escape    ^     ^ ^ ^

which results in
NON RESTOCK - Anchor, Reusable, 25 X KH-EZ 1/2" x 5"""

You need to figure out why the data is coming in this way. It looks like it has been repeatedly converted with extra escapes added each time.

The code is basically that given at the bottom of Exporting and Importing a CSV | Ignition User Manual.

1 Like
3

You should probably be using a context manager when handling files:

with open(filepath, 'r') as f:
    data = csv.reader(f)

That way the file will be closed automatically, even if an error is raised during its processing.

2 Likes
4

I might have if I'd ever heard of them... :winking_face_with_tongue: