PxPlus User Forum
Main Board => Discussions => Programming => Topic started by: Mike Hatfield on July 02, 2019, 11:06:27 PM
-
Hi all
I have to read a csv file, update the 6th column and write the record out again.
I know I could do this with a call to Excel but I can't remember how to do it. I think it would also be very slow.
So, instead I'm reading the input CSV, updating the 6th column and writing it out to a new CSV file.
This is great except the new file has quotes around each column.
How do I write the record without the quotes?
Thanks
00015 DIM CSVREC$[1:7]
00016 IOLIST CSVREC${ALL}:[STR(",")]
00021 OPEN (2)"/HIT/TEMP/CUSTOMER.CSV"
00022 LET CUSTOMEROUT$="/HIT/TEMP/CUSTOMEROUT.CSV"
00023 ERASE CUSTOMEROUT$,ERR=*NEXT
00024 SERIAL CUSTOMEROUT$
00025 OPEN LOCK (3)CUSTOMEROUT$
00030 READ (2,END=0500)IOL=0016
00032 CSVREC$[6]=123456"
00034 WRITE (3)IOL=0016
This becomes
name@gmail.com,base,default,David,Chard,,
this
"name@gmail.com","base","default","David","Chard","123456","",
-
Quotes in csv columns should be ignored by reading applications. Their purpose is just to hide commas in strings. So does it matter?
-
Hi Allen,
I thought that was true but wasn't sure.
The file will be imported to Magento 2.x and I wanted to be certain.
Thanks
BTW I just discovered *obj/excel
This is so easy to use, HOWEVER, how do you make it work in WindX where the file you are working with is on the NTHOST server and you are on a workstation that has Excel installed?
-
Mike,
Generally when I have to read a CSV file I use the :[STR(",")] format which will handle quoted strings automatically.
My normal CSV processing logic goes something like this...
DIM Columns$[1:100] ! Array to receive column data
READ (csv_file,END=xxx) Columns${ALL}:[STR(",")]
This will parse the columns into the array string the quotes where required. It also will handle string with quotes which can be in CSV files with two quotes for each quote -- such as
"Mike says ""Hi"" to everybody"
You can also WRITE the CSV file using the same formatting. Just make sure the number of columns in the array match what you want output (or use a range specification as opposed to {ALL})
-
Hello Mike,
You should be able to instantiate the object as follows to indicate that the Excel is to be run locally on the workstation;
x=new("[lcl]*obj/excel")
However - the path$ arguments cannot include [lcl] or [wdx] tags.