CSV quotes and DATA

BlitzPlus Forums/BlitzPlus Beginners Area/CSV quotes and DATA

Paulo

(Posted 2007) [#1]

Hello to all.
Sorry this is not really a Blitz problem, but maybe someone can help.

I'm making a quiz game and I want to export a lot of Excel data to CSV and ultimately make it a DATA statement, but because 90% of the CSV file is strings, I need each entry to have "quotes" around it and wondered if anyone knew a fast way to do this?

Ive tried doing a Search and replace, e.g. replace the commas with "," but this gets messy because many of the strings contain commas and the last part of the data contains numbers which I dont want as strings.

Here's an example...

I have this in CSV:

This is a question,answer1,answer2,1,2

and I want:

Data "This is a question","answer1","answer2",1,2

I just want to avoid going through it line by line if possible.

Any help greatly appreciated.

bryan970

(Posted 2007) [#2]

do you have access to perl?
I think you can get it for free its an open source product

perl is great at doing this kind of stuff, if you do I can write the script for you.

bryan970

(Posted 2007) [#3]

also if you wanted to use blizplus I think what you are trying to do is very similiar to the example posted here in the documentation section for removing tabs (except you are adding quotes)

Function StripTabs$(cl$)
pos=0
Repeat
pos=pos+1
istab=0
l$=Left$(cl$,1)
If Asc(l$)=9 Or Asc(l$)=32
istab=1
cl$=Right$(cl$,Len(cl$)-1)
End If
Until istab=0
Return Trim$(cl$)
End Function

b32	(Posted 2007) [#4]

Yes, I would do it in the same way, only I would use Instr to find the comma's. I don't think it matters if you quote the numeric values too though: you can still read them as integers.

Graphics 800, 600, 0, 2

in$ = "This is a question,answer1,answer2,1,2"

Print Convert$(in$)

WaitKey()

End

Function Convert$(in$)

	in$ = in$ + ","
	out$ = ""
	Repeat
		
		cp = Instr(in$, ",")
		If cp < 1 Then Exit
		
		s$ = Left$(in$, cp - 1)
		
		val# = s$
		If Trim$(s$) <> "0" Then
		
			;the value is a number
			If val = 0 Then s$ = Chr$(34) + s$ + Chr$(34)
			
		End If

		out$ = out$ + s$ + ","
		in$ = Mid$(in$, cp + 1, Len(in$))
		
	Forever
	
	out$ = "Data " + Left$(out$, Len(out$) - 1)
	Print out$

End Function

Yan	(Posted 2007) [#5]

Assuming there are no stray spaces...

newLine$ = chr$(34) + Replace(oldLine$, ",", chr$(34) + ", " + chr$(34)) + chr$(34)
newLine$ = "Data " + Replace(newLine$, chr$(34) + chr$(34), chr$(34))

Paulo

(Posted 2007) [#6]

Thanks very much for the suggestions guys! Bryan970, I do have access to Perl (if memory serves me correctly), but I will try the examples posted first. :)

b32 and Yan, thanks for your code snippets, I can see the potential and will give it a try.

Paulo

Andy_A

(Posted 2007) [#7]

Here's another way to go about it.

It's a function from the code archives
http://www.blitzbasic.com/codearcs/codearcs.php?code=1922

;Parse string function demo
;  By:Andy Amaya
;Date:2007.02.11

;Purpose for parse$() function:
;	To allow user to extract words or groups of symbols using
;	the delimiter best suited to the task at hand.

crLf$ = Chr$(13)+Chr$(10)
separator$ = crLf$+"==========================================================="+crLf$

;===================================================
;Parse string using the "comma" character
;	Possible Use: read data in comma separated value file
;===================================================
Print "...separating CSV fields"
For x = 1 To 5
	Print parse$("This is a question,answer1,answer2,1,2",x,",")
Next
Print separator$


Print "Creating data statement(s)..."+crLf$
lineOfData$ = "Data "
For x = 1 To 5
	If x < 4 Then
		lineOfData$ = lineOfData$ + Chr$(34) + parse$("This is a question,answer1,answer2,1,2",x,",") + Chr$(34) + ","
	Else
		lineOfData$ = lineOfData$ +  parse$("This is a question,answer1,answer2,1,2",x,",") + ","
	End If
Next
lineLen = Len(lineOfData$)
lineOfData$ = Left$(lineOfData$,lineLen-1)

Print lineOfData$
Print separator$



a$ = Input("Press [ENTER] to Exit.")
End

Function parse$(string2Chk$, n, delimiter$=" ")
	;initialize local variables
	Local count% = 0
	Local findDelimiter% = 0
	Local position% = 1
	Local current$ = ""
	;'n' must be greater than zero
	;otherwise exit function and return null string
	If n > 0 Then
		;strip leading and trailing spaces
		string2Chk$  = Trim(string2Chk$)
		;find the word(s)
		Repeat
			;first check if the delimiter occurs in string2Chk$
			findDelimiter% = Instr(string2Chk$,delimiter$,position)
			If findDelimiter <> 0 Then
				;extract current word in string2Chk$
				current$ = Mid$(string2Chk$,position,findDelimiter-position)
				;word extracted; increment counter
				count = count + 1
				;update the start position of the next pass
				position = findDelimiter + 1
				;if counter is same as n then exit loop
				If count = n Then findDelimiter = 0
			End If
		Until findDelimiter = 0
		;Special Case: only one word and no delimiter(s) or last word in string2Chk$
		If (count < n) And (position <= Len(string2Chk$)) Then
			current$ = Mid$(string2Chk$,position, Len(string2Chk$) - position+1)
			count = count + 1
			;looking for word that is beyond length of string2Chk$
			If count < n Then current$ = ""
		End If
	End If
	Return current$
End Function